date in query

[Jean-Paul]

In my code I have following sql:

sql = "SELECT Afspraken.* FROM Afspraken WHERE Afspraken.Aankomst= 800
AND afspraken.datum_bezoek= #" &
Format(Forms![Bedrijven]!Knop29.Caption, "DD/MM/YYYY") & "#;"

this gives following result when I enter ? sql

SELECT Afspraken.* FROM Afspraken WHERE Afspraken.Aankomst= 800 AND
afspraken.datum_bezoek= #06/02/2009#;

This returns no record

When I put it in a query, nothing is returned either...

When I delete the date part, I get 5 records matching the "Aankomst=800"
criterium
When I manually add: 06/02/09 in the query-wizard
I get the correct record.
After enetring acces automaically adds the # before and after the date

When I then look at the query code it looks exactly the same as I had
before....

Who can explain waht happens

 

[John Spencer]

Date literals maust be entered in mm/dd/yyyy of yyyy/mm/dd format.

Try building the SQL statement this way


sql = "SELECT Afspraken.* FROM Afspraken WHERE Afspraken.Aankomst= 800
AND afspraken.datum_bezoek= #" &
Format(Forms![Bedrijven]!Knop29.Caption, "YYYY-MM-DD") & "#;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jean-Paul]

Problem solved... great help, but strange situation...

Thanks
JP

Date literals maust be entered in mm/dd/yyyy of yyyy/mm/dd format.

Try building the SQL statement this way


sql = "SELECT Afspraken.* FROM Afspraken WHERE Afspraken.Aankomst= 800
AND afspraken.datum_bezoek= #" &
Format(Forms![Bedrijven]!Knop29.Caption, "YYYY-MM-DD") & "#;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

In my code I have following sql:

sql = "SELECT Afspraken.* FROM Afspraken WHERE Afspraken.Aankomst= 800
AND afspraken.datum_bezoek= #" &
Format(Forms![Bedrijven]!Knop29.Caption, "DD/MM/YYYY") & "#;"

this gives following result when I enter ? sql

SELECT Afspraken.* FROM Afspraken WHERE Afspraken.Aankomst= 800 AND
afspraken.datum_bezoek= #06/02/2009#;

This returns no record

When I put it in a query, nothing is returned either...

When I delete the date part, I get 5 records matching the
"Aankomst=800" criterium
When I manually add: 06/02/09 in the query-wizard
I get the correct record.
After enetring acces automaically adds the # before and after the date

When I then look at the query code it looks exactly the same as I had
before....

Who can explain waht happens

 

[John Spencer]

Not all that strange.

MS is a US company and used US date specifications (mm-dd-yyyy) for date
literals. I suspect that doing otherwise would have been a rat's nest
(a big problem) in coding. The other format (yyyy-mm-dd) is the
standard for SQL.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Problem solved... great help, but strange situation...

Thanks
JP

Date literals maust be entered in mm/dd/yyyy of yyyy/mm/dd format.

Try building the SQL statement this way


sql = "SELECT Afspraken.* FROM Afspraken WHERE Afspraken.Aankomst= 800
AND afspraken.datum_bezoek= #" &
Format(Forms![Bedrijven]!Knop29.Caption, "YYYY-MM-DD") & "#;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================