criteria parameters

[Guest]

I created an unbound form to enter parameters and it works fine. My question
is I need to sometimes populate my report combining 2 of the parameters
instead of 4 parameters but it won't work. I have to enter all 4 parameters
to get the report. How can I leave a parameter blank and still get a report
by entering 2 of the 4 parameters or by leaving them blank? Or by combining
parameters. I hope someone undersands me. Thanks

 

[Duane Hookom]

I expect you have criteria in your report's record source query like:
Forms!frmA!txtStart
I generally recommend removing the criteria from the query and applying a
filter using the Where clause of the DoCmd.OpenReport method:

Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEnd & "# "
End If
'more check for filtering values
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

 

[John Vinson]

I created an unbound form to enter parameters and it works fine. My question
is I need to sometimes populate my report combining 2 of the parameters
instead of 4 parameters but it won't work. I have to enter all 4 parameters
to get the report. How can I leave a parameter blank and still get a report
by entering 2 of the 4 parameters or by leaving them blank? Or by combining
parameters. I hope someone undersands me. Thanks

Two ways:

Simple but somewhat limited: instead of a criterion

=[Forms]![YourForm]![YourControl]

use

=[Forms]![YourForm]![YourControl] OR [Forms]![YourForm]![YourControl]
IS NULL

You'll see how Access makes a total hash of the query design window
when you do this though!


More difficult but more flexible:

Write VBA code in the Click event of a command button on the Form to
poll through the controls on the form, building up a SQL string for
each non-null control. Use this SQL string either as the Recordsource
for the report, or just build up a Where clause and use it in the
WhereCondition argument of the OpenReport action.

John W. Vinson[MVP]

 

[Guest]

You can make a parameter appear to be optional with something like this:

IIf(IsNull(Forms!MyForm!txtParamter1),"Like *",Forms!MyForm!txtParamter1)

 

[Duane Hookom]

Oops... won't work with the "Like" inside the IIf().

 

[Guest]

Thank You.

I expect you have criteria in your report's record source query like:
Forms!frmA!txtStart
I generally recommend removing the criteria from the query and applying a
filter using the Where clause of the DoCmd.OpenReport method:

Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEnd & "# "
End If
'more check for filtering values
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

I created an unbound form to enter parameters and it works fine. My
question
is I need to sometimes populate my report combining 2 of the parameters
instead of 4 parameters but it won't work. I have to enter all 4
parameters
to get the report. How can I leave a parameter blank and still get a
report
by entering 2 of the 4 parameters or by leaving them blank? Or by
combining
parameters. I hope someone undersands me. Thanks

 

[Guest]

Thank You so much.

I created an unbound form to enter parameters and it works fine. My question
is I need to sometimes populate my report combining 2 of the parameters
instead of 4 parameters but it won't work. I have to enter all 4 parameters
to get the report. How can I leave a parameter blank and still get a report
by entering 2 of the 4 parameters or by leaving them blank? Or by combining
parameters. I hope someone undersands me. Thanks

Two ways:

Simple but somewhat limited: instead of a criterion

=[Forms]![YourForm]![YourControl]

use

=[Forms]![YourForm]![YourControl] OR [Forms]![YourForm]![YourControl]
IS NULL

You'll see how Access makes a total hash of the query design window
when you do this though!


More difficult but more flexible:

Write VBA code in the Click event of a command button on the Form to
poll through the controls on the form, building up a SQL string for
each non-null control. Use this SQL string either as the Recordsource
for the report, or just build up a Where clause and use it in the
WhereCondition argument of the OpenReport action.

John W. Vinson[MVP]