How to use a date range

[wgma]

I need to get records between two dates. Here is my query:

SELECT WGMAHIST_TIMEDTY.TDYPT, WGMAHIST_TIMEDTY.TDYCO, WGMAHIST_TIMEDTY.TDYSSN, WGMAHIST_TIMEDTY.TDYFND
FROM WGMAHIST_TIMEDTY
WHERE WGMAHIST_TIMEDTY.TDYSSN = 464299266
AND WGMAHIST_TIMEDTY.TDYEDT >= #06/29/2007#
AND WGMAHIST_TIMEDTY.TDYEDT <= #09/21/2007#
AND WGMAHIST_TIMEDTY.TDYPT = 1
AND WGMAHIST_TIMEDTY.TDYCO = 8

I am obviously doing it wrong because I keep getting an error that complains about the key work BETWEEN.

How can I make this work? I am using MS Access 2007.

Thanks,
wgma

 

[Rabbie]

Try using this

SELECT WGMAHIST_TIMEDTY.TDYPT, WGMAHIST_TIMEDTY.TDYCO, WGMAHIST_TIMEDTY.TDYSSN, WGMAHIST_TIMEDTY.TDYFND
FROM WGMAHIST_TIMEDTY
WHERE WGMAHIST_TIMEDTY.TDYSSN = 464299266
AND (WGMAHIST_TIMEDTY.TDYEDT Between #06/29/2007# AND #09/21/2007#)
AND WGMAHIST_TIMEDTY.TDYPT = 1
AND WGMAHIST_TIMEDTY.TDYCO = 8

Hope this helps

 

[wgma]

Thanks, but that didn't work. I still get the same error message.

 

[Rabbie]

Is the field WGMAHIST_TIMEDTY.TDYEDT in Date format. if not this would cause an error. Also can you post the exact error message you got. This may well highlight the problem.

 

[wgma]

The exact message is:

ODBC -- call failed.
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0401 - Comparison operator BETWEEN operands not compatible. (#-401)

I have an ODBC connection to a DB2 database.

 

[Rabbie]

Now I have the full story I suspect the problem is different date formats in the DB2 database compared to Access. I am afraid I don't know DB2 so I can't help you with this. Sorry