Query Question - Help Please

[totslnewbie]

Hi guys,

So I need to create a query that does the following:


I was told to use the Switch function, but that didn't quite work out...Before that I was trying the IIF AND statements, but couldn't get it right...(I was only able to input "Call Missed" but not the others.) Any help on how to do this would be appreciated...

[June7]

Post your attempted query SQL statement.

Maybe

Status: IIf([EndDate]<Now(), IIf([CompletedCallDate] Is Null, "Call Missed/Check if Call Made", "Completed"), "To Be Completed")

[totslnewbie]

A1CT3Message: IIf([DateA1CT3TO]<Date() And IsNull([CompletedDateA1CT3]),"Missed Data Point", IIf([DateA1CT3TO]>Date() AND Not IsNull([CompletedDateA1CT3], “Complete”, “To be Completed”))

[June7]

That expression does not fit the flow diagram you posted. Did you try my suggestion?

[totslnewbie]

Wow, yes it did! Thanks very much

[totslnewbie]

ohh oops, the only other thing is that, if the EndDate > Now, but there is a completed call date, it should say complete. I think I messed that up in the diagram

[totslnewbie]

redid the diagram.

[totslnewbie]

ok i was able to redo the code you provided based on the above diagram. works now, thank you very much.

[June7]

Okay then I see why you tried that.

Will EndDate ever be Null?

Maybe:

IIf([EndDate]<Now(), IIf([CompletedCallDate] Is Null, "Call Missed/Check if Call Made", IIf([CompletedCallDate]>Now, "Completed", "To Be Completed"), "Completed")

or

IIf([EndDate]>Now() Or [CompletedCallDate]>Now, "Completed", IIf([CompletedCallDate] Is Null, "Call Missed/Check if Call Made", "To Be Completed"))