Keep date as date in a field. Why keep dates as strings? A string will be used as a string by any program.
Now, if you keep it as date,
Between #3/4/2004# And #3/5/2004#
your single quotes tell access "treat this as a string".
I notice that you have some type of European date format. Use the Access query builder to write your query. your sintax is wrong. ine would look like this:
WHERE (((Table1.date1) Between #3/4/2004# And #3/5/2004#));