Filtering: What Am I Doing Wrong?

[Guest]

Code

Private Sub CmdOK_Click(
On Error GoTo Err_CmdOK_Clic
Dim strWhere As Strin
Dim strLink As Strin
If Len(Me.CmbFilter & vbNullString) = 0 The
MsgBox "Please select one of the options.", vbInformation, "Selection Required
ElseIf Me.CmbFilter = "See All" The
DoCmd.OpenForm "Web Status Clients Form Test New Filter
DoCmd.Close acForm, "Web Status Clients Search Form w Filters
ElseIf Me.CmbFilter = "Filter By.." The
If Not IsNull(Me.CmbRegions) The
strWhere = strWhere & strLink & "Regions=""" & CmbRegions & """
strLink = " And
End I
If Not IsNull(Me.CmbActivity) The
strWhere = strWhere & strLink & "Activity Ranking=""" & CmbActivity & """
strLink = " And
End I
DoCmd.OpenForm "Web Status Clients Form Test New Filter", acNormal, , strWher
DoCmd.Close acForm, "Web Status Clients Search Form w Filters
End I
Exit_CmdOK_Click
Exit Su
Err_CmdOK_Click
MsgBox Err.Descriptio
Resume Exit_CmdOK_Clic
End Su

Problem: Syntax error (missing operator) in query expression 'Regions="Chicago" And Activity Ranking="1"'

It seemed to work fine when I had it just Regions but for a combination, I am unable to get it to work

Thank you!

 

[Newbie]

Have you tried debugging it and seeing what SQL statement your string is
creating?

If you place a break point just after the point where the string is created
and then in the immediate window type:
? strWhere this will show you what SQL you are passing

Looking at this quickly it looks like you are using strLink before you have
actually said what strLink is equal to .. .

Try somthing like: (untested)

strLink = " And "
strWhere = "Regions = '" & cmbRegions & "'" 'This is a single and
double quotes and then double, single, double quotes

blah blah

strWhere = strWhere & strLink & ActivityRanking = '" & cmbActivity & "'"


HTH

Code:

Private Sub CmdOK_Click()
On Error GoTo Err_CmdOK_Click
Dim strWhere As String
Dim strLink As String
If Len(Me.CmbFilter & vbNullString) = 0 Then
MsgBox "Please select one of the options.", vbInformation, "Selection Required"
ElseIf Me.CmbFilter = "See All" Then
DoCmd.OpenForm "Web Status Clients Form Test New Filter"
DoCmd.Close acForm, "Web Status Clients Search Form w Filters"
ElseIf Me.CmbFilter = "Filter By.." Then
If Not IsNull(Me.CmbRegions) Then
strWhere = strWhere & strLink & "Regions=""" & CmbRegions & """"
strLink = " And "
End If
If Not IsNull(Me.CmbActivity) Then
strWhere = strWhere & strLink & "Activity Ranking=""" & CmbActivity & """"
strLink = " And "
End If
DoCmd.OpenForm "Web Status Clients Form Test New Filter", acNormal, , strWhere
DoCmd.Close acForm, "Web Status Clients Search Form w Filters"
End If
Exit_CmdOK_Click:
Exit Sub
Err_CmdOK_Click:
MsgBox Err.Description
Resume Exit_CmdOK_Click
End Sub

Problem: Syntax error (missing operator) in query expression

'Regions="Chicago" And Activity Ranking="1"'.

 

[Marshall Barton]

strWhere = strWhere & strLink & "Activity Ranking=""" & CmbActivity & """" []

Problem: Syntax error (missing operator) in query expression 'Regions="Chicago" And Activity Ranking="1"'.

If you're going to use blanks or other funky characters in
names, then you must enclose the name in square brackets:

strWhere = strWhere & strLink & "[Activity Ranking]=""" &
CmbActivity & """"

 

[Guest]

Thank you both for responding..

Mr. Barton, thank you especially for clearing up my problem

Job well done! Now how do I put you in for a vote for volunteer of the month

 

[Marshall Barton]

Thank you both for responding...

Mr. Barton, thank you especially for clearing up my problem!

Job well done! Now how do I put you in for a vote for volunteer of the month?

I think you just did. ;-)

Glad to help,