date format inconsistent

[Starry]

Hi

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.

Query is as follows:

dteWeekend = .......object resulting in 06/09/2009.....

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 & " HAVING (((Jobdata.Labname) Is Not Null) AND
((Jobdata.Labweek) = #" & dteWeekend & "#) AND ((Jobs.Complete) = False));"

Resulting SQL...which is fine:

SELECT Jobdata.Labname FROM Jobs LEFT JOIN Jobdata ON Jobs.JobNumber =
Jobdata.JobNumber GROUP BY Jobdata.Labname, Jobdata.Labweek, Jobs.Complete
HAVING (((Jobdata.Labname) Is Not Null) AND ((Jobdata.Labweek) =
#06/09/2009#) AND ((Jobs.Complete) = False));

But then when pasted into the grid or run from within my code the date flips
to 09/06/2009.

I discovered one thread where the solution was to use dd/mm/yy throughout
but this affects other aspects and would create significant work.

How can I stop this any ideas?

Access2003 uk settings

 

[Starry]

Hi, fixed it using ..... Format(dateVariable,"dd/mmm/yyyy") ...... in the
SQL to force the month to SEP etc. The query no longer flips the date -)

 

[John W. Vinson]

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]

 

[Starry]

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.