CASE WHEN statement problem

[Cujo67Camaro]

I am trying to make a field in a query which displays a date based off of the criteria below. I tried nesting IIF statements but I couldn't get it to work so I tried a Case WHEN statement.

CASE
WHEN [Appfrom] Is Null & [Fundfrom] Is Null THEN "7/7/1999"
WHEN [Appfrom] Is Null & [Fundfrom] IS NOT NULL THEN [Fundfrom]
WHEN [Fundfrom] Is Null & [Appfrom] IS NOT NULL THEN [Appfrom]
WHEN [Fundfrom] IS NOT NULL & [Appfrom] IS NOT NULL THEN "7/7/1999"
END

Here is the IIF statement

IIf(IsNull([Appfrom] And [Fundfrom]),"7/7/1999",(IIf(Not IsNull([Appfrom]) And IsNull([Fundfrom]),[Appfrom],(IIf(IsNull([Appfrom]) And Not IsNull([Fundfrom]),[Fundfrom],"7/7/1999"))))))

Neither one works and have no idea how to fix it. Any ideas would be greatly appreciated. Thanks.

 

[Banana]

I've never heard of "Case When" statement.

I'd use "Choose" or "Switch" function; look into the query's help file for an explanation on how to use those functions.

 

[Brianwarnock]

I've never heard of a Case When either , and though Switch or Choose may be the best option to get your IIF to work correctly, pity you didn't say how it was failing, I think you need IsNull after the 1st AND to make it clear

IIf(IsNull([Appfrom] And IsNull[Fundfrom]),"7/7/1999",(...

BUT if you are trying to insert Dates then you need to use # not "


edit Actually you only need 2 tests the Not isnull(Appfrom) and Isnull(Fundfrom) and the reverse, as both null or both notnull default to the same.

Brian

 

[pbaldy]

CASE is valid in T-SQL (SQL Server) and IIf() is valid in Access. Which are you working in?

 

[Cujo67Camaro]

Thanks a million! Everything works fine with that query now.