IIF Statement Using Dates in MS Access/VBA/SQL

[davidb88]

Hi!

I am trying to create a if/then statement using MS VBA and an SQL statement to update a new field based on the dates of the current date field. I thought that the code I had written would work well but when I try to run it I get an error saying there are an invalid number of arguments.

This is the code I am trying to run:

Dim maxdate As DateDim strSQLdt As String


maxdate = DMax("[Closed Date]", "tblHPILTV")
strSQLst = "Update tblHPILTV " _
        & "SET tblHPILTV.[Updated Close Date] = IIF([Closed Date] Is Null," _
        & "DATE(2008,7,1),IIF([Closed Date]>" & maxdate & "," & maxdate & ",IIF(MONTH([Closed Date])<=3," _
        & "DATE(YEAR([Closed Date]),1,1),IIF(MONTH([Closed Date])<=6,DATE(YEAR([Closed Date]),4,1)," _
        & "IIF(MONTH([Closed Date])<=9,DATE(YEAR([Closed Date]),7,1),DATE(YEAR([Closed Date]),10,1))))));"


DoCmd.RunSQL (strSQLst)

I think the if statements are relatively straightforward and the maxdate variable is supposed to be finding the most recent date in the Closed Date field.

If someone is able to make a suggestion as to what the issue may be that would be most appreciated.

Thanks so much!
David

 

[xenou]

I wouldn't use IIF's here's. The logic can be simplified without such all the complicated nesting. In words what is it that you want to do?

 

[davidb88]

Hi Xenou, thanks for responding. Basically what I am trying to do is create an update to a new column "Scrubbed Closed Date" based on the already populated "Closed Date" column. The first thing I am doing is find the most recent closed date (maxdate). From there I am trying to do the following:
- If the closed date is blank set the "Scrubbed Closed Date" = 7/1/2008.
- If the closed dated date on the table is more recent than the max closed date, set the scrubbed closed date equal to the max closed date
- If the closed date is earlier or equal to March, set the scrubbed closed date month equal to January 1, with the same year as the closed date
- If the closed date is earlier or equal to June, set the scrubbed closed date month equal to April 1, with the same year as the closed date
- If the closed date is earlier or equal to September, set the scrubbed closed date month equal to July 1, with the same year as the closed date
- Otherwise set the closed date equal to October 1, with the same year as the closed date

The reason that I am trying to do this is eventually in another step in the database I will be linking the date to another table based on the date and the only dates in the second table are the second month of the quarter.

Hopefully I was able to explain that clearly enough in words. If not please let me know.

If you or anyone else have any suggestions of an alternative to nested If statements that would be great.

Thanks again!
David

 

[xenou]

Hi,
Okay. I was thinking of something different but you can avoid the nesting:

In two queries (dealing with the Nulls separately):

Update tblHPILTV SET [Updated Close Date] = 
    DateSerial(Year([Closed Date]), ((Int((Month([Closed Date])+2)/3)-1)*3)+1,1);

Update tblHPILTV SET [Updated Close Date] = DateSerial(2008,7,1) WHERE [Closed Date] Is Null;

Or in one query:

Update tblHPILTV SET [Updated Close Date] = 
    Nz(DateSerial(Year([Closed Date]), ((Int((Month([Closed Date])+2)/3)-1)*3)+1,1), 
    DateSerial(2008,7,1))

The formula for quarters is adapted from Solved: What Quarter is the Date? [Archive] - VBA Express Forum (and elsewhere).

Looking back at your original query, I think the problem is that DATE() is an excel formula. In access you must use DateSerial(). Also you need to put date literals between hashes. maxdate is a date literal, at least when you are writing it into a string (i.e. "#" & maxdate & "#"). So you could probably re-write it to get it working if you wanted to stay with your original idea.

Note that I don't have any use of max date in my query anyway - there won't be any dates greater than the max date so you don't need to do anything about that. Unless you mean greater than OR EQUAL TO the max date ... . If that is what you meant then add a criteria to the above query:

Two queries version:

Update tblHPILTV SET [Updated Close Date] = 
    DateSerial(Year([Closed Date]), ((Int((Month([Closed Date])+2)/3)-1)*3)+1,1)
    WHERE [Closed Date] <> #" & maxdate & "#;"

Update tblHPILTV SET [Updated Close Date] = DateSerial(2008,7,1) WHERE [Closed Date] Is Null;

one query version:

Update tblHPILTV SET [Updated Close Date] = 
    Nz(DateSerial(Year([Closed Date]), ((Int((Month([Closed Date])+2)/3)-1)*3)+1,1), 
    DateSerial(2008,7,1))
   WHERE [Closed Date] <> #" & maxdate & "#;"

 

[davidb88]

Xenou, I can not thank you enough! This works perfectly and solves this problem I have been trying to solve for over a week now! Thank you again!