Error in multiple select in a list box code

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.
 
K

Ken Snell [MVP]

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]="))
 
K

kris

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.
 
K

kris

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.
 
K

Ken Snell [MVP]

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?

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?
 

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