Re: date format inconsistent
- From: "Starry" <nospam>
- Date: Mon, 14 May 2007 16:05:34 +0100
Many thanks John
You're spot on I had changed the code but not the query! Apologies for the
confusion.
Now produces:
SELECT DISTINCT Jobdata.Labname FROM Jobs LEFT JOIN Jobdata ON
Jobs.JobNumber = Jobdata.JobNumber WHERE (((Jobdata.Labname) Is Not Null)
AND ((Jobdata.Labweek) = #06/Jan/2008#) AND ((Jobs.Complete) = False));
date has been handled as per previous reply.
Thanks.
"John W. Vinson" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:g6n943pe9opvl4tmf9sbg43tb9mqkmr97e@xxxxxxxxxx
On Fri, 11 May 2007 17:48:41 +0100, "Starry" <nospam> wrote:
Despite reading all I can I cannot seem to work around this.
My query is flipping ambiguous dates to US format. If it is passed a date
of
say 23/08/2009 it works fine but given the following or anything where the
date and month can be changed it does! How can I prevent this? All related
fields etc are set as UK.
Date literals MUST be in either US mm/dd/yyyy format, or an unambiguous
format
such as yyyy-mm-dd or yyyy-mmm-dd. The query engine does *not* check or
respect the system date/time settings... period.
A date which makes no sense in mm/dd/yyyy format (23/08/2009) will be
flipped
if that makes it reasonable; but 12/08/2009 is December 8, not 12th
August.
There is no setting or formatting to change this behavior.
You'll need to coerce literal dates into an acceptable format: e.g.
strsql = "SELECT Jobdata.Labname"
strsql = strsql & " FROM Jobs LEFT JOIN Jobdata ON Jobs.JobNumber =
Jobdata.JobNumber"
strsql = strsql & " GROUP BY Jobdata.Labname, Jobdata.Labweek,
Jobs.Complete"
strsql = strsql & " WHERE (((Jobdata.Labname) Is Not Null) AND
((Jobdata.Labweek) = #" & Format(dteWeekend, "mm\/dd\/yyyy") & "#) AND
((Jobs.Complete) = False));"
Note that I changed HAVING to WHERE - the WHERE clause filters records
*before* calculating the totals, HAVING calculates it after.
On looking again - *why* are you using a Group By at all? You're not
counting,
or summing, or averaging anything! If you just want the labname, use a
Select
query (with no totals) and set its Unique Values property to Yes.
John W. Vinson [MVP]
.
- References:
- date format inconsistent
- From: Starry
- Re: date format inconsistent
- From: John W . Vinson
- date format inconsistent
- Prev by Date: Cartesian Help Please
- Next by Date: Re: Return multiple values
- Previous by thread: Re: date format inconsistent
- Next by thread: Return multiple values
- Index(es):
Relevant Pages
|
