Syntax Error (missing operator) in Query Expresison

V

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
 
M

Marshall Barton

Vince Cole said:
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.
 
V

Vince Cole

Marshall Barton said:
Vince Cole said:
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
 
M

Marshall Barton

Vince said:
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
 
V

Vince Cole

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

-Vince

Marshall Barton said:
Vince said:
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
 
V

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

Marshall Barton said:
Vince said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top