How to calculate date cells that are Empty

[WKU]

I am open to suggestions if you believe that there is a better way of doing this. I have a query that has 11 dates on it, I am trying with iif statements to get it down to one column reflecting the most recent date out of the 11, for example 5/2/14 and 4/9/14, I would want to have 5/2/14. The problem with this is that most of the cells are empty and and I can't calculate empty date cells. my thought was to create a statement that goes something like this iif([Date1]="", "1", [date1]), obviously this does not work but I would like for it to show something other then nothing so that I can calculate on it.

[ItsMe]

How about testing for [date]<>""

Also, should not use reserved words like date as names for fields, objects, etc.

[June7]

If the field is empty it is probably a Null. Maybe handle Null.

Nz([Date1],#12/31/2999#)

Sounds like non-normalized data structure.

Maybe too many fields for nested IIf. Alternative might be Switch, otherwise build a custom function.

[WKU]

It isn't a normal data structure but the upside is that the amount of information is fairly limited with only a few hundred list items. You're formula created a column with all rows showing the date of 12/31/2999, how would I use it in order to show the actual dates that are there but when it is Null to replace it with 12/31/2999?

[June7]

That's what the Nz function should have accomplished. If Date1 is null then return 12/31/2999.

[WKU]

It only returns 12/31/2999 what could be wrong?

[June7]

Offhand, that means all records have no data in that field, but if you know there is data, then I cannot explain without analyzing data. If you want to provide, follow instructions at bottom of my post.