DateAdd and greater than and less than date range criteria issue

[MPXJohn]

Hi all, I have a criteria that is giving me fits. In my query I have the following code [PurchaseDate: DateAdd("d",-7,[StartDate])] in the Field Line in Design View. In the first criteria line I have the following code [>#1/1/2015# And <[Enter Purchase Date]]. What is supposed to happen when the Query is opened is the user is prompted to enter a Purchase Date. The query will then look for Purchase Dates between 1/1/15 and the Entered Purchase Date minus 7 days and display that which falls under the range. This was working fine in January but as soon as we started entering February dates it stopped. The DateAdd function is working fine, but the [>#1/1/2015# And <[Enter Purchase Date]] is not. It displays all dates after 1/1/15 but does not stop at the date entered. Thanks for the help.

[June7]

Was this working last year? Did you previously have 1/1/2014 in the criteria?

Cannot replicate the issue. If you want to provide db for analysis, follow instructions at bottom of my post.

[MPXJohn]

This query was just created in the beginning of January 2015. January 9, 2015 to be precise. I just found out from our purchaser that it only ever worked for January. She tried putting in a February date when it was still January and it would not work then.

[June7]

Suggestion in previous post still applies.

Are international dates involved?

[rpeare]

try

between #1/1/2015# and cdate([Enter the Purchase Date])

[MPXJohn]

That worked rpeare. Thank you & June7 for your help.

[June7]

BETWEEN AND is inclusive for the range end dates. Your original expression did not include the range end dates. It should still have worked - it did in my tests.

But glad you have a solution.

[MPXJohn]

The actual range end date is entered in upon opening the query where the user is prompted to "Enter Purchase Date"
[>#1/1/2015# And <[Enter Purchase Date]]. I am glad the solution I received worked as well. I would still like to figure out why the other did not work some time in the future. Thanks.