IIF Statement Using Dates in MS Access/VBA/SQL

davidb88

Board Regular
Joined
Sep 23, 2013
Messages
76
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:
Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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
 
Upvote 0
Hi,
Okay. I was thinking of something different but you can avoid the nesting:

In two queries (dealing with the Nulls separately):
Code:
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:
Code:
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:
Code:
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:
Code:
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 & "#;"
 
Last edited:
Upvote 0
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! :)
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top