Hi all,

I have a two column table in which the dates are stored in a mmddyyyy format. Now i have to create a query whereby i can check if the date that i have in my Datesupdate field for whether it is a monday or a tuesday etc and if it is then increase it by 1 but if the date in the Datesupdate field is a Friday then increase it by 3.. I have tried doing this in part and for increasing the date used:

[tbl_updatedate]![Datesupdate] + 1

where tbl_updatedate is my table and Datesupdate is my date field. But what happens is that i get the increased date as mm+1,dd,yyyy. It works with the format ddmmyy but i cannot give the date like that. Also for setting the criteria mentioned i am not sure how to make use of say IIf. I have seen the use of iif as given below in the Update field of the Update query:

= IIf(Weekday(Date())=1, [tbl_updatedate]![Datesupdate]+1, IIf(Weekday(Date())=2, [tbl_updatedate]![Datesupdate]+1, IIf(Weekday(Date())=3, [tbl_updatedate]![Datesupdate]+1, IIf(Weekday(Date())=4, [tbl_updatedate]![Datesupdate]+1, IIf(Weekday(Date())=5, [tbl_updatedate]![Datesupdate]+3)

but this would mean checking whether the day today is a Monday or Tuesday etc instead of checking the dates in the Datesupdate field for which day they are on . Could i also know what is the exact difference between what you are allowed to put in the update and the criteria row of any update query. For eg say how can one put

= IIf(Weekday(Date())=1, [tbl_updatedate]![Date]+1, IIf(Weekday(Date())=2, [tbl_updatedate]![Date]+1, IIf(Weekday(Date())=3, [tbl_updatedate]![Date]+1, IIf(Weekday(Date())=4, [tbl_updatedate]![Date]+1, IIf(Weekday(Date())=5, [tbl_updatedate]![Date]+3)

in the update field for doesn't it hold conditions or criteria within it.

Any Ideas!

Chow
Goels

P.S - Just to add, also i noticed that putting the above mentioned IIf statement in the Update field gives a pop-up box the tab saying "Enter Parameter Value" and the content being a text box with the heading tbl_updatedate. If i give it in the criteria row then i start getting an error that the 'destination should be specified'.

Have you tried using the DateAdd function instead of merely doing a "+ 1" or a "+ 3"? That is

NewDate = DateAdd("d", 1, OldDate)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division