Multiple entries per day..

[cdbiggs]

I've got a database that has multiple entries per day. I've created a query
that gets date inputs from a form, then creates a report that shows the
entries for everything between the 2 dates that are entered (via the form).
However, when I run the query, it only pulls one or two items from each day.
I'm trying to find out why?? Here's my query:

Between [forms]![form-Report-Parameters]![txtDateFrom] AND
[forms]![form-Report-Parameters]![txtDateTo]

It's pulling from the correct table, however, only one or two items is
retrieved...like I said. Any help would be appreciated.

Thanks

 

[John Vinson]

I've got a database that has multiple entries per day. I've created a query
that gets date inputs from a form, then creates a report that shows the
entries for everything between the 2 dates that are entered (via the form).
However, when I run the query, it only pulls one or two items from each day.
I'm trying to find out why?? Here's my query:

Between [forms]![form-Report-Parameters]![txtDateFrom] AND
[forms]![form-Report-Parameters]![txtDateTo]

It's pulling from the correct table, however, only one or two items is
retrieved...like I said. Any help would be appreciated.

Thanks

Might it be getting all the records EXCEPT for those on the last day?
This is typical.

Access stores date/time values as Double Float numbers, a count of
days and fractions of a day (times) since midnight, December 30, 1899.
As such, the date in txtDateTo corresponds to a date with a zero time
portion - meaning midnight at the beginning of that day. Any event at
a time later than midnight won't be found (because 32885.412 is
greater than 32885.000).

Try a criterion (covering several possible problems) of

[Datefield] >= CDate([forms]![form-Report-Parameters]![txtDateFrom])
AND [Datefield] < DateAdd("d", 1,
CDate([forms]![form-Report-Parameters]![txtDateTo]))

If that's not it - please post the SQL view of your query and examples
of some of the date/time values being missed for a given set of
criteria.

John W. Vinson[MVP]

 

[Jerry Whittle]

John's answer is probably the best. However there could be other reasons. If
the query in question has more than one table, it could be that you need a
left join instead of an inner join if one table contains records that can't
be linked to the other table.

Posting the entire SQL statement would have helped us here.

Another possibility is that the dates are being stored in a text field
instead of a Date/Time field. That can cause all sorts of problems.