Date range criteria

[Frustrated query user.]

I am passing date values from text boxes on forms into
query criteria. The field name in the query is:
"Critical Date: CDate(DateAdd("d",-[DaysNotice],
[EndDate]))". [EndDate] is a date and DaysNotice is an
integer.

The query criteria for [Critical Date] is:
"Between [Forms]![inpfrmReport]![txtBeginDate] And
[Forms]![inpfrmReport]![txtEndDate]". [txtBeginDate] and
[txtEndDate] are both formatted as dates.

When I remove the above criteria line from the query, it
returns the one record it is supposed to. And, when I
hard code the Begin and End Dates in the criteria
(Between #1/1/2005# And #12/31/2005#) it also returns the
matching record. BUT, when I leave the Form.Control path
above, the record DOES NOT RETURN. Several variants of
the above have been tried, such as "#" & before and after
each control name, and nothing seems to work. Can someone
please help me understand this problem and get around it??

Thank you in advance.

 

[Dale Fye]

Frustrated,

1. The first think I would check is to determine what is actually in your
EndDate field. If this field is being filled with date and time data, then
your criteria may not be giving you everything you expect.

Try it with criteria:

Between [Forms]![inpfrmReport]![txtBeginDate]
And DateValue(DateAdd("d", 1,
[Forms]![inpfrmReport]![txtEndDate].[txtBeginDate]))

When you save a date/time field using Now(), instead of Date(), you are
actually storing a decimal number where the number after the decimal
represents the number of hours after midnight. Basically, what your
criteria does is filters only those records that fall between midnight on
txtBeginDate and midnight on txtEndDate, but will actually exclude any
records that have a critical date between 12/31/05 12:00 AM and 1/1/06

2. You might also want to try it as follows. Access may not be correctly
interpreting these two fields as being date/time values.

Between CDate([Forms]![inpfrmReport]![txtBeginDate] )
And DateValue(DateAdd("d", 1,
cDate([Forms]![inpfrmReport]![txtEndDate].[txtBeginDate])))

HTH
Dale