simple "between dates" query

[mejia.j88]

hi all,

i have a report that is based on a query with the following criteria:

Between [forms]![frm_postetchreport]![txt_start] And [forms]![frm_postetchreport]![txt_end]

if i choose the start and end date to be the same, my report comes up blank,
if i choose the same start date and a later end date, then the data for the start day shows up.

is my criteria wrong?



thanks,
j

[aytee111]

Sometimes when the date has the time on it then this happens - the date becomes 01/01/2012 00:00:00. So your from date and to date will both be the same, i.e. zero seconds past midnight. Your search field will need to formatted to mm/dd/yyyy, maybe all the dates will.

[mejia.j88]

sorry what do you mean by search field, the field in the query which contains this criteria?

[aytee111]

Yes, the date field you are wanting to match in between those dates.

[June7]

Yes, if the field includes a time component, it must handled:

1. Include time in the criteria.
Between [forms]![frm_postetchreport]![txt_start] & " 8:00:00 AM And " & [forms]![frm_postetchreport]![txt_end] & " 0:00:00 PM"
or

2. Format the field to drop the time part and filter only by the date. Just setting the format property in table is not enough (this doesn't change the underlying data). Use Format function to extract the date part and apply search criteria to this constructed field.
CDate(Format(datefield, "mm/dd/yyyy")) Between [forms]![frm_postetchreport]![txt_start] And [forms]![frm_postetchreport]![txt_end]