date in query

J

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
 
J

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
'====================================================
 
J

Jean-Paul

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

Thanks
JP

John said:
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 said:
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
 
J

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
'====================================================


Jean-Paul said:
Problem solved... great help, but strange situation...

Thanks
JP

John said:
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
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top