Row Source for a Graph on a Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gojavid - 06 Mar 2008 13:18 GMT

Any ideas for a newbie as to why this statement won't work?

SELECT (Format([Date],"DDDDD")),Sum([ClalibratorOD]) AS
[SumOfClalibratorOD] FROM [tblBBiEIAQC] where ([Date] >= 1/1/08 AND
[DATE] <= 3/30/08) GROUP BY (Int([Date])),(Format([Date],"DDDDD"));

I don't get any error messages, but I also don't get any data.

Reply to this Message

Douglas J. Steele - 06 Mar 2008 14:44 GMT

Literal dates need to be delimited with # characters:

SELECT (Format([Date],"DDDDD")),Sum([ClalibratorOD]) AS
[SumOfClalibratorOD] FROM [tblBBiEIAQC] where ([Date] >= #1/1/08# AND
[DATE] <= #3/30/08#) GROUP BY (Int([Date])),(Format([Date],"DDDDD"));

Dates are actually stored as 8 byte floating point numbers, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day. When Access sees 1/1/08, it does the arithmetic, yielding 0.125, which
it sees as being 3:00 AM on 30 Dec, 1899. Similarly, 3/30/08 is seen as
being 0:18 AM on 30 Dec, 1899. Since I doubt your table has any data for
that time period (<g>), that's why you're getting nothing returned.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Any ideas for a newbie as to why this statement won't work?
>
[quoted text clipped - 3 lines]
>
> I don't get any error messages, but I also don't get any data.

Reply to this Message