Between function ignores years, after performing date addition

[Guest]

I have a query that performs the following date calculation:
ExpireDate: DateAdd("m",[Months to Expriration],[LastPurchase])

And I would like to filter ExpireDate for a user entered date range. To do
this I used the following in the criteria field:
Between Nz([Enter expiration start date],#1/1/1900#) And Nz([Enter
expiration finish date],Date())

This solution works partially. I get returns that pay attention to the
month and the day but not the year. For example when I enter "10/1/2004" for
start and "10/31/2004" for finish, I get a return with dates in October, but
for both years 2003 and 2004.
Additionally:
1) I've tried "10/1/04" and "10/31/04", with the same result.
2) I believe I've used the same Between function above elsewhere, and I
thought it was working fine. Do I need to format after doing a DateAdd to
get correct results?

Any suggestions appreciated!
--Laura

 

[John Vinson]

1) I've tried "10/1/04" and "10/31/04", with the same result.
2) I believe I've used the same Between function above elsewhere, and I
thought it was working fine. Do I need to format after doing a DateAdd to
get correct results?

Format() is going the WRONG way - it will convert the date to a Text
String which sorts alphabetically not chronologically.

Try using CDate() around each parameter instead.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

CDate() around the user entered parameters did the trick!
Thanks!
--Laura