Posting Date Criteria on Report from Query

[jsparker]

I have a report that pulls info from a query that has a set criteria of a date range using [Between].

How do I post that date range on the report? The date range changes from report to report so I'm hoping there is a way to show the range criteria on the report from the query.

Any suggestions?

 

[boblarson]

Use a parameters form to capture the input and then you can refer to it from the queries to get the parameters but also the report can reference it (as long as it stays open).

 

[tarcona]

This is how I do mine. I input the dates from a form. But on your report make 2 text boxes.

Put this in the first one's control's source:

=[Forms]![frmName]![txtDateFirst]

and then put on the second's contol source:

=[Forms]![frmName]![txtDateLast]

That works for me. Fill in your information instead of mine. Only thing to keep is the [Forms] but fill in the rest. Also, you might want to put a text box at the beginning say "Between" and a text box in the middle saying "and".

 

[boblarson]

This is how I do mine. I input the dates from a form. But on your report make 2 text boxes.

Put this in the first one's control's source:

=[Forms]![frmName]![txtDateFirst]

and then put on the second's contol source:

=[Forms]![frmName]![txtDateLast]

That works for me. Fill in your information instead of mine. Only thing to keep is the [Forms] but fill in the rest. Also, you might want to put a text box at the beginning say "Between" and a text box in the middle saying "and".

tarcona -

Want a learning experience? Just use this in ONE text box:

="Between " & [Forms]![frmName]![txtDateFirst] & " And " & [Forms]![frmName]![txtDateLast]


No three text boxes needed and it grows and shrinks with your dates.

 

[jsparker]

="Between " & [Forms]![frmName]![txtDateFirst] & " And " & [Forms]![frmName]![txtDateLast]

Ok. So I'm not sure what I input.

What is [Forms]? Is that the form that the query pulls from? Though it pulls from a table. [frmtable] same question.

It's a query pulling from a table to a report. So not sure where I add my information.

 

[boblarson]

The Forms part you leave as is as it tells Access you are looking in the forms collection.

The form name that the text boxes are on. You will build a form with text boxes named txtDateFirst and txtDateLast and then save the form. Replace the frmName with the actual name of the form that you create and save.

That is for the display on the report.

But for the query, you would then, in the criteria, put

Between [Forms]![frmName]![txtDateFirst] And [Forms]![frmName]![txtDateLast]

in the criteria line of the query where you now have something like [ENTER DATE]

 

[jsparker]

You guys are awesome. I played with it and finally got it to work. I was being stupid and didn't realize that you had to enter the dates in the form first then the query pulls the records from those dates and then puts the dates in the text box on the final report. Excellent. Thanks! Kudos to all.

Bob,
FYI I grew up in McMinnville so I'm very familiar with your area. Can't wait to move back up there.

 

[boblarson]