Default date for Null field

[lrgm]

I created an expression to fill null fields in one of my tables with a
designated default date using the IIf function. The null fields were filled
but when I apply a <=[question] criteria, records containing the default
dates are not always included, depends on the criteria date. Does this issue
sound familiar?

Thanks!

 

[Evi]

We'll call your Datefield XDate

Instead of the IIF what about trying

DateNull: DateValue(NZ([XDate],#24/04/2008#))

(I've set the default date to 24/04/08 in this example - oddly, I typed it
in the way Access usually prefers ie #4/24/2008# but it changed it to a
'normal' date)
Evi

 

[Rick Brandt]

I created an expression to fill null fields in one of my tables with a
designated default date using the IIf function. The null fields were
filled but when I apply a <=[question] criteria, records containing
the default dates are not always included, depends on the criteria
date. Does this issue sound familiar?

Thanks!

Please explain where you put this expression. You cannot "use an expression
to fill Null Fields" unless you mean you used it in an UPDATE query.

 

[John W. Vinson]

I created an expression to fill null fields in one of my tables with a
designated default date using the IIf function. The null fields were filled
but when I apply a <=[question] criteria, records containing the default
dates are not always included, depends on the criteria date. Does this issue
sound familiar?

Thanks!

If you are selecting the records to be updated by using a date range
criterion, NULL values will not be found. NULL is not less than [question],
nor is it greater than [question], nor is it equal to [question] - it's just
unknown and will not match any criterion other than

IS NULL

If you want to update all records where Datefield is NULL, just use a
criterion of IS NULL on datefield; no IIF is needed. If that's not what you
want to do please explain.

 

[Ken Sheridan]

Evi:

This is another one of Access's little foibles to confuse us Yurpeans. In
query design view dates are in the format of the local regional setting.
This even applies to date literals delimited by the # character. If you
switch to SQL view you'll see that its in mm/dd/yyy format there.

With a date like 24 April it doesn't matter of course as there is no month
24, but with a date like 5 April then #05/04/2008# this would usually be
understood to be 4 May regardless of the regional date format in use, but in
query design view does in fact represent 5 April where the regional date
format is set as dd/mm/yyyy in Windows control panel.

I do wonder whether MS simply overlooked this when 32 bit Access was
introduced. In version 2 the regional setting was respected both in
functions like CDate, and in date literals using the # date delimiter
character. With Access 95 date literals had to be in US short date or an
otherwise internationally unambiguous format. Maybe they simply forgot to
change it in the query designer. It doe seem to be a strange anomaly
otherwise.

Ken Sheridan
Stafford, England

We'll call your Datefield XDate

Instead of the IIF what about trying

DateNull: DateValue(NZ([XDate],#24/04/2008#))

(I've set the default date to 24/04/08 in this example - oddly, I typed it
in the way Access usually prefers ie #4/24/2008# but it changed it to a
'normal' date)
Evi

I created an expression to fill null fields in one of my tables with a
designated default date using the IIf function. The null fields were filled
but when I apply a <=[question] criteria, records containing the default
dates are not always included, depends on the criteria date. Does this issue
sound familiar?

Thanks!

 

[Evi]

Thanks for explaining that Ken. I thought that Access was just taking the
Mick! I guess I'll have to format my date fields to avoid any possible
errors.

Evi

Evi:

This is another one of Access's little foibles to confuse us Yurpeans. In
query design view dates are in the format of the local regional setting.
This even applies to date literals delimited by the # character. If you
switch to SQL view you'll see that its in mm/dd/yyy format there.

With a date like 24 April it doesn't matter of course as there is no month
24, but with a date like 5 April then #05/04/2008# this would usually be
understood to be 4 May regardless of the regional date format in use, but in
query design view does in fact represent 5 April where the regional date
format is set as dd/mm/yyyy in Windows control panel.

I do wonder whether MS simply overlooked this when 32 bit Access was
introduced. In version 2 the regional setting was respected both in
functions like CDate, and in date literals using the # date delimiter
character. With Access 95 date literals had to be in US short date or an
otherwise internationally unambiguous format. Maybe they simply forgot to
change it in the query designer. It doe seem to be a strange anomaly
otherwise.

Ken Sheridan
Stafford, England

We'll call your Datefield XDate

Instead of the IIF what about trying

DateNull: DateValue(NZ([XDate],#24/04/2008#))

(I've set the default date to 24/04/08 in this example - oddly, I typed it
in the way Access usually prefers ie #4/24/2008# but it changed it to a
'normal' date)
Evi

I created an expression to fill null fields in one of my tables with a
designated default date using the IIf function. The null fields were filled
but when I apply a <=[question] criteria, records containing the default
dates are not always included, depends on the criteria date. Does

this
issue

sound familiar?

Thanks!