Looking to make a query based on dates entered into a form. Here is what I have and its not working:
SQL View:

HAVING (((Recap.Date) Between [Forms]![State Mileage Finder]![Starting Date] And [Forms]![State Mileage Finder]![Ending Date]));

Here is what I entered into the Criteria field in Design View:

Between [Forms]![State Mileage Finder]![Starting Date] And [Forms]![State Mileage Finder]![Ending Date]

What am I doing wrong?

Try Greater than and less than.

Would this be a correct Criteria:

> [Forms]![State Mileage Finder]![Starting Date] And < [Forms]![State Mileage Finder]![Ending Date]

?

Edit: Didn't work.

[This message has been edited by Go3Team (edited 11-19-2001).]

Looks good to me, but you may want to add an equal sign to both parameters?

Are you checking only the value of one field?

I am checking the total mileage traveled in each state during a particular quarter, for fuel tax purposes. So the date query would be for example: 1/1/01 to 3/31/01 or 4/1/01 to 6/30/01. But the people using this would not know how to edit the query to perform the function, which is why I want to have them enter the dates into a form.

Yes. That is the correct approach. Have the users enter the beginning and entering date and then hit a command button that runs a query (or report based off that query).

Your criteria expression is correct if you are checking only the value of one field in the table the query is based off of.

The fields in the form the users enter the parameters should be unbound as well as the form itself.

Are you still unsure as to how to make this work?

Still stumped. I ended up having to put [Enter Starting Date] and [Enter Ending Date]. The problem I'm having now, is that I dont want to have it grouped by dates. I have it GROUP BY because I need to figure the sum of all miles traveled in each state. The date function is there because Miles have to be figured out quarterly. It wont let me GROUP BY [Truck Number].

If you could send your db to me....I will dummy a form for you. Its late in KY and I have to go. I can look tomorrow.

Send your db to jwindon@kde.state.ky.us

I will email you tomorrow morning.

An example is the best learning tool.

You have mail. Thanks.

With regards to grouping....my thought would be to create 2 queries....first one to return records within the date parameters and the second to return your sums.