Select Sql Date Problem

[wazza]

Hi

I have used sql in vba to retrieve records between 2 dates.

i.e. between #02/04/2003# AND #06/04/2003# (ddmmyyyy)

The dates in the underlying table is set to general. However, for some reason the records retreieve do not reflect my criteria.... instead i get records between

04/02/2003 AND 04/06/2003 (mmddyyyy)

the dates in thw sql appear to be correct... ???

rgrds

 

[Mile-O]

Why are you using VBA in the first instance?

 

[Rich]

between Format(#02/04/2003#,"mm/dd/yyyy") AND Format(#06/04/2003#," mm/dd/yyyy")

 

[wazza]

im using vb because ive developed some type of wizard form which querys records to display in a listbox from user input. im then attaching the sql to the rowsource of the listbox.

so i can actually define the format of the date in sql....? i will try that..

ive just noticed that if i write #3/4/2003# instead of #03/04/2003# this works... but i dont understand why - when the underlying tables format is specified as dd/mm/yyyy isnt this inherited through to the query?

rgrds

 

[Mile-O]

You can define the format of a field in the query design grid too.

 

[Mile-O]

when the underlying tables format is specified as dd/mm/yyyy isnt this inherited through to the query?

A format is just for displaying data. Although your dates will show up in UK format, they are still US format.

 

[wazza]

ok, i thought it might be something to do with the US..

ive got it working thanks!

 

[EMP]

Whenever you surround a date by the # signs, Access reads the date in the "m/d/yyyy" format.

So between #02/04/2003# AND #06/04/2003# is between Feb 4, 2003 and Jun 4, 2003