By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

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

P: 5
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


Expand|Select|Wrap|Line Numbers
  1. Public Sub Search_Click()
  2.     Dim strWhere As String
  3.     Dim strWork As String
  4.     Dim varItem As Variant
  5.     Dim lngLen As Long                     
  6.     Const conJetDate = "\#dd\/mm\/yyyy\#"   
  7.     Dim strSQL As String
  8.  
  9.     strWhere = ""               
  10.  
  11.     '***********************************************************************
  12.     'Look at each search box, and build up the criteria string from the non-blank ones.
  13.     '***********************************************************************
  14.     If Not IsNull(Me.periodunderreview) Then
  15.         strWhere = strWhere & "([Month] = " & Format(Me.periodunderreview, conJetDate) & ") AND "
  16.     End If
  17.  
  18.     If Not IsNull(Me.cboProduct) Then
  19.         strWhere = strWhere & "([Product] = """ & Me.cboProduct & """) AND "
  20.     End If
  21.  
  22.     If Not IsNull(Me.cboFocusArea) Then
  23.         strWhere = strWhere & "([BusinessArea] = """ & Me.cboFocusArea & """) AND "
  24.     End If
  25.  
  26.     If Not IsNull(Me.cboActivity) Then
  27.         strWhere = strWhere & "([Sub-output] = """ & Me.cboActivity & """) AND "
  28.     End If
  29.  
  30.     If Not IsNull(Me.cboSite) Then
  31.         strWhere = strWhere & "([Location] = """ & Me.cboSite & """) AND "
  32.     End If
  33.  
  34.     If Not IsNull(Me.cboAssessor) Then
  35.         strWhere = strWhere & "([Assessor1] = """ & Me.cboAssessor & """) AND "
  36.     End If
  37.  
  38.     For Each varItem In Me.lstWorktype.ItemsSelected
  39.         strWork = strWork & "([Worktype] = """ & Me.lstWorktype.ItemData(varItem) & """) OR "
  40.         Next
  41.           'Test to see if we have subfilter...
  42.         If IsNull(strWork) Then
  43.         ' do nothing
  44.         Else
  45.         ' strip off last "OR" in the filter
  46.             If Right(strWork, 4) = " OR " Then
  47.                 strWork = Left(strWork, Len(strWork) - 4)
  48.             End If
  49.  
  50.             'Add some parentheses around the subfilter
  51.             strWhere = strWhere & "( " & strWork & " ) AND "
  52.         End If
  53.  
  54.     '***********************************************************************
  55.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  56.     '***********************************************************************
  57.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  58.     lngLen = Len(strWhere) - 5
  59.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  60.         MsgBox "Please select the relevant parameters.", vbInformation, "No parameters"
  61.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  62.         If lngLen > 0 Then
  63.             strWhere = Left$(strWhere, lngLen)
  64.         End If
  65.     End If
  66.  
  67.    'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  68.    Debug.Print strWhere
  69.  
  70.    'Finally, apply the string as the form's Filter.
  71.    Me.Filter = strWhere
  72.    Me.FilterOn = True
  73.  
  74.    DoCmd.SetWarnings False
  75.  
  76.    strSQL = "SELECT [Z-Results].* INTO tblResultsTemp FROM [Z-Results]" & _
  77.             "WHERE  " & strWhere & ";"
  78.  
  79.    DoCmd.RunSQL strSQL
  80.  
  81.    DoCmd.SetWarnings True
  82.  
  83. End Sub
Apr 28 '10 #1
Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.


Expand|Select|Wrap|Line Numbers
  1.         If strWork & ""="" Then 
  2.         ' do nothing 
  3.         Else 
  4.         ' strip off last "OR" in the filter 
  5.             If Right(strWork, 4) = " OR " Then 
  6.                 strWork = Left(strWork, Len(strWork) - 4) 
  7.             End If 
  8.  
  9.             'Add some parentheses around the subfilter 
  10.             strWhere = strWhere & "( " & strWork & " ) AND " 
  11.         End If 
Apr 28 '10 #2

NeoPa
Expert Mod 15k+
P: 31,768
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 = ""
    Expand|Select|Wrap|Line Numbers
    1. If Not Me.chkHistorical Then _
    2.     strWhere = strWhere & " AND ([TranDate]>=#" & Format(Date, 'm/d/yyyy') & "#)"
    It is generally quite easy to strip either with a simple :
    Expand|Select|Wrap|Line Numbers
    1. 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!
Apr 28 '10 #3

P: 5
@TheSmileyOne
TheSmileyOne,

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

Thanks once again.
Apr 29 '10 #4

P: 5
@NeoPa
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).
Apr 29 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Good to hear you got it working. Come back anytime :)
Apr 29 '10 #6

NeoPa
Expert Mod 15k+
P: 31,768
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.
Apr 29 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.