Query using IIF

[TimE]

2 date fields. SCHEDULE DATE and RESCHEDULED DATE, for ease of typing, I am going to refer to them as SD and RD.

If RD is blank and SD is in the future, then show SD. If RD is in the future, then show RD. If SD and RD is in the future, RD "SHOULD" be a greater date than SD, show RD.

In Excel, I used the below formula and it works the way I want.
EXCEL: =IF(A2>=NOW()-1,A2,IF(B2>=NOW()-1,B2,""))

When I try the same in the query, it does not.

ACCESS: SCHED DATE: IIF([SD]>=Now()-1,[SD],IIF([RD]>=Now()-1,[RD],""))


Thanks in advance for ANY assistance.

 

[Bat17]

If RD is blank and SD

You are not testing for a Blank RD

RD "SHOULD" be a greater date than SD, show RD

Assuming that [RD]&[SD] are dates rather than Date/Time then you could simplify it with

ACCESS: SCHED DATE: IIF([SD]>Date(),[SD],IIF([RD]>Date(),[RD],""))

This says:-
If SD is in the future the Show SD
Else IF RD is in the future Show RD
if neither RD or SD are in the future then show nothing.

Is that the logic you are after?

Peter

 

[TimE]

The more I think about it, I am not sure what my logic is. SD is the original Date. If not met, then RD is entered. RD should always be greater than SD. My excel conversion gave me an #Error as did

SCHED DATE: IIF([SD]>Date(),[SD],IIF([RD]>Date(),[RD],"")).

I am not sure of the nested IIF.

 

[Bat17]

Try

SCHED DATE: IIF([SD]>Date(),[SD],IIF([RD]>Date(),[RD]))

I think it is trying to add an empty string into a date field that is causing the problem.

Peter

 

[TimE]

It appears to be working now....thank you.