Problem with code for criteria form including multi-select list box

[ladycyradis]

I have a bit of a problem with some code in an Access 2003 database that I’m hoping someone can help me with. (If my code seems a little disjointed, it's because it is mostly copied from other answers posted here, and adjusted to suit my needs as required, but I think I've either left something out or used something I shouldn't have.)

Background
I have a pop-up form that has a number of combo boxes and a multi-select list box. Users can select values from any combination of these fields, click on the ‘Set parameters’ button, and the tblResultsTemp table will be created from teh filtered data extracted from the Z-Results table. The user can then minimise the form and go to the main form from which they can open the reports based on the data in tblResultsTemp.

The Problem
The code (see below) works just fine – except in the following circumstances:

• no values have been selected for any field
• no values have been selected in the list box

In the first case, (when the user should get the message to select some parameters) the immediate window shows

( )

and I get the error message: Run-Time Error ‘3075’: Syntax error (missing operator) in query expression ‘( )’.

In the second case, a sample of the immediate window shows

([Product] = "ERA") AND ([BusinessArea] = "HRR") AND ([Sub-output] = "2.2.1 Process lodgments") AND ( )

and I get the error message: Run-Time Error ‘3075’: Syntax error (missing operator) in query expression ‘([Product] = "ERA") AND ([BusinessArea] = "HRR") AND ([Sub-output] = "2.2.1 Process lodgments") AND ( )’.

I’m pretty sure the problem lies in the part of the code where I am chopping off the trailing 'AND' (lines 58-65), or in the list box section (lines 39-52), as the problem seems to relate to the parentheses around strWork, but I can't figure out how to fix it. I'm sure it's something very simple, but I haven't found anything that can tell me what to do.

Thanks in advance for any assistance
Rochelle

Public Sub Search_Click()
    Dim strWhere As String
    Dim strWork As String
    Dim varItem As Variant
    Dim lngLen As Long                     
    Const conJetDate = "\#dd\/mm\/yyyy\#"   
    Dim strSQL As String
    
    strWhere = ""               

    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    If Not IsNull(Me.periodunderreview) Then
        strWhere = strWhere & "([Month] = " & Format(Me.periodunderreview, conJetDate) & ") AND "
    End If
    
    If Not IsNull(Me.cboProduct) Then
        strWhere = strWhere & "([Product] = """ & Me.cboProduct & """) AND "
    End If
    
    If Not IsNull(Me.cboFocusArea) Then
        strWhere = strWhere & "([BusinessArea] = """ & Me.cboFocusArea & """) AND "
    End If

    If Not IsNull(Me.cboActivity) Then
        strWhere = strWhere & "([Sub-output] = """ & Me.cboActivity & """) AND "
    End If

    If Not IsNull(Me.cboSite) Then
        strWhere = strWhere & "([Location] = """ & Me.cboSite & """) AND "
    End If

    If Not IsNull(Me.cboAssessor) Then
        strWhere = strWhere & "([Assessor1] = """ & Me.cboAssessor & """) AND "
    End If

    For Each varItem In Me.lstWorktype.ItemsSelected
        strWork = strWork & "([Worktype] = """ & Me.lstWorktype.ItemData(varItem) & """) OR "
        Next
          'Test to see if we have subfilter...
        If IsNull(strWork) Then
        ' do nothing
        Else
        ' strip off last "OR" in the filter
            If Right(strWork, 4) = " OR " Then
                strWork = Left(strWork, Len(strWork) - 4)
            End If

            'Add some parentheses around the subfilter
            strWhere = strWhere & "( " & strWork & " ) AND "
        End If
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "Please select the relevant parameters.", vbInformation, "No parameters"
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        If lngLen > 0 Then
            strWhere = Left$(strWhere, lngLen)
        End If
    End If
           
   'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
   Debug.Print strWhere

   'Finally, apply the string as the form's Filter.
   Me.Filter = strWhere
   Me.FilterOn = True
   
   DoCmd.SetWarnings False

   strSQL = "SELECT [Z-Results].* INTO tblResultsTemp FROM [Z-Results]" & _
            "WHERE  " & strWhere & ";"
   
   DoCmd.RunSQL strSQL
   
   DoCmd.SetWarnings True
   
End Sub

[TheSmileyCoder]

Your problem is in the IsNull(strWork) . A variable declared in code is not null, as opposed to say a textbox/combobox which can be null.

A way to test for it, is to write like shown below. The reason why I do the & "" before the comparison is that it makes it more gracefully handle the cases where strWork has not been assigned a value yet.

        If strWork & ""="" Then 
        ' do nothing 
        Else 
        ' strip off last "OR" in the filter 
            If Right(strWork, 4) = " OR " Then 
                strWork = Left(strWork, Len(strWork) - 4) 
            End If 
  
            'Add some parentheses around the subfilter 
            strWhere = strWhere & "( " & strWork & " ) AND " 
        End If

[NeoPa]

Rochelle,

Let me just put together a few ideas/concepts that may help. Some are simple preferences. Others necessary to ensure your c ode works as required :

1. Date literals in SQL must be in m/d/yyyy format regardless of what your regional settings are (See Literal DateTimes and Their Delimiters (#)).
2. When working with optional phrases within a WHERE clause (where filters are only added when they're selected by the operator) it is advisable to surround each separate optional element in parentheses. It seems you've already followed this course, which is good.
3. When building up a filter string in this scenario, I always add the ORs & ANDs at the front rather than at the end.
   strWhere = ""
   

   If Not Me.chkHistorical Then _
       strWhere = strWhere & " AND ([TranDate]>=#" & Format(Date, 'm/d/yyyy') & "#)"

   It is generally quite easy to strip either with a simple :
   

   strWhere = Trim(Mid(strWhere, 5))

   This handles both OR & AND and is pretty easy code to write as well as to understand.
4. If each of the conditional elements are only added when there is something to add, then you should never end up with empty parentheses.

I hope this proves helpful & Welcome to Bytes!

[ladycyradis]

Your problem is in the IsNull(strWork) . A variable declared in code is not null, as opposed to say a textbox/combobox which can be null.

A way to test for it, is to write like shown below. The reason why I do the & "" before the comparison is that it makes it more gracefully handle the cases where strWork has not been assigned a value yet.

        If strWork & ""="" Then 
        ' do nothing 
        Else 
        ' strip off last "OR" in the filter 
            If Right(strWork, 4) = " OR " Then 
                strWork = Left(strWork, Len(strWork) - 4) 
            End If 
  
            'Add some parentheses around the subfilter 
            strWhere = strWhere & "( " & strWork & " ) AND " 
        End If

TheSmileyOne,

Thanks so much for your response. That part of the code now works perfectly.

Thanks once again.

[ladycyradis]

Rochelle,

Let me just put together a few ideas/concepts that may help. Some are simple preferences. Others necessary to ensure your c ode works as required :

1. Date literals in SQL must be in m/d/yyyy format regardless of what your regional settings are (See Literal DateTimes and Their Delimiters (#)).
2. When working with optional phrases within a WHERE clause (where filters are only added when they're selected by the operator) it is advisable to surround each separate optional element in parentheses. It seems you've already followed this course, which is good.
3. When building up a filter string in this scenario, I always add the ORs & ANDs at the front rather than at the end.
   strWhere = ""
   

   If Not Me.chkHistorical Then _
       strWhere = strWhere & " AND ([TranDate]>=#" & Format(Date, 'm/d/yyyy') & "#)"

   It is generally quite easy to strip either with a simple :
   

   strWhere = Trim(Mid(strWhere, 5))

   This handles both OR & AND and is pretty easy code to write as well as to understand.
4. If each of the conditional elements are only added when there is something to add, then you should never end up with empty parentheses.

I hope this proves helpful & Welcome to Bytes!

Thanks for your reply also, NeoPa. You helped me solve a problem (with the dates) I didn't know I had (until the previous one was fixed).

[TheSmileyCoder]

Good to hear you got it working. Come back anytime :)

[NeoPa]

It's a pleasure Rochelle :)

Living where I do I come across this problem more often than many of our other experts, whose regional date settings match the SQL standard ones anyway.