Using field in a form to set parameters for a query

  • Thread starter Thread starter DM - NPS
  • Start date Start date
D

DM - NPS

I have a form that lets the user pick a report, set the date (StartDate &
EndDate) and select a specific area (AreaKey). The idea being the query the
report is based on will use the dates and area selected by the user in the
query so the report is only for the parameters specified.

In the date field of the query I have:
=[forms].[frmReportMenu].[StartDate] And <=[forms].[frmReportMenu].[EndDate]
Click to expand...

In the Site_ID field I have: [forms].[frmReportMenu].[AreaKey]

This works but the user must enter a date (which is fine) and an area.
Where I run into problems is when the user wants to run the report for all
sites. If they leave the AreaKey field empty the report returns no values
when it should return the data for all sites.

Any Ideas?
 
The Nz function might work here. Try this criteria for your Site_ID

Like Nz([Forms]![frmReportMenu].[AreaKey], "*")
 
Seems like it should have worked but I still come up with a blank report
unless I fill in all the parameters
 
WHERE Site_ID = [forms].[frmReportMenu].[AreaKey] OR
[forms].[frmReportMenu].[AreaKey] Is Null

I generally don't put any dynamic criteria in record source queries. I
prefer building a where condition to use in the DoCmd.OpenReport method.

--
Duane Hookom
Microsoft Access MVP


DM - NPS said:
I have a form that lets the user pick a report, set the date (StartDate &
EndDate) and select a specific area (AreaKey). The idea being the query the
report is based on will use the dates and area selected by the user in the
query so the report is only for the parameters specified.

In the date field of the query I have:
=[forms].[frmReportMenu].[StartDate] And <=[forms].[frmReportMenu].[EndDate]
Click to expand...

In the Site_ID field I have: [forms].[frmReportMenu].[AreaKey]

This works but the user must enter a date (which is fine) and an area.
Where I run into problems is when the user wants to run the report for all
sites. If they leave the AreaKey field empty the report returns no values
when it should return the data for all sites.

Any Ideas?
Click to expand...
 
Back
Top