Calculations on dates in query criteria

[rjmccafferty]

I use queries and reports in access that rely on dates entered by the user into unbound fields in a form as part of the selection criteria for the queries. The most common example is

Between [Forms]![f-reports-interactive]![TextStartDate] And [Forms]![f-reports-interactive]![TextEndDate]

This works very well for our purposes. But I would also like to use these date references with calculations in the criteria for the queries and don't seem to be able to figure out the syntax. (My many books on Access are useless on this one). What I would like to do is convert the above statement to one that selects all records from [TextEndDate] +1 day to [TextEndDate] + 30 days inclusive. I assume I need to use the DateAdd function somehow, but am not understanding how to use it properly as selection criteria in a query.

 

[rac2]

If TextEndDate is a date datatype -

BETWEEN DateAdd("d", 1, [TextEndDate])
AND DateAdd("d", 30, [TextEndDate])

If TextEndDate is a string datatype -

BETWEEN DateAdd("d", 1, DateValue([TextEndDate]))
AND DateAdd("d", 30, DateValue([TextEndDate]))

maybe.