Query Criteria Help Please

[brtny82]

Hello,
I have a Query called [Trending Report Query] that is pulling from the table [Monthly Trending data]. I have a form called [Monthly Trending] that I'm using to specify what trending date I'm pulling from (it is in format mm/dd/yyyy). In the field criteria of the Query, I have

[Forms]![Monthly Trending]![Trend]

but I need it to pull the date in the form and the 2 months prior to that. For example, if I put 02/01/2015 in the form as my trending date. I need the Query to report out on all that have the dates 12/01/2014, 01/01/2015, and 02/01/2015. What does my criteria need to be? I can't figure it out and google is no help.

Thank you in advance

 

[pbaldy]

Along the lines of

Between DateAdd(...) And [Forms]![Monthly Trending]![Trend]

filling in the arguments for the function.

 

[brtny82]

Thanks that worked! Unfortunately I didn't think that through.
Is there a way to return the date entered on the form and if there is a record, then also return the previous two months? But if there isn't a record for the date on the form, then do not return records on the previous two months.
For example, if I put 02/01/2015 in the form as my trending date. I need the Query to report out on all that have the dates 12/01/2014, 01/01/2015, and 02/01/2015. But ONLY if there is a record for 02/01/2015. Does that make sense?

 

[pbaldy]

The only way that comes immediately to mind is testing for that date before using the query. Like if it's for a report, test before opening the report.

 

[brtny82]

Okay, I didn't know if there was a module I could do or an Iif expression. Thanks

 

[pbaldy]

There may be other ways, that's just the first one that came to mind. You can use a DCount() with the form textbox, and only open the report if the count is greater than 0.