RunTime Error: 3360

[yummy7]

Hi All,
I have stuck in great trouble.
When I click on command button to view Query which selects StoreNo from List.It works fine if some stores selected.But when I select round about 300 stores from list,it gives runtime error:3360 'query is too complex '.Following is code.
Private Sub Command18_Click()
CurrentDb.QueryDefs("Report").SQL = "SELECT Snumber,System_Type from Store_t where " & GetCriteria()
DoCmd.OpenQuery "Report", acViewPreview
End Sub

<-Function to get Store Nos from List->
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim varItm As Variant

For Each varItm In Forms![rptStores]!List10.ItemsSelected
stDocCriteria = stDocCriteria & "[Snumber] = " & Forms![rptStores]!List10.Column(0, varItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If

GetCriteria = stDocCriteria
End Function
Kindly help me.

 

[PHV]

You may try this function instead:
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim varItm As Variant
With Forms![rptStores]!List10
For Each varItm In .ItemsSelected
stDocCriteria = stDocCriteria & "," & .Column(0, varItm)
Next
End With
If stDocCriteria <> "" Then
stDocCriteria = "Snumber In (" & Mid(stDocCriteria, 2) & ")"
Else
stDocCriteria = "1=1"
End If
GetCriteria = stDocCriteria
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

It may be quicker and easier to add records to a temporary table, which could then be used in a query to select a set of records.

 

[yummy7]

Thanx alot PHV.It works. Also thanx Remou for your positive Response.

 

[yummy7]

Tell me its function PHV
stDocCriteria = "1=1" ?

You bring happiness to many faces,you are really great.

 

[PHV]

It's an expression that always evaluate to true.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886