Report/Query not selecting specific records

[Guest]

I have a database for tracking miles traveled and fuel purchased in each
state. I have a report based on a query to total miles and gallons for each
state in a quarter. Last quarter, the report worked perfectly. This
quarter, for some reason, it's bringing in all records that have ever been
entered instead of just the previous quarter's records. The only difference
is that I changed the way the report opens. It used to required entering the
beginning and ending dates multiple time. So, I created a form from which to
pull the starting and ending date. I have opened the query on which the
report is based and from there, input the dates in three different formats:
7/01, 7/01/07, 7/01/2007, etc, and it is still pulling all records. Please
give me an idea on how to fix this!! I have a deadline on doing my quarterly
reports & I don't want to have to think about figuring all of this manually.
That will take till next year this time!!
Thank you for sharing your wisdom!

 

[Al Campagna]

Loni,
Several ways to do that...

If your dialog form was called frmDialog, and on that form, there were
two unbound text controls, StartDate and End Date then...

In the query behind your report, have it look to the open form (form must
remain open during report) to create a criteria against your report's date
field.

Between Forms!frmDialog!StartDate AND Forms!frmDialog!EndDate

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[Guest]

That is the way I have it set up. The form is a dialog form that an okay
button sets the form's visible property to false and opens the report. The
report is based on a query and the date criteria comes from the parameters
form. In tropubleshooting the problem, I opened the query and supplied the
correct dates and it still returned all records.

 

[Al Campagna]

Loni,
.While your working on this problem, leave the form *Open and Visible.*
.The report window should open on top of the form, and when closed, the
form is again
.What is your form name... and your form field names?
.Please show (copy and paste) your query criteria "exactly" as you have
it...

Also, I'm assuming you're entering legitimate date values in m/d/yy or
mm/dd/yy format on the form.

In troubleshooting the problem, I opened the query and supplied the
correct dates and it still returned all records.

That's always a good idea...
Did you let the query prompt you for both dates, or did you hard wire two
dates in the criteria?

If the query results won't respond to two legitimate date parameters,
then the problem must lie in the query itself.

 

[Guest]

My form name is frmReportParameters. My field names are StartDate and
EndDate.

My query criteria is as follows:

=[Forms]![frmReportParameters]![StartDate] Or <=[Forms]![frmReportParameters]![EndDate]

Yes, I am using legitimate date values. And when I bypassed the form and
supplied the dates directly to the query, I let the query prompt for the
dates.

Just for your information, this is sql for that query:

SELECT Sum(tblTripDetails.Gallons) AS SumOfGallons,
Sum(tblTripDetails.LegMiles) AS SumOfLegMiles, tblTripDetails.StateMiles
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripDate)>=[Forms]![frmReportParameters]![StartDate] Or
(tblTrips.TripDate)<=[Forms]![frmReportParameters]![EndDate]))
GROUP BY tblTripDetails.StateMiles
ORDER BY tblTripDetails.StateMiles;

Thanks for your help! BTW...Don't know if it matters, but I'm using Access
2000

 

[Al Campagna]

Loni,
I think I see the problem, as to why you're getting all records... even
with a legitimate date range.

Your SQL....
WHERE (((tblTrips.TripDate)>=[Forms]![frmReportParameters]![StartDate] Or
(tblTrips.TripDate)<=[Forms]![frmReportParameters]![EndDate]))

Should be...
WHERE (((tblTrips.TripDate)>=[Forms]![frmReportParameters]![StartDate] AND
(tblTrips.TripDate)<=[Forms]![frmReportParameters]![EndDate]))

01/01/2011 passes your OR test, as it is > StartDate
01/01/1945 passes your OR test, as it is < EndDate

Your date MUST pass BOTH the > Start AND < EndDate criteria...

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

My form name is frmReportParameters. My field names are StartDate and
EndDate.

My query criteria is as follows:

=[Forms]![frmReportParameters]![StartDate] Or
<=[Forms]![frmReportParameters]![EndDate]

Yes, I am using legitimate date values. And when I bypassed the form and
supplied the dates directly to the query, I let the query prompt for the
dates.

Just for your information, this is sql for that query:

SELECT Sum(tblTripDetails.Gallons) AS SumOfGallons,
Sum(tblTripDetails.LegMiles) AS SumOfLegMiles, tblTripDetails.StateMiles
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripDate)>=[Forms]![frmReportParameters]![StartDate] Or
(tblTrips.TripDate)<=[Forms]![frmReportParameters]![EndDate]))
GROUP BY tblTripDetails.StateMiles
ORDER BY tblTripDetails.StateMiles;

Thanks for your help! BTW...Don't know if it matters, but I'm using
Access
2000

