Using field in a form to set parameters for a query

[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]

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?

 

[ghetto_banjo]

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

Like Nz([Forms]![frmReportMenu].[AreaKey], "*")

 

[DM - NPS]

Seems like it should have worked but I still come up with a blank report
unless I fill in all the parameters

 

[Duane Hookom]

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

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]

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?