Error in multiple select in a list box code

  • Thread starter Thread starter kris
  • Start date Start date
K

kris

Hello,
I have an error when my multiple -select code is executed.The code
looks like

Function s()

Dim frm As Form, ctl As control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!first
Set ctl = frm!category
strSQL = "Select * from Table1 where [category]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [category]="
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 2)
CurrentDb.QueryDefs("Query3").SQL = strSQL
End Function

Error-"missing operator in query expression"
I dont know if the above code is right.Please let me know where I went wrong.
I'm new to VB coding and access.
 
You're not trimming off enough at the end of the statement:

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - Len(" OR [category]="))
 
Hey Ken,
Thank you for your response. Can you try to resolve this...
I modified the code to

Function s()

Dim frm As Form, ctl As control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!first
Set ctl = frm!category
strSQL = "SELECT Table3.* FROM Table3 WHERE ([Category]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Category]="
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - Len(" OR [Category]="))
strSQL = strSQL & ");"
'strSQL = Left$(strSQL, Len(strSQL) - 2)
CurrentDb.QueryDefs("Query3").SQL = strSQL
End Function

But I get Syntax error.Missing operator in query expression.

Ken Snell said:
You're not trimming off enough at the end of the statement:

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - Len(" OR [category]="))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


kris said:
Hello,
I have an error when my multiple -select code is executed.The code
looks like

Function s()

Dim frm As Form, ctl As control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!first
Set ctl = frm!category
strSQL = "Select * from Table1 where [category]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [category]="
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 2)
CurrentDb.QueryDefs("Query3").SQL = strSQL
End Function

Error-"missing operator in query expression"
I dont know if the above code is right.Please let me know where I went
wrong.
I'm new to VB coding and access.
Click to expand...
Click to expand...
 
Got it fixed.Replaced the statement in side for loop with
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR [Category]="

But what I wan to know is can i use "strSQL" string in a macro where the
action say runsql and give this string as parameter?

Ken Snell said:
You're not trimming off enough at the end of the statement:

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - Len(" OR [category]="))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


kris said:
Hello,
I have an error when my multiple -select code is executed.The code
looks like

Function s()

Dim frm As Form, ctl As control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!first
Set ctl = frm!category
strSQL = "Select * from Table1 where [category]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [category]="
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 2)
CurrentDb.QueryDefs("Query3").SQL = strSQL
End Function

Error-"missing operator in query expression"
I dont know if the above code is right.Please let me know where I went
wrong.
I'm new to VB coding and access.
Click to expand...
Click to expand...
 
kris said:
Got it fixed.Replaced the statement in side for loop with
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR [Category]="
Good.


But what I wan to know is can i use "strSQL" string in a macro where the
action say runsql and give this string as parameter?
Click to expand...

No. Macros do not understand VBA variables. If you're using ACCESS 2007, you
can use TempVars variables in macros; perhaps that would be usable for you
if you're using ACCESS 2007?
 
Back
Top