Syntax Error (missing operator) in Query Expresison

[Vince Cole]

I am receiving an error message: Syntax Error (missing operator) in Query
Expresison. Once again, I am able to print all reports from my database, but
I am trying to design a print button that will allow the user to print
selected report(s). Below is the code that I have for this print button:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = strWhere & Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

strRptFilter = BuildWhereCondition("Stock List")
DoCmd.OpenReport "Keystone Stock Report", , , strRptFilter

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

Once again I am an Access and VB novice (actually, novice is too nice a word
for my experience level), so any help is greatly needed and would be greatly
appreciated.

Thanks - Vince

 

[Marshall Barton]

I am receiving an error message: Syntax Error (missing operator) in Query
Expresison. Once again, I am able to print all reports from my database, but
I am trying to design a print button that will allow the user to print
selected report(s). Below is the code that I have for this print button:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = strWhere & Left(strWhere, Len(strWhere) - 2) & ")"
End Select

[snip]

You are including "IN (" with every selected item. Try
something more like:

Case Else 'Multiple Selection
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & ", '" & .ItemData(varItem) & "'"
Next varItem
End With
strWhere =" IN (" & Mid(strWhere, 3) & ")"
End Select

When debugging this kind of thing, it helps if you add:
Debug.Print strWhere
before using the strWhere string. Then you can use Ctrl+G
to display the immediate window and look at the result of
the code to see where it went off the rails.

 

[Vince Cole]

I am receiving an error message: Syntax Error (missing operator) in Query
Expresison. Once again, I am able to print all reports from my database, but
I am trying to design a print button that will allow the user to print
selected report(s). Below is the code that I have for this print button:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = strWhere & Left(strWhere, Len(strWhere) - 2) & ")"
End Select

[snip]

You are including "IN (" with every selected item. Try
something more like:

Case Else 'Multiple Selection
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & ", '" & .ItemData(varItem) & "'"
Next varItem
End With
strWhere =" IN (" & Mid(strWhere, 3) & ")"
End Select

When debugging this kind of thing, it helps if you add:
Debug.Print strWhere
before using the strWhere string. Then you can use Ctrl+G
to display the immediate window and look at the result of
the code to see where it went off the rails.

Marsh,

Thanks for your help. I made the change you suggested and replaced the
current Case Else statement with your suggestion. However, I am receiveing
the same error message. Once again, I am novice at this, so I'm not sure if
I am doing something incorrect or missing something obvious, but any further
insight you could privide would be be greatly appreciated. This is the even
procedure I have now for the print command for user selected report(s):


Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & ", '" & .ItemData(varItem) & "'"
Next varItem
End With
strWhere = " IN (" & Mid(strWhere, 3) & ")"
End Select

BuildWhereCondition = strWhere

End Function
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

strRptFilter = BuildWhereCondition("Stock List")
DoCmd.OpenReport "Keystone Stock Report", , , strRptFilter

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


Once again, any help is greatly neeeded and appreciated.
Thanks
Vince

 

[Marshall Barton]

Thanks for your help. I made the change you suggested and replaced the
current Case Else statement with your suggestion. However, I am receiveing
the same error message. Once again, I am novice at this, so I'm not sure if
I am doing something incorrect or missing something obvious, but any further
insight you could privide would be be greatly appreciated. This is the even
procedure I have now for the print command for user selected report(s):


Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & ", '" & .ItemData(varItem) & "'"
Next varItem
End With
strWhere = " IN (" & Mid(strWhere, 3) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Two things look wrong.

The Case 1 code uses ItemsSelected(0) and I don't think the
0 is right. Since IN works fine with a single item, I
suggest that you just remove that case and let the Case Else
deal with it.

The line:
DoCmd.OpenReport "Keystone Stock Report", , , strRptFilter
needs to include the name of the field to search. It should
look something like:

DoCmd.OpenReport "Keystone Stock Report", , , "[the field
name] " & strRptFilter

 

[Vince Cole]

Thanks for the feedback Marshall. I'll try the suggested changes and let you
knmow what happens.

-Vince

Thanks for your help. I made the change you suggested and replaced the
current Case Else statement with your suggestion. However, I am receiveing
the same error message. Once again, I am novice at this, so I'm not sure if
I am doing something incorrect or missing something obvious, but any further
insight you could privide would be be greatly appreciated. This is the even
procedure I have now for the print command for user selected report(s):


Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & ", '" & .ItemData(varItem) & "'"
Next varItem
End With
strWhere = " IN (" & Mid(strWhere, 3) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Two things look wrong.

The Case 1 code uses ItemsSelected(0) and I don't think the
0 is right. Since IN works fine with a single item, I
suggest that you just remove that case and let the Case Else
deal with it.

The line:
DoCmd.OpenReport "Keystone Stock Report", , , strRptFilter
needs to include the name of the field to search. It should
look something like:

DoCmd.OpenReport "Keystone Stock Report", , , "[the field
name] " & strRptFilter

 

[Vince Cole]

Hey Marshall,

Just wanted to let you know that I made the suggested changes and it works
fine. Thanks so much for your help. I could not have done it without your
help.

Thanks
Vince

Thanks for your help. I made the change you suggested and replaced the
current Case Else statement with your suggestion. However, I am receiveing
the same error message. Once again, I am novice at this, so I'm not sure if
I am doing something incorrect or missing something obvious, but any further
insight you could privide would be be greatly appreciated. This is the even
procedure I have now for the print command for user selected report(s):


Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & ", '" & .ItemData(varItem) & "'"
Next varItem
End With
strWhere = " IN (" & Mid(strWhere, 3) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Two things look wrong.

The Case 1 code uses ItemsSelected(0) and I don't think the
0 is right. Since IN works fine with a single item, I
suggest that you just remove that case and let the Case Else
deal with it.

The line:
DoCmd.OpenReport "Keystone Stock Report", , , strRptFilter
needs to include the name of the field to search. It should
look something like:

DoCmd.OpenReport "Keystone Stock Report", , , "[the field
name] " & strRptFilter