IIF statement to return specific multiple dates

Question

Please help with giving me an IIF statement for query in Access 2003 to do the following: (IIF Statement field name should be Date Due)

Date Something Sent + 30 days = (Field Name = 2nd Request)

Date Something Sent + 60 days = (Field Name = 3rd Request)

So if today's date is plus or minus 2 days to 2nd Request, then use 2nd Request, else use 3rd Request if today's date is plus or minus 2 days to 3rd Request.

Thanks!  Your help is much appreciated!

Yappy

Answer 1

Some of the records for [Date PreQualification Packet Sent are null. 

I ran your test expression the value returned was 0 in all records.

I figured it out so that it will work the way I want it:

DateDue: IIf(Now()<=[Date PreQualification Packet Sent]+30,[Date PreQualification Packet Sent]+60,IIf(Date()>=[Date PreQualification Packet Sent]+60,[Date PreQualification Packet Sent]+90,""))

Thank you for all your help.  It was much appreciated!

Yappy

Answer 2

This is my statement:

DateDue: IIF([2nd Request Date] Between Date() -2 AND Date() +2, [2nd Request Date], IIF(([3rd Request Date] Between Date() -2 AND Date() +2, [3rd Request Date],""))

It is telling me that the expression I entered has a function containing the wrong number of arguments.

We are almost there.  What am I missing?

Thanks again!

Yappy

Answer 3

Try this --

DateDue: IIF([2nd Request] + 30 Between Date() -2 AND Date() +2, [2nd Request], IIF(([2nd Request] + 60 Between Date() -2 AND Date() +2, [3rd Request],""))

---

Build a little, test a little.

Answer 4

This is not what I was looking for; however it will be useful to me.

I need to see one column listing the date due.

So if today's date is plus or minus 2 days to 2nd Request date, then use 2nd Requestdate, else use 3rd Request date if today's date is plus or minus 2 days to 3rd Request date.

It is important that the 2nd request date or 3rd request date be within a plus or minus 2 day range of the current date.

Thanks,

Yappy

Answer 5

I'm not clear whether you want 2 columns listing the date due or one column indicating which request. The following you will tell you whether its a 2nd or 3rd request depending on the date sent.

DateDue: IIF(DateAdd("d",30,[sentdate])>Date(),"",IIF(DateAdd("d",60,[datesent])<Date(),"3rd Request","2nd Request"))

If you want separate columns then:

2ndRequest: DateAdd("d",30,[sentdate])

the 3rd request would be the same except change 30 to 60.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries