[AccessD] Nz trap with dates in queries

Hi Stuart

Yes, in code it works - you can use VarType to confirm the data type
returned.

Neither have I had any issues in queries, it only shows when you apply a
sorting. But maybe I've never sorted on a column using Nz with anything else
than text.

/gustav

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
Sendt: 3. maj 2013 11:00
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Nz trap with dates in queries

It implicitly castes as a number with no problems.

I frequently use  things like:
TotalCharge: Cost * Qty  + NZ(FreightCharge,0)


On 2 May 2013 at 10:14, Charlotte Foust wrote:

> 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