Specifying conditional Criteria in a Query or Report

[Stephen @ ZennHAUS]

Hi Guys and Gals,

I have a number of reports each fed by a different query. In each query I
have criteria supplied by a form
(for example, Between [forms]![frmDateRange]![txtDateFrom] And
[forms]![frmDateRange]![txtDateTo])
which works just fine.

However, now I want to add the option to specify additional criteria using
the form such as an equipment
number, operator etc. Naturally, each criteria is not being specified each
time the report is being run and
so I need to include certain criteria only when it is chosen in the form. So
I used an IIf statement to try and
make the criteria only apply when the combo box on the form is filled out
(for example, IIf([forms]![frmDateRange]![cbxEquipment] Not IsNull,
[forms]![frmDateRange]![cbxEquipment]) )

When the query is run from the form, if the combobox has something in it, it
works fine, but if there is nothing in the combobox, the report displays
nothing. If I remove the IIF statement from the criteria, the report
displays all of the records regardless of which equipment it is (as you
would expect).

What am I doing wrong with my IIf statement? Is there a better way to do
this especially considering some reports will have up to 5 criteria
specified or not.

Cheers

Stephen @ ZennHAUS

 

[Mike Painter]

Hi Guys and Gals,
make the criteria only apply when the combo box on the form is filled
out (for example, IIf([forms]![frmDateRange]![cbxEquipment] Not
IsNull, [forms]![frmDateRange]![cbxEquipment]) )

An IIF statement has teh following format
IIF(Something, true, false)
If somethig evaluates is true then the true part evaluates, if not then the
false side does.
You have no "false" side. Access should throw an error but probably returns
Null.

You may have to build the query in code. This is not hard but can be
frustrating and timeconsuming.

TxtSQLF = "first part of code that is always the same. Watch the use of
single and double quotes"
TxtSQLB = " the rest of the code"

If Me.[cbxEquipment] then
TxtSQL = TxtSQLF & "Where cbxEquipment = '" & me.cbxequioment & "' " &
TxtSQLB
Else
TxtSQL = TxtSQLF & TxtSQLB

End If

is a quick example. Then run
Msgbox TxtSql until it looks right, watching for bad quotes, etc.
Finally, when it is right you can run it from DoCmd.

 

[Beetle]

You need to buil a string variable based on what is selected
in the relevant form controls, then apply that string as a filter
or use it as criteria in a where clause.

You can find a working example of this at;

http://allenbrowne.com/ser-62.html

 

[Stephen @ ZennHAUS]

Thanks for your suggestions Mike.

I am familiar with building queries in code and find it quite easy to do.
What I don't know in this case is how do I pass the resulting dynaset to the
report then.

Also, for your own information, you are correct about the IIF statement
syntax being IIF(condition, truepart, falsepart). However, if you leave the
truepart blank or omit the falsepart completely, Access does nothing (or at
least it is supposed to do nothing).

