Parameter Query to print selected records

[Bryan P]

Using Access 2003. I have a recipe database and when I add to it periodically
Iwant to print the new records. The data for the report comes from a
Parameter query. A macro button on the form does the printing.
The criteria on the query is Between [What's the first record] And [What's
the last]. I'd like to be able to replace the latter with Last Record
(without input) but can't seem to get the syntax!
Any help appreciated.

 

[Allen Browne]

You may be able to fudge it with:
Between Nz([What's the first record],0) And
Nz([What's the last], 0)

A better solution would be to re-write the WHERE clause from:
WHERE ([Field1]) Between [What's the first record] And
[What's the last])
to:
WHERE (([What's the first record] Is Null)
OR ([Field1] >= [What's the first record]))
AND (([What's the last] Is Null)
OR [Field1] <= [What's the last]))
Assuming these are numeric (or date) parameters, be sure to declare the
paramaters (Parameters on Query menu in query design), so Access interprets
them correctly.

The best solution might be to build a Filter string dynamically, so it does
not contain any unnecessary parts - especially if you want to add criteria
on other fields as well. For an example of how to do this, download this
example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Or there's a simpler example using dates in Method 2 of this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html