Using a "between" date range where the table is filled with dates populated by "Now()"

[snipe]

Hey guys,

When a user uses the entry from to input records, a [DateAdded] field is automatically added with Now() so I can get a date/time of when the record was added.

I have a few reports that use [DateAdded]. For instance, I have a report where the user inputs a date range into a form and a report is ran off that date range that shows how many records were entered.



If I input a few records and then run the date range, any record that was entered today doesn't get counted. I recently changed the [DateAdded] from a date only to a date/time. Now that it is switched, that report (along with a few others) don't pull any records for today.

Any idea how to fix this?

Thanks!

[NTC]

In the most general terms: if the query criteria is 1 element - then for records to be returned it must exactly match that element. So in the case of Now() it must be identical down to the second.

But if it is 2 elements (i.e. between) such as >= X and <= Y then you get a range to match against and it is much more forgiving.

So you skin the cat either by aligning everything to not have time and be date only where you can have 1 element searches - or - you set up 2 element ranges to corral the entire time range of a date...

Depending on whether or not you can control or change data that is already in the database may dictate your approach....

[June7]

Create fields in query with expression that extracts the date part and apply filter criteria to that constructed field.

CDate(Format([datefield], "mm/dd/yyyy")) AS DateOnly


DoCmd.OpenReport "report name", , , "DateOnly BETWEEN #" & Me.tbxStart & "# AND #" & Me.tbxEnd & "#"

[snipe]

Got it. Thanks you two. Appreciate it as always.