Code error to query with 6 Combo boxes

[Kolas]

Can anyone help me to find error in the following code.
I created a form with 6 comboboxes and write the ollowing code with the
guidence of www.fontstuff.com
But, when i press the button 'Apply Filter', again, a window prompts for
'Enter parameter value -PressureTier'
If I restrict combo boxes only for Material, and Diameter, the error is not
coming.
Please guide me.

Private Sub cmdApplyFilter_Click()
Dim strMaterial As String
Dim strDiameter As String
Dim strPressureTier As String
Dim strPipeType As String
Dim strStatus As String
Dim strPipeDigitised As String
Dim strFilter As String

'If a combo box is empty its value is Null so I can use an If Statement to
check whether or not the user made a choice and then construct the
appropriate SQL:

If SysCmd(acSysCmdGetObjectState, acReport, "rptPipe") <> acObjStateOpen
Then
MsgBox "You must open the report first."
Exit Sub
End If

If IsNull(Me.cboMaterial.Value) Then
strMaterial = "Like '*'"
Else
strMaterial = "='" & Me.cboMaterial.Value & "'"
End If



If IsNull(Me.cboDiameter.Value) Then
strDiameter = "Like '*'"
Else
strDiameter = "='" & Me.cboDiameter.Value & "'"
End If



If IsNull(Me.cboPressureTier.Value) Then
strPressureTier = "Like '*'"
Else
strPressureTier = "='" & Me.cboPressureTier.Value & "'"
End If



If IsNull(Me.cboPipeType.Value) Then
strPipeType = "Like '*'"
Else
strPipeType = "='" & Me.cboPipeType.Value & "'"
End If



If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If



If IsNull(Me.cboPipeDigitised.Value) Then
strPipeDigitised = "Like '*'"
Else
strPipeDigitised = "='" & Me.cboPipeDigitised.Value & "'"
End If


strFilter = "[Material]" & strMaterial & "AND [Diameter]" & strDiameter
& "AND [PressureTier]" & strPressureTier & "AND [PressureTier]" &
strPressureTier & "AND [PipeType]" & strPipeType & "AND [Status]" & strStatus
& "AND [PipeDigitised]" & strPipeDigitised

With Reports![rptpipe]
.Filter = strFilter
.FilterOn = True
End With
End Sub

 

[Douglas J. Steele]

You don't have any spaces between the various clauses.

You're going to end up with something like:

"[Material]='abc'AND [Diameter]='12'AND

Put spaces in front of " AND " in your statement.

 

[Rick Brandt]

Comments interspersed...

Can anyone help me to find error in the following code.
I created a form with 6 comboboxes and write the ollowing code with
the guidence of www.fontstuff.com
But, when i press the button 'Apply Filter', again, a window prompts
for 'Enter parameter value -PressureTier'
If I restrict combo boxes only for Material, and Diameter, the error
is not coming.
Please guide me. [snip]
'If a combo box is empty its value is Null so I can use an If
Statement to check whether or not the user made a choice and then
construct the appropriate SQL:

If SysCmd(acSysCmdGetObjectState, acReport, "rptPipe") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If

If IsNull(Me.cboMaterial.Value) Then
strMaterial = "Like '*'"
Else
strMaterial = "='" & Me.cboMaterial.Value & "'"
End If

Rather than building a string where non-filtered fields are still filtered
on using Like "*" just do not include those fields in the WHERE clause at
all. Even a Like "*" makes a query more complicated. What I do in these
cases would look like....

If IsNull(Me.cboMaterial.Value) Then
strMaterial = ""
Else
strMaterial = "Material = '" & Me.cboMaterial.Value & "' "
End If

Also notice that I left a trailing blank in that string. You need that so
that the syntax is correct when you combine all of the strings together.
You are just butting each string segment right up against the other so I
wonder how it works at all.

[snip] > strFilter = "[Material]" & strMaterial & "AND [Diameter]" &

strDiameter & "AND [PressureTier]" & strPressureTier & "AND
[PressureTier]" & strPressureTier & "AND [PipeType]" & strPipeType &
"AND [Status]" & strStatus & "AND [PipeDigitised]" & strPipeDigitised

With Reports![rptpipe]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Notice that you have filtered on PressureTier twice above.