Date format in Access

[Manish]

hi friends,
I am facing one prob? i am passing this script from vb application to access database.
Select * from v_VoucherSummary where DATE between #04-11-1999# and #06-11-1999#

it shld give me all the records between the dates 4th november 1999 and 6th november 1999.
but it don't return any record/s. I run the same script using query in access directly It seams that access change the format for the dates bcz when I view the query in sql format it show me the original script but when u use the grid view its shows me criteria as follows
between #11-04-1999# and #11-06-1999#
Can anyone help me how can i prevent this change in the date format???
Thanx Manish

[Pratyush]

Access automatically interprets the dates in system format.
So it is always confusing to compare the dates.Access also converts the given dates into system format.So it is better to do string comparison in yyyymmdd format.
Like your query :
Select * from v_VoucherSummary where DATE between #04-11-1999# and #06-11-1999#

can be written as
Select * from v_VoucherSummary where format(DATE,"yyyymmdd") between "19991104" and "19991106"

This will return the correct value.