Issue with query using Date originally in Excel, linked in to Access

[BFP]

Hi All.

I have created a table in Access (2003) that is linked to a spreadsheet in Excel. I have tried to tell Excel that the format of a the date column is indeed a date (m/d/yyyy h/mm/ss). (I have even tried to recreate the table in Access only so I could attempt to ensure a date format was applied.)

The issue I am seeing is: when I attempt to run a filter/query on a valid date range (selected using combo boxes linked to original data), I get an empty record set. My hunch is that, even though I tell Excel to "treat these cells as a date in this format", it ignores my format command and treats the data as text. (Don't know what could be happening when I copy the data in Excel and apply date format.)

Natrually, in Access, when I try to apply a filter, it views my "dates" as strings and does not know how to react to an "And" SQL query (with or without a Between).

If this info is in a FAQ or another post, I apologize...it is getting late and I did not see the answer.

Thanks in advanced.

BFP

 

[GingerR]

It's best to import Excel data then put it in a table that is formatted the way you want, exactly because of the reason you cite. You say to changed the "date format" of the field in Access, you must instead change the "DATA TYPE" from "text" to DATE/TIME.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html