Query via Excel

[mikeymay]

I have a query in Access which selects records with a date value in between 2 dates from a field called 'Period', e.g. 01 Jan 07 to 01 Apr 07 (>="01 Jan 07" AND <="01 Apr 07") which returns all the records I would expect, i.e all records between 01 Jan 07 and 01 Apr 07.

The 'Period' data/values are put together using 2 fields

• strMonth e.g. Jan, Feb, Mar, etc
• strYear e.g. 2006, 2007, etc

and built using the expression

Period: CDate("01 " & Trim([tblBookIt]![strMonth]) & " " & [tblBookIt]![strYear])

returning a valid date value for each record returned in the query

When I run this query using 'Get External Data' in Excel using the Query Wizard, the Filter Data option doesn't give me all the period values that are on the table. I have 2 years of data in the table, Jan 06 to Feb 08, each record having a Month (strMonth) and Year (strYear) field but running the query in Excel only gives me the option to select Mar 07 to Feb 08.

I am struggling to work out why when using Excel to run the query it seems to be ignoring all data with a date value prior to 01 Mar 07. I know this query is very specefic to my database which you don't have access to, but am hoping someone is aware of what I may be doing wrong.


Thanks

 

[DCrake]

Try using the following:

Between #01 Apr 07# And #01 Jul 07#

CodeMaster