Problem with Query using date range from control

[frogmann]

Hello, I am trying to reference a date range using a control from a form. I can reference the form ok using: [Forms]![frmMaryLtrReview]![txtSentDate] (this works when using a range: <[Forms]![frmMaryLtrReview]![txtSentDate]). But I want to specify a range: Between ([Forms]![frmMaryLtrReview]![txtSentDate]) And ([Forms]![frmMaryLtrReview]![txtSentDate]-61). Nothing I try works. I've tried: Between #" & [Forms]![frmMaryLtrReview]![txtSentDate] & "# And #" & [Forms]![frmMaryLtrReview]![txtSentDate] & "#-61, etc. Any ideas or suggestions would be very helpful. Thanks!

 

[lespaul]

try:

 Between #" & [Forms]![frmMaryLtrReview]![txtSentDate] & " 00:00:01"# And #" & [Forms]![frmMaryLtrReview]![txtSentDate] & " 11:59:59"#

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

I would try place the earlier date first:

Between ([Forms]![frmMaryLtrReview]![txtSentDate]-61) And ([Forms]![frmMaryLtrReview]![txtSentDate])

Duane
Hook'D on Access
MS Access MVP

 

[balin]

See the thread "filtering between 2 dates" 15th April

 

[frogmann]

Hello All,
Here is the answer:
Between ([Forms]![frmMaryLtrReview]![txtSentDate]) And DateAdd("d",-61,[Forms]![frmMaryLtrReview]![txtSentDate])

Thanks for the help!

 

[dhookom]

Frogman,
Did you try my suggestion? It should work the same as your answer.

Duane
Hook'D on Access
MS Access MVP

 

[frogmann]

dhookom, I tried but it didn't work (Access 2002), kept getting "too sophisticated or not well written" error messages (to paraphrase). Is in the confines of a query, and only the DateAdd() seemed to work. Thanks though!

 

[dhookom]

This suggests your dates are being interpreted as text values rather than dates. Using DateAdd() pushes the query to understand the control references as date values rather than text. It might be possible to further troubleshoot if we could see the full SQL view. If you have it working, don't bother.

Duane
Hook'D on Access
MS Access MVP