Trouble with Between Criteria in Parameter Query

[Xcalibur]

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.

 

[CraigDolphin]

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

 

[Steve R.]

Take a look at this write-up 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

 

[Xcalibur]

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.

 

[Jon K]

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

 

[Xcalibur]

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

You're great!