selecting date values using vb6

[Jimbob]

I'm attempting to use 2 masked edit boxes to input a date range (using the format dd/mm/yy) for searching an access database. my app generates a query similar to the one below: -

select * from onsites where date >= #02/04/00# and date <= #30/04/00# order by date;

(onsites being a table, and date being a value within it)

my problem is that the query creates a recordset containing ALL records in the table, unless the dd part of the date is >= to the mm part of the date, eg 04, 05, 06 etc /04/00, in which case the query works as i would expect.

Initially I thought that the date format may be set to mm/dd/yy, but the date value can be > 12, so perhaps not.

I've tried removing the >= and just specifying = in the query, but this made no difference.

I have been able to select an exact date using a LIKE clause, but really need to select a range of dates.

has anyone got any ideas what i'm doing wrong here, or is it just a flaw in the database engine...??

[Ianpbaker]

There is a nice easy way of getting around this.
Instead of using the greater and less than try using the BETWEEN clause as Follows

select * from onsites where date BETWEEN #02/04/00# and #30/04/00# order by date

any problems reply back

[Jimbob]

the between clause doesnt seem to make much difference, i've tried it on a table containing 1 record for each working day in may

running: -

select * from onsites where date BETWEEN #01/05/00# and #04/05/00# order by date;

should (thoretically...) return 01/05, 02/05, 03/05 and 04/05

if i run the following: -

select * from onsites where date BETWEEN #01/05/00# and #05/05/00# order by date;

5 records are returned 01/05, 02/05 etc

[bsmith]

Try using the FORMAT() function to format all of the dates that you are using the same way. It sounds to me that the dates you are using are being interpruted as mm/dd/yyyy. This may be due to your setting in windows?

[Jimbob]

the format() function worked!!!

thank you both so much for your help.