If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
date format inconsistent
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 |
#2
|
|||
|
|||
date format inconsistent
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 (:-) "Starry" nospam wrote in message ... 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 |
#3
|
|||
|
|||
date format inconsistent
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] |
#4
|
|||
|
|||
date format inconsistent
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" wrote in message ... 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] |
Thread Tools | |
Display Modes | |
|
|