dd/MM/YYYY in SQL & Ctrl Panel but access read it as MM/dd/YYYY

[Guest]

I have a SQL String as followed

SELECT a.*, b.Model,b.Balance FROM BOMTransact a INNER JOIN BOMChild b ON
a.BOMChildID = b.BOMChildID WHERE a.TransactID >= 1 AND b.[Model] LIKE '%c%'
AND a.[CreatedDate] BETWEEN #01/09/2007# AND #07/09/2007# ORDER BY b.Model,
a.LotNo, a.CreatedDate

when execute it against access (or view it in design view) access read it as
MM/dd/YYYY

I have changed the control panel date format to dd/MM/YYYY but the access
still rigidly inteprete it as MM/dd/YYYY any ideas ?

 

[Marshall Barton]

I have a SQL String as followed

SELECT a.*, b.Model,b.Balance FROM BOMTransact a INNER JOIN BOMChild b ON
a.BOMChildID = b.BOMChildID WHERE a.TransactID >= 1 AND b.[Model] LIKE '%c%'
AND a.[CreatedDate] BETWEEN #01/09/2007# AND #07/09/2007# ORDER BY b.Model,
a.LotNo, a.CreatedDate

when execute it against access (or view it in design view) access read it as
MM/dd/YYYY

I have changed the control panel date format to dd/MM/YYYY but the access
still rigidly inteprete it as MM/dd/YYYY any ideas ?

You must use the #yyyy/mm/dd# or #mm/dd/yyyy# style of dates
regardless of your regional settings. Otherwise, your query
might not work on some machines.

 

[John W. Vinson]

when execute it against access (or view it in design view) access read it as
MM/dd/YYYY

Access always interprets nn/nn/nnnn dates as mm/dd/yyyy. It ignores the
control panel settings.

John W. Vinson [MVP]