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.
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.