Query Overloading when too many selected

[Gee]

I have query that runs off the following code. When a user selects too many Models, I get the Query is Too Complex error. I know why, it is because the user selects too many models and the query creats loads of "OR" statements. I found that there is a limit of 249.
This is the VBA Code that runs the query and opens the report:

Private Sub Command55_Click()
    Dim varItm As Variant
    Dim ModelWhere As String
    Dim strQuery
    Dim LowPop As String
    Dim SDate As Integer
    Dim EDate As Integer
    Dim qdf As DAO.QueryDef
    
    If SysCmd(acSysCmdGetObjectState, acQuery, "SUMCascadeQry") = acObjStateOpen Then
    DoCmd.Close acQuery, "SUMCascadeQry"
    End If
    
    
    Set qdf = CurrentDb.QueryDefs("SUMCascadeQry")
    
    
    LowPop = InputBox("Please Enter Minimum Population", "Population")
    SDate = InputBox("Please Enter First Year", "Year")
    EDate = InputBox("Please Enter Last Year", "Year")
    
    strQuery = "SELECT * FROM dbo_BAMtbl WHERE ((dbo_BAMtbl.Pop)>=" & LowPop & " AND "
    
    For Each varItm In Me.ListModu.ItemsSelected
        If ModelWhere = "" Then
           ModelWhere = "[dbo_BAMtbl].[Model]=" & _
           Chr(34) & Me.ListModu.Column(0, varItm) & Chr(34)
        Else
           ModelWhere = ModelWhere & " OR [dbo_BAMtbl].[Model]=" & _
           Chr(34) & Me.ListModu.Column(0, varItm) & Chr(34)
        End If
    Next varItm
    
    strQuery = strQuery & "(" & ModelWhere & "));"
    
    qdf.SQL = strQuery
    
    DoCmd.OpenReport "CascadeSUMrpt", acViewPreview, , "Year([Date]) >=" & SDate & " And Year([Date]) <= " & EDate
    'DoCmd.OpenQuery ("SUMCascadeQry")
    
    'MsgBox (strQuery)
    
    
    Set qdf = Nothing
End Sub

This is the query and where the error happens:

SELECT *
FROM dbo_BAMtbl
WHERE ((dbo_BAMtbl.Pop)>=0 AND ([Model]="DV23" OR [Model]="DV03" OR [Model]="DV2512" OR [Model]="DV8001"));

There are over a million DV Model types possible. When too many are chose, that "OR" statement goes on 'til it over loads. I don't know the limit, but I believe there is a limit to the number of "OR" statements a query can use.


The .Pop is an input box in the form and the Model choices are from cascading list boxes also in the form, as you can see from the VBA above.
I had been thinking about a "Create Table" temporary table query, but I think that will still cause that multiple "OR" statement problem.
This project is getting way past my experience and I need help badly, so thank you in advance for any help I can get!

[pbaldy]

One option would be to build an In() clause instead, as done here:

http://www.baldyweb.com/multiselect.htm

[June7]

Paul, is there a limit to the length of constructed string?

Could use the temp table approach and instead of building a query to retrieve all records, write each record individually. So within the loop could be an SQL INSERT statement something like:

CurrentDb.Execute "DELETE FROM tempModels"
CurrentDb.Execute "INSERT INTO tempModels SELECT * FROM dbo_BAMtbl WHERE Model='" & Me.Pop & "'"

Alternative to the INSERT is to open a recordset of the temp table and add records to the recordset then when the last record is added, commit the records to the table. http://msdn.microsoft.com/en-us/libr...to_a_recordset

[pbaldy]

Yes, there are limits (look at specifications in help for specifics), though I'm not sure which one is being run into here. Options include:

Number of characters in a cell in the query design grid

1,024

Number of characters for a parameter in a parameter query

255

Number of AND operators in a WHERE or HAVING clause

99*

Not sure if the last applies to OR as well as AND. You can certainly use a temp/local table and join it in a query. I'd use a recordset rather than SQL; in my experience more efficient for lots of items:

http://www.baldyweb.com/MultiselectAppend.htm

[Gee]

I have over a million possible DV Models and they will, on occasion, select more than 1,000.
I tried the "IN" alternative and ended up with a similar message about exceeding selections.

Tell me more about Temp tables? I'm really confused about how I would implement that process.

[June7]

A temp table is a table that holds data only for the duration of a process and is then purged. I use several temp tables to handle some complicated data manipulation for report output. Report is bound to temp table. VBA code saves data to table before report is executed. When the process completes, records are deleted.

If your db is split, put temp tables in the front end.

[pbaldy]

It would seem physically difficult to select 1,000; user must spend a lot of time clicking. I was thinking a different direction on the temp table, though either would work. I was thinking of the table just holding the selected values, and then a query could join that table to the actual data table to limit the selections. If you go that way, the size of your data may dictate that the temp table be in the back end, not the front. If it's in the front end, the back end can't evaluate the join, and all of the data table has to be brought to the front end to be evaluated (voice of experience).

June's method would work fine (and I'd agree with that table being in the front end). The specifics of your situation may lead you to the better solution for you.

[June7]

Don't understand why the temp table in frontend holding only the ID could not join to the linked backend data table. Seems that should work just as well as saving all the data needed for the output in temp table. Either way, temp table must be in frontend so multiple users don't interefere with each other.

[pbaldy]

I didn't say you couldn't have that join if it was in the front end, I said there were potential performance problems associated with that setup.

[Gee]

I am running into those problems.
This is an excellent solution, but I am getting incorrect data
I get the correct Model, and year, which are both inputs, but the other fields are all wonky and don't match to the Model/year.

Private Sub Command55_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  On Error GoTo ErrorHandler
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tempModels", dbOpenDynaset, dbAppendOnly)
  If Me.ListModu.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Model"
    Exit Sub
  End If
  Set ctl = Me.ListModu
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
     rs!Model = ctl.ItemData(varItem)
    rs!Manufacturer = Me.Manufacturer
    rs!Count = Me.Count
    rs!Date = Me.Date
    rs!Pop = Me.Pop
rs.Update
  Next varItem
  
SDate = InputBox("Please Enter First Year", "Year")
EDate = InputBox("Please Enter Last Year", "Year")
DoCmd.OpenReport "CascadeSrptTEMP", acViewPreview, , "Year([Date]) >=" & SDate & " And Year([Date]) <= " & EDate
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

[pbaldy]

Should the values be coming from the listbox instead of the form? By the way, you probably want to empty the temp table at the start.

[Gee]

I've planned on emptying the temp table with some code at some point, I'm trying to tackle the other problems first.

I'm not sure what you mean by the values coming from the list box....Model comes from the list box and bunches of other fields come from a table (dbo_BAMtbl), into the temp table into the report, which are supposed to be from the same record as the model, but it's not working...I'm getting Manufacturer and the other fields from unrelated records. I trimmed it down to Manufacturer, Model, Count, Date and Pop but there are actually about 20 fields.
Any ideas as to what I'm doing wrong now?

[pbaldy]

Well, you're getting those values from the form, so they'll be the same for every record appended. If they come from the same place as the model, I'd expect them to be in the listbox row source, and you get the values from there.