In MS Access 97 I have a basic query based on a single table. When I use "Between [Start Date] and [End Date]" as the criteria in a date field it returns incorrect records.

It works correctly on date fields that are not calculated but not on the date field that is calculated. If I type in the actual dates in the criteria (instead of the prompts) it returns the correct records.

Is there a solution to this behaviour? I need the users to be able to input the dates.

Thanks.

P.S. I have tried using the base query as a subquery but I still have the same issue in the second query based on the subquery.

Are you sure that your calculation returns a date and not a text string?
ie.

=IIF([conditionistrue],#1/1/2007#,#7/4/2007#) ...would be ok but
=IIF([conditionistrue],1/1/2007,7/4/2007) ...would not, and
=IIF([conditionistrue],"1/1/2007","7/4/2007")...this would not work either

Take a look at this write-up (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D2/S5A318.asp) PS (When I test this, it only went to the main page, need to manually go to BETWEEN)

One thing with BETWEEN is that it is functionally equivalent to:
StartDate > AND < EndDate

If you want to include the StartDate and EndDates you may want to consider:
StartDate => AND <= EndDate

I did verify that the date is an actual date and not a string and since when I type the dates directly into the criteria it returns the correct records that also tells me that it must be a date.

I also tried using > or < and it also returns the incorrect records.

I searched the internet and haven't come up with anything on it, I'm hoping if we put all our brains together we'll come up with something.

Thanks.

Try declaring the data type of the two parameters (in query Design View, choose menu Query, Parameters...)
.

Thanks Jon K your solution worked! I wasn't aware you could declare the data type on parameters that easily.

You're great!