[AccessD] Nz trap with dates in queries

Hi Charlotte

I've never seen an issue because of this. It should only be so if you in a
query sort on an expression using it.

/gustav

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Charlotte Foust
Sendt: 2. maj 2013 19:14
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Nz trap with dates in queries

That issue has been around forever, Gustav.  Nz always needs to be cast into
a datatype unless you're looking for a string.

Charlotte

On Thu, May 2, 2013 at 12:08 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi all
>
> I browsed this article:
>
> http://www.fmsinc.com/MicrosoftAccess/query/sort/multiple-dates.htm
>
> and much to my surprise the note about the Nz trap is true.
>
> When used in a query, this expression returns a string:
>
>     DateNotNull: Nz([DateFieldWithNulls], Date())
>
> You can easily see it, because if you apply a date format to the 
> column, it has no effect.
>
> The real strange part, however, is that if you add two other columns:
>
>     TypeDate: VarType([DateFieldWithNulls])
>     TypeDatez: VarType(Nz([DateFieldWithNulls], Date()))
>
> the first returns 7 (DateTime) and 1 (Null) while the other returns 7s 
> only ... no 8s for string!
>
> The workaround is either of these methods - the first corrects the 
> result, the second (from the article) uses a good, old alternative:
>
>     DateNotNull: CDate(Nz([DateFieldWithNulls], Date()))
>     DateNotNull: IIf([DateFieldWithNulls] Is Null, Date(),
> [DateFieldWithNulls])
>
> /gustav