show specific criteria or all criteria

[gray78]

I have several reports where I can set a specific parameter(dropdown box in a form) for a specific field and run the report. This works fine but if I want to leave the field blank and run the report for all data in the field where the parameter sits, I cannot do this, any suggestions?


Thanks

 

[cghoga]

Try this...

Like iif(Forms!Formname!Objectname.value is null,"*",Forms!Formname!Objectname.value)

Hope this helps.

 

[dhookom]

I prefer to remove the criteria from the report's record source. I use the control values in the Where clause of the DoCmd.OpenReport method. For instance

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & " AND [SaleDate]>=#" & _
        Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " AND [SaleDate]<=#" & _
        Me.txtEndDate & "# "
End If
'other sections of code
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

I think this is much "cleaner".
There are some other references here faq703-2657, faq701-2328, and faq181-5497.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]