Query based Report using parameters

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
--
Rob

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mobo" wrote in message
...
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
--
Rob

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
--
Rob


"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mobo" wrote in message
...
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
--
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mobo" wrote in message
...
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
--
Rob


"Allen Browne" wrote:

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

"mobo" wrote in message
...
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
--
Rob

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


"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mobo" wrote in message
...
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
--
Rob


"Allen Browne" wrote:

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

"mobo" wrote in message
...
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
--
Rob