insert literal date when null

[opopanax666]

hi everybody,

I have a column "EndDate" with some dates filled in, and some null values. I want to create a new column with the filled-in date-values, and the null-values need to be 31/12/2999. So i tried

EndDate2: Nz([EndDate];#31/12/2999#)

but no go, null-fields stay empty.

What did i miss?..

 

[Gasman]

So they are not Null?
Have you tested them?

SELECT TestTransactions.ID, Nz([TransactionDate],#12/31/2099#) AS Expr1
FROM TestTransactions;

 

[opopanax666]

No, they were not... Changed it to an IIF-function, and now it works as intended.

Thank you for the speedy response!

 

[moke123]

EndDate2: Nz([EndDate] ; #31/12/2999#)

Not sure of your regional settings but try a comma instead of a semi-colon.

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

 

[Gasman]

Not sure of your regional settings but try a comma instead of a semi-colon.

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

Nice spot Moke, never noticed the semicolon.

 

[The_Doc_Man]

The one other thing to watch for is that a column might look like there is nothing in it but it isn't null. That happens if the column was originally intended for dates expressed in string format. Not saying that would happen in your specific case, but the better test - where that case applies - is IIF NZ(datefield, "") = "", #31/12/2999#, datefield) - because IF a string CAN be involved, it can be an EMPTY string which is NOT a null but it looks the same to your eye.

Just offering a side note.