Trouble using IIf with standard dates 1/1/2015 for example

[steffwood]

Hi,

I have a question, I'm using the expression builder in a query to determine which code to use based on a date.

I've had problems with the dates, no matter what I use, it applies the false condition to the list, even if I use a date such as =1/1/2015,"X","Y".
It always selects Y. I've checked the formatting in the input file that is linked, it is set to a date format, US MM/DD/YYYY. On the parameters in the query, property sheet there is no option for Format.

Also, how can I use a SWITCH statement? I want to be able to say:

If date = 1/1/2015 use X
If date is between 1/2/2015 and 1/30/2015 use Y
If date <= 1/1/2015 use Z



Thank you, any help would be great.

[ssanfu]

Are you using the date delimiter? Dates need to be delimited with the hash mark (#).
Also note that "Date" is a reserved word in Access and shouldn't be used for object names.

If MyDate = #10/16/2015# Then
  X = TRUE
End If

Something = Switch(MyDate < #1/1/2015#, X, MyDate = #1/1/2015#, Z, MyDate > #1/1/2015# And MyDate < #1/30/2015#, Y)

[steffwood]

Thank very much!