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.

 

[Pat Hartman]

There are two problems.
1. fixing existing bad data
2. preventing future bad data

The solution to #1 is an update query that has a where that finds all the rows with null in the date field you want to change. I'm assuming that the date field is defined as a date data type and not a string.

The solution to #2 is either to add a default to the date field in the table or to add code in the form's BeforeUpdate event to populate the date before the record gets saved if the user hasn't provided a value.