In this case is seems to assume that because I have the IIf statement in the
criteria there should be something there rather than opting for doing
nothing. I have tried using a false part of Null, IsNull and "". But all of
these have the same result as omitting the falsepart completely. :-(

Cheers

Stephen @ ZennHAUS

Hi Guys and Gals,
make the criteria only apply when the combo box on the form is filled
out (for example, IIf([forms]![frmDateRange]![cbxEquipment] Not
IsNull, [forms]![frmDateRange]![cbxEquipment]) )

An IIF statement has teh following format
IIF(Something, true, false)
If somethig evaluates is true then the true part evaluates, if not then
the false side does.
You have no "false" side. Access should throw an error but probably
returns Null.

You may have to build the query in code. This is not hard but can be
frustrating and timeconsuming.

TxtSQLF = "first part of code that is always the same. Watch the use of
single and double quotes"
TxtSQLB = " the rest of the code"

If Me.[cbxEquipment] then
TxtSQL = TxtSQLF & "Where cbxEquipment = '" & me.cbxequioment & "' " &
TxtSQLB
Else
TxtSQL = TxtSQLF & TxtSQLB

End If

is a quick example. Then run
Msgbox TxtSql until it looks right, watching for bad quotes, etc.
Finally, when it is right you can run it from DoCmd.

 

[Bob Barrows]

Thanks for your suggestions Mike.

I am familiar with building queries in code and find it quite easy to
do. What I don't know in this case is how do I pass the resulting
dynaset to the report then.

You don't need to. All you need to do is build the WHERE clause and pass
it to the report using the WhereCondition argument of the OpenReport
method. Here's the online help for OpenReport:

OpenReport Method
See Also Applies To Example Specifics
The OpenReport method carries out the OpenReport action in Visual Basic.

expression.OpenReport(ReportName, View, FilterName, WhereCondition,
WindowMode, OpenArgs)

expression Required. An expression that returns a DoCmd object.

ReportName Required Variant. A string expression that's the valid name
of a report in the current database. If you execute Visual Basic code
containing the OpenReport method in a library database, Microsoft Access
looks for the report with this name, first in the library database, then
in the current database.

View Optional AcView. The view to apply to the specified report.

AcView can be one of these AcView constants.
acViewDesign
acViewNormal default Prints the report immediately.
acViewPivotChart Not supported.
acViewPivotTable Not supported.
acViewPreview

FilterName Optional Variant. A string expression that's the valid name
of a query in the current database.

WhereCondition Optional Variant. A string expression that's a valid SQL
WHERE clause without the word WHERE.

WindowMode Optional AcWindowMode.

AcWindowMode can be one of these AcWindowMode constants.
acDialog
acHidden
acIcon
acWindowNormal default

OpenArgs Optional Variant. Sets the OpenArgs property.

Remarks
For more information on how the action and its arguments work, see the
action topic.

The maximum length of the WhereCondition argument is 32,768 characters
(unlike the Where Condition action argument in the Macro window, whose
maximum length is 256 characters).

You can leave an optional argument blank in the middle of the syntax,
but you must include the argument's comma. If you leave one or more
trailing arguments blank, don't use a comma following the last argument
you specify.

Example
The following example prints Sales Report while using the existing query
Report Filter.

DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"

 

[Chegu Tom]

I would build my search string wiht vba code

I have a form with 5 textboxes cmbVender, txtProject, txtPO, txtSize and
cmbType
The query underlying the form uses venderid, projectid, ponum, OD, Type and
active fields

The user can enter values in any of the boxes and it will generate a report
selected by those criteria
lots of ANDs

I look to see if the control has data and if it does I add an 'AND' phrase
tho the criterial string

my simple minded code-----------------------

Private Sub cmdSearchPO_Click()
On Error GoTo Err_cmdSearchPO_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SearchForm"
stLinkCriteria = "active = true"

If Not IsNull(Me.cmbVender) Then
stLinkCriteria = stLinkCriteria & " and [VenderID] = '" & Me![cmbVender]
& "'"
End If

If Not IsNull(Me.txtproject) Then

stLinkCriteria = stLinkCriteria & " and projectid = '" &
Me.txtproject & "'"
End If

If Not IsNull(Me.txtPO) Then
stLinkCriteria = stLinkCriteria & " and PONum = '" & Me.txtPO & "'"
End If


If Not IsNull(Me.cmbType) Then
stLinkCriteria = stLinkCriteria & " and type = '" & Me.cmbType & "'"
End If

If Not IsNull(Me.txtSize) Then
stLinkCriteria = stLinkCriteria & " and (OD) like '" & Me.txtSize &
"*'"
End If


DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

---------- End of cod

 

[Stephen @ ZennHAUS]

Thanks Bob.

Works a treat. Don't know why I hadn't been able to work that out on my own
previously.

Cheers

Stephen @ ZennHAUS