multiple criteria parameter queries

  • Thread starter Thread starter Joe Schneider
  • Start date Start date
J

Joe Schneider

I have created a report based on a parameter query. The
criteria for the query is set through an unbound form. The
criteria I am looking to use is:
Between [Beginning Date] And [Ending Date]
I also have two combo boxes on the unbound form for [Family
ID#] and [Fund].

The report works fine if all of the parameters on the
unbound form are filled in but if you leave any one of them
blank the report shows no records. I have tried Is Null in
the Or criteria row but have been unsucessful. I would like
to be able to use the unbound form with any choices in the
Date field, family ID# or Fund to return any or all
combinations.

I am not an advanced Access 2000 user but am trying to
learn. Any help would be appreciated.

Thanks
Joe Schneider
 
Hi,


BETWEEN Nz( [Beginning Date], #1-1-1900#)
AND Nz( [Ending Date], #1-1-3000# )


would use "default" values like, if either of the parameter is Null.


Hoping it may help,
Vanderghast, Access MVP
 
I have created a report based on a parameter query. The
criteria for the query is set through an unbound form. The
criteria I am looking to use is:
Between [Beginning Date] And [Ending Date]
I also have two combo boxes on the unbound form for [Family
ID#] and [Fund].

The report works fine if all of the parameters on the
unbound form are filled in but if you leave any one of them
blank the report shows no records.
Click to expand...

This can be done in the grid, but I find the SQL view to be less
confusing when you have multiple OR conditions. Open your query in SQL
view and edit the WHERE clause to something like (using your
fieldnames):

WHERE ([datefield] >= [Beginning Date] OR [Beginning Date] IS NULL)
AND ([datefield] <= [Ending Date] OR [Ending Date] IS NULL)
AND ([Family ID#] = [Enter Family ID:] OR [Enter Family ID] IS NULL)
AND ([Fund] = [Enter fund:] OR [Enter fund:] IS NULL)
 
Back
Top