DateDiff Query

[Enderjit Singh]

I have text fields with dates in the following format:

YYYYMMDD

I've set up a query to return the difference between three
sets of dates as follows:

Days: DateDiff("d",Nz([Date1],[Date2]),[Date3])

But it returns an Error result. I think it is do to with
the YYYYMMDD format, but I don't know how to resolve the
problem. I don't want to have to change the field into a
data field.

 

[Bruce M. Thompson]

Is this related to your earlier question regarding changing the "format" of your
fields (entitled "Nz Function")? If so, are these fields actually "Date" fields,
or are they Text? If they are Date fields, then changing your "Format" for the
dates using code in our earlier communication will actually change the values -
the way Date values are displayed is controlled by the "Format" property of the
control displaying the same (or your computer's regional settings, if no format
has been set). The actual Date value is a floating point number. From the
immediate window:

? cdbl(date())
37869
? cdbl(now())
37869.5577777778

In the second example, the "day" is represented by the whole number (the same
value returned in the first example) and the time is represented by the
fractional part of the date value.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<

I have text fields with dates in the following format:

YYYYMMDD

I've set up a query to return the difference between three
sets of dates as follows:

Days: DateDiff("d",Nz([Date1],[Date2]),[Date3])

But it returns an Error result. I think it is do to with
the YYYYMMDD format, but I don't know how to resolve the
problem. I don't want to have to change the field into a
data field.