Too Complex to be evaluated - HELP!

[awmmoore]

Hello Everyone,

I've got a problem with a query of mine. I've tried to be clever and give users the ability to select multiple dates to run a query on. The VBA code I've got for this is below:

ListBox1.AddItem "01/" & Combo3.Value & "/" & Combo29.Value
Dim strstring As String
strstring = ""
For i = 0 To Me.ListBox1.ListCount - 1
    If i = Me.ListBox1.ListCount - 1 Then
    strstring = strstring & "#" & Format(Me.ListBox1.Column(0, i), "DD/MM/YYYY") & "#"
    Else
    strstring = strstring & "#" & Format(Me.ListBox1.Column(0, i), "DD/MM/YYYY") & "# Or "
    End If
Next i
Text30.Value = strstring

I then in my query, have the date field look at the value of text30.value. The value of text30.value looks like #01/01/2011# Or #01/02/2011# or #01/03/2011#. When the query tries and runs however, it brings up the really annoying error of being typed incorrectly or being too complex. However, if I type into query design mode #01/01/2011# Or #01/02/2011# or #01/03/2011# in the date field "WHERE" criteria, it works... I can't understand why. Any help will be greatly appreciated.

[pbaldy]

The problem is that the query design grid is doing some work for you that you don't realize. Valid SQL is:

WHERE FieldName = #01/01/2011# Or FieldName = #01/02/2011#

Note the repetition of the field name. If you type

#01/01/2011# Or #01/02/2011#

into the criteria in the design grid, it converts it for you (change to SQL view after tabbing out of that field and you'll see what I mean). When you run the query dynamically looking at that criteria, it won't (can't?). That leaves you with

WHERE FieldName = #01/01/2011# Or #01/02/2011#

which isn't valid SQL. I typically use a technique like this to filter forms or reports with a multiselect listbox:

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

[awmmoore]

Hello,

The query now works, but it brings back all data, and doesn't seem to specify the filtering based on the dates. Below is the query the listbox is bound to:

SELECT Tbl_SMT.FullName AS [SMT Tech Name], TblQuality.Date, TblQuality.TelScore AS [Telephone Score], TblQuality.SAPNotNoCompleted AS [Notifications Raised], TblQuality.SAPNotTelephone AS [(T) Telephone], TblQuality.SAPNotEmail AS [(E) Email], TblQuality.SAPNotFace AS [(F) Face to Face], TblQuality.SAPNotProcess AS [(P) Process], TblQuality.SAPNotWorkType AS [Incorrect Work Type], TblQuality.SAPNotLongText AS [Long Text Missing], TblQuality.SAPNotReportedBy AS [Reported By Missing], TblQuality.SAPNotPrioritySet AS [Incorrect Priority], TblQuality.SAPNotConstraintDates AS [Constraint Dates], TblQuality.SAPNotREVariance AS [RE Variance], TblQuality.SAPConNumberComp AS [Confirmations Made], TblQuality.SAPConNoEmployeeNo AS [Employee ID Missing], TblQuality.SAPConNoConfText AS [No Confirmation Text], TblQuality.SAPConZeroMins AS [Zero Minutes], TblQuality.SMQRej1 AS Rejects FROM (TblQuality LEFT JOIN Tbl_SMT ON TblQuality.Username=Tbl_SMT.Username) LEFT JOIN Tbl_TLs ON Tbl_SMT.TeamLeaderID=Tbl_TLs.Username WHERE (((Tbl_SMT.FullName) Like Forms!frmShow!Combo1.value) And ((forms!frmShow!text30.value)) And ((Tbl_TLs.FullName) Like forms!frmShow!combo5.value));

The VBA which my code outputs into text30.value is:

ListBox1.AddItem "01/" & Combo3.Value & "/" & Combo29.Value
Dim strstring As String
strstring = ""
For i = 0 To Me.ListBox1.ListCount - 1
    If i = Me.ListBox1.ListCount - 1 Then
    strstring = strstring & "((tblQuality.Date)=#" & Format(Me.ListBox1.Column(0, i), "DD/MM/YYYY") & "#)"
    Else
    strstring = strstring & "((tblQuality.Date)=#" & Format(Me.ListBox1.Column(0, i), "DD/MM/YYYY") & "#) Or "
    End If
Next i
Text30.Value = strstring
List7.Requery

Can anyone shed any light on this?