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

 

[LittleSmudge]

I suspect this is the JET dates problem.

JET ignores the International Date format setting in your machine and always tries to apply the jumbled MM/DD/YYYY format to a date unless you tell it otherwise.

In SQL strings that you send to JET ( Ie Queries, RecordSets etc ) you need to preformat the Date string.

Eg.

DoCmd.RunSQL "SELECT * FROM tblDemo " _
           & "WHERE DateField " _
           & "BETWEEN #" & Format(cboStart, "Long Date") & "# " _
           & "AND #" & Format(cboEnd, "Long Date") & "# "

'ope-that-'elps






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.