Re: Report/Query not selecting specific records

I don't care much about the formatting of a field in the underlying table.
The key issue as I suggested is the actual data type.

"7/01/07" could be July 1, 2007 or January 7, 2007 depending on where you
live.
Did you try open qrylfta and then the form to enter dates? Then see if the
results are as expected?

--
Duane Hookom
MS Access MVP

"Loni - RWT" <LoniRWT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6862BDF8-4507-4CA0-BB24-B212B1B1D559@xxxxxxxxxxxxxxxx
The date in the underlying table is formatted as date/time, short date.
I'm
assuming this is what you are talking about. I'm unaware of any field
formatting within a query. As far as American formatting, I'm assuming
that
you mean the date is being entered as 7/01/07, which is the way I'm doing
it.

This is a saved query "qryIfta" This query is the record source for the
report & the report only serves as a vehicle to display the results of the
query.
BTW...I don't know if it makes a difference, but I'm using Access 2000.

"Duane Hookom" wrote:

I would save the sql as a saved query and then open the form to enter
date
values. Then display the results in the query to see if it looks good.
Are
you using actual date data types and using Amercan format?

--
Duane Hookom
Microsoft Access MVP


"Loni - RWT" wrote:

I have a dialog form in which I enter the dates for the report. An
okay
button sets the form's visible property to false and opens the report.
The
query then gets it's criteria from the form. However, if I open the
query
and supply the correct dates, it still returns all records. The only
change
made to the query since last quarter when all worked fine was to change
the
date criteria prompt to pull these from the form. Here si the SQL for
the
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.

"Duane Hookom" wrote:

Can you share how you are using the date entries to filter the
report? This
might require you to provide either the code that opens the report or
the
report's record source SQL view.

--
Duane Hookom
Microsoft Access MVP


"Loni - RWT" wrote:

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!



.