hi all,
hope you can help. I have an ms access database, in the database is a table which contains a date. I have an asp page which takes each date (select distinct) nd places these dates into a select drop don wn box. In the database the dates are displayed in the formay dd/mm/yyyy. When the asp script runs the date in the drop down list is displayed as dd/mm/yy. Ive checked the locale settings and these seems fine. Anyway in the next page im using a search between sql statement to search the records by date, however this produces no records. I think this is because of the above problem with the date format. Is this correct, and if it is anyone know how i can solve it.

Many thanks

Hello Paul,
I had this type of problems for a long time. Now I have solved it fully using the following method.

When inserting date into access database, I make it into a string of the format
MM/DD/YYYY and put it in between # and #.
i.e. Use the query

"INSERT INTO <table> (datefield) VALUES(#" & <monthcombovalue> & "/" & <datecombovalue> & "/" & <yearcombovalue> & "#)"

While fetching the result from database, I am doing it in following way.

datevalue=day(rs("datefield"))
monthvalue=month(rs("datefield"))
yearvalue=year(rs("datefield"))

While searching, in the select query I am putting it like this.

"SELECT * FROM <table> WHERE datefield BETWEEN #" & <monthcombovalue1> & "/" & <datecombovalue1> & "/" & <yearcombovalue1> & "# AND #" & <monthcombovalue2> & "/" & <datecombovalue2> & "/" & <yearcombovalue2> & "#)"



This will work independent of the locale setting at server.

Please try this and let me know.

Also, may I know whether there are any potential problems for this method ? (AFAIK, I could not find anything.)

BTW, date format should be shortdate in access table.

Paul,

Also remember that you got to avoid # around date values for searching in the database, if you are not using access.

Cheers!

_________________________
- Vijay G
Strive for Perfection