Query based Report using parameters

[mobo]

Hi, I have a query which calculates for every record in the Payments table a
LastIssueDate and a NextOrderDate. This is done using for each of the date
fields the DateAdd function based on a FirstIssueDate. I can produce a report
based on this query but wish to do so by filtering the NextOrderDate with a
user input for Start and End dates. I assume I need some sort of Report run
time parameter based on the query and as a start to solving this problem used
the Between function in the criteria of the NextOrderDate field of the query.
Although the user can input the required dates they have no effect on the
query output. I would appreciate any advice to resolve this issue. Thanks

 

[Allen Browne]

See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

 

[mobo]

Thanks very much. Your Method 2 works but I seem to have a #Name problem with
the 2 text boxes inserted into the Report Header which defy the usual reasons
but I can probably resolve. However, while the process works, the Between
part does so providing I know and use Start and End Dates that exist in the
report. Is there a way of changing the coding so that inputting 2 dates which
may or may not exist will return all valid dates in between. Thanks

 

[Allen Browne]

For Method 1, switch the query to design view.

Change the WHERE clause like this:
WHERE (([StartDate] Is Null) OR ([MyField] >= [StartDate))
AND (([EndDate] Is Null) OR ([MyField] < [EndDate] + 1))

For Method 2, the code already handles nulls. But make sure the focus is not
still in the last date field, and the value may not have been updated. (If
you clicked the button, then the value of the text box is updated, so that's
not the issue.

Remove the apostrophy from the start of the Debug.Print line, and look in
the Immediate Window (Ctrl+G) to see if the expression looks right.

 

[mobo]

Very helpful Allen, problem now resolved and very much appreciate your
assistance.
--
Rob

For Method 1, switch the query to design view.

Change the WHERE clause like this:
WHERE (([StartDate] Is Null) OR ([MyField] >= [StartDate))
AND (([EndDate] Is Null) OR ([MyField] < [EndDate] + 1))

For Method 2, the code already handles nulls. But make sure the focus is not
still in the last date field, and the value may not have been updated. (If
you clicked the button, then the value of the text box is updated, so that's
not the issue.

Remove the apostrophy from the start of the Debug.Print line, and look in
the Immediate Window (Ctrl+G) to see if the expression looks right.