Loni,
.While your working on this problem, leave the form *Open and
Visible.*
.The report window should open on top of the form, and when closed,
the
form is again
.What is your form name... and your form field names?
.Please show (copy and paste) your query criteria "exactly" as you
have
it...

Also, I'm assuming you're entering legitimate date values in m/d/yy or
mm/dd/yy format on the form.

That's always a good idea...
Did you let the query prompt you for both dates, or did you hard wire
two
dates in the criteria?

If the query results won't respond to two legitimate date parameters,
then the problem must lie in the query itself.

 

[Guest]

I appreciate all of your assistance. I actually just figured that out. I
apparently made a typo when I added the form for inputing the dates on the
query. Once I changed the OR to AND, everything worked perfectly. Sometimes
you look at stuff so much that you miss the obvious. Once I stepped away
from it for a while, it was clear. Again, thanks for all of your help.

Loni,
I think I see the problem, as to why you're getting all records... even
with a legitimate date range.

Your SQL....
WHERE (((tblTrips.TripDate)>=[Forms]![frmReportParameters]![StartDate] Or
(tblTrips.TripDate)<=[Forms]![frmReportParameters]![EndDate]))

Should be...
WHERE (((tblTrips.TripDate)>=[Forms]![frmReportParameters]![StartDate] AND
(tblTrips.TripDate)<=[Forms]![frmReportParameters]![EndDate]))

01/01/2011 passes your OR test, as it is > StartDate
01/01/1945 passes your OR test, as it is < EndDate

Your date MUST pass BOTH the > Start AND < EndDate criteria...

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

My form name is frmReportParameters. My field names are StartDate and
EndDate.

My query criteria is as follows:

=[Forms]![frmReportParameters]![StartDate] Or
<=[Forms]![frmReportParameters]![EndDate]

Yes, I am using legitimate date values. And when I bypassed the form and
supplied the dates directly to the query, I let the query prompt for the
dates.

Just for your information, this is sql for that query:

SELECT Sum(tblTripDetails.Gallons) AS SumOfGallons,
Sum(tblTripDetails.LegMiles) AS SumOfLegMiles, tblTripDetails.StateMiles
FROM tblTrips INNER JOIN tblTripDetails ON tblTrips.TripID =
tblTripDetails.TripID
WHERE (((tblTrips.TripDate)>=[Forms]![frmReportParameters]![StartDate] Or
(tblTrips.TripDate)<=[Forms]![frmReportParameters]![EndDate]))
GROUP BY tblTripDetails.StateMiles
ORDER BY tblTripDetails.StateMiles;

Thanks for your help! BTW...Don't know if it matters, but I'm using
Access
2000

Loni,
.While your working on this problem, leave the form *Open and
Visible.*
.The report window should open on top of the form, and when closed,
the
form is again
.What is your form name... and your form field names?
.Please show (copy and paste) your query criteria "exactly" as you
have
it...

Also, I'm assuming you're entering legitimate date values in m/d/yy or
mm/dd/yy format on the form.

In troubleshooting the problem, I opened the query and supplied the
correct dates and it still returned all records.
That's always a good idea...
Did you let the query prompt you for both dates, or did you hard wire
two
dates in the criteria?

If the query results won't respond to two legitimate date parameters,
then the problem must lie in the query itself.


That is the way I have it set up. The form is a dialog form that an
okay
button sets the form's visible property to false and opens the report.
The
report is based on a query and the date criteria comes from the
parameters
form. In tropubleshooting the problem, I opened the query and supplied
the
correct dates and it still returned all records.

:

Loni,
Several ways to do that...

If your dialog form was called frmDialog, and on that form, there
were
two unbound text controls, StartDate and End Date then...

In the query behind your report, have it look to the open form
(form
must
remain open during report) to create a criteria against your report's
date
field.

Between Forms!frmDialog!StartDate AND Forms!frmDialog!EndDate

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your
life."


I have a database for tracking miles traveled and fuel purchased in
each
state. I have a report based on a query to total miles and gallons
for
each
state in a quarter. Last quarter, the report worked perfectly.
This
quarter, for some reason, it's bringing in all records that have
ever
been
entered instead of just the previous quarter's records. The only
difference
is that I changed the way the report opens. It used to required
entering
the
beginning and ending dates multiple time. So, I created a form from
which
to
pull the starting and ending date. I have opened the query on which
the
report is based and from there, input the dates in three different
formats:
7/01, 7/01/07, 7/01/2007, etc, and it is still pulling all records.
Please
give me an idea on how to fix this!! I have a deadline on doing my
quarterly
reports & I don't want to have to think about figuring all of this
manually.
That will take till next year this time!!
Thank you for sharing your wisdom!