Between function ignores years, after performing date addition

From: LauraB (LauraB_at_discussions.microsoft.com)
Date: 11/10/04 

Date: Tue, 9 Nov 2004 16:09:02 -0800

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

Relevant Pages

  • Re: Query comparing dates
    ... If not, the calculation can take place in the query, right? ... If there is an expiration date available, the query will need to compare today's date with the expiration date and show any records whose expiration date is three months in the future, using the month and year only. ... I apologize if this is not explained clearly. ...
    (microsoft.public.access.queries)
  • Query comparing dates
    ... I need help in designing and creating a select or parameter query. ... calculation can take place in the query, ... date with the expiration date and show any records whose expiration ...
    (microsoft.public.access.queries)
  • Re: date calculation
    ... > I need to find out the expiration date then print on the report to alert ... query to do the calculation and print the date ...
    (microsoft.public.fox.programmer.exchange)
  • Re: query for tag renewal
    ... You will see all records with an expiration date in that range. ... The above criteria gave me all tagexpirations in the table. ... When you make a query based on the table containing ExpDate, ...
    (microsoft.public.access.queries)
  • Re: Show only certain records in a query by date and time
    ... I had thought about those 2 at one time but for some reason ... answers using Jeff Conrad's favorite sites, and the help file in access ... I have a query with a dtt field that has therapy end dates, ... expiration date in the field and also records that have expiration dates ...
    (microsoft.public.access.gettingstarted)