date format

[Jean-Paul De Winter]

Hi,
I have a table with a field called "Datum"
Here the date of records are stored in format DD/MM/YYYY

In my code I wrote:

Dim Starten as Date
Dim SQL as string

then in the code itself I wrote:
sql = "SELECT Agenda.datum FROM agenda WHERE Agenda.datum=#" & starten "#;"

This returns
SELECT Agenda.datum FROM agenda WHERE Agenda.datum=#5/12/2005#;

No record is found because finally access seems to look for dates in the
format MM/DD/YYY

What to do?

Thanks

 

[John Vinson]

Hi,
I have a table with a field called "Datum"
Here the date of records are stored in format DD/MM/YYYY

Not really. The date is *STORED* as a Double Float number, a count of
days (and fractions of days, if you have a time portion) since
midnight, December 30, 1899. The Format assigned to a field only
controls how it is *displayed*, not what's stored.

In my code I wrote:

Dim Starten as Date
Dim SQL as string

then in the code itself I wrote:
sql = "SELECT Agenda.datum FROM agenda WHERE Agenda.datum=#" & starten "#;"

This returns
SELECT Agenda.datum FROM agenda WHERE Agenda.datum=#5/12/2005#;

No record is found because finally access seems to look for dates in the
format MM/DD/YYY

What to do?

Use the American mm/dd/yyyy format, or an unambiguous format such as
dd-mmm-yyyy or yyyy.mm.dd; for instance,

sql = SELECT Agenda.datum FROM agenda WHERE Agenda.datum=#" & _
Format(starten, "mm/dd/yyyy") & "#;"

John W. Vinson[MVP]

 

[Jean-Paul De Winter]

Problem solved... thank you so much John
JP

John Vinson schreef: