Using Nz to return a date

[mbentley]

I'm using the Nz function to interrogate a date field in a query. Problem is that Access does not seem to recognize the returned value as a date when the original value was null. Here's what I've tried:

NewDate: Nz([OldDate],0)

and

NewDate: Nz([OldDate],#01/01/1900#)

I've also tried specifying in the field properties that the field is a date, which does not help.

Any ideas?

 

[KenHigg]

NewDate: iif(isnull([OldDate],#01/01/1900#,[OldDate])

???
ken

 

[mbentley]

I forgot to mention that I tried that too. Didn't work. But thanks.

 

[KenHigg]

new date: IIf(IsNull([date]),#1/1/2000#,[date])

Worked here. Is something else going on? Is this just a select query?

???
ken

 

[mbentley]

Yes, but it does use a group by clause and an aggregate function on the NewDate field. Of note, I'm using Access 2000.

 

[KenHigg]

Have you tried saving this query, then doing a new query based on it that does the totals / grouping stuff?

kh