syntax for multiple search criteria in same field (1 Viewer)

[mike6271]

Hi,

I have a search form which allows to search for various criteria such as postcode.

The code for building the filter is as follows:

varWhere = (varWhere + " AND ") & _
                   "([ContactID] IN (SELECT ContactID FROM tblBusinessAddress " & _
                     "WHERE tblBusinessAddress.postcode LIKE '" & Me.txtPostcode & "'))"

Can someone please suggest a way of enabling the user to search for multiple postcodes such as NE1 "OR" NE2 using the same field in the form without having to write OR ((tblBuisnessAddress.Postcode)="NE2") OR....... etc.

Thanks in advance

 

[pbaldy]

How about:

WHERE postcode IN("NE1", "NE2")

 

[Stephd8459]

Mike,

So I'm looking for something similar, maybe we can work this out together
I built a filter with the following code

Dim strWhere as String
 
 If Not IsNull(Me.Month.Value) Then
       strWhere = strWhere & "(Month([ContEndDate]) Like " & Me.Month.Value & ") AND "

And I'd like to be able to have the users enter more than 1 month when filtering.

the resource I used for this is here
http://www.fontstuff.com/access/acctut19.htm#dialog1

They have a select list example

[COLOR=#000080]Dim[/COLOR] varItem [COLOR=#000080]As[/COLOR] [COLOR=#000080]Variant[/COLOR]
[COLOR=#000080]For[/COLOR] [COLOR=#000080]Each[/COLOR] varItem [COLOR=#000080]In[/COLOR] Me.lstOffice.ItemsSelected
    strOffice = strOffice & ",'" & Me.lstOffice.ItemData(varItem) _
    & "'"
[COLOR=#000080]Next[/COLOR] varItem

I just haven't been able to make it work for me

Not sure if it will help any but if you figure it out post it here

 

[pbaldy]

Does this help?

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

 

[Stephd8459]

Do I have to use a List Box?

I currently have a text box that I want the user to key in the month numericly so March = 3

When I made the adjustments per your db I'm getting a runtime error 3075
syntax error(missing operator) in query expression '[Status]= 'Active' And (Month([ContEndDate]) Like 7 8)'

The entire code is below -

'Report filter options
Private Sub cmdApplyFilter_Click()
    Dim strStatus As String
    Dim strWhere As String
    Dim strFilter As String
    Dim lngLen As Long
    Dim varItem As Variant
    Dim ctl As Control
 
 
 If SysCmd(acSysCmdGetObjectState, acReport, "rptCustom") <> acObjStateOpen Then
     MsgBox "You must open the report first."
     Exit Sub
End If
 
    If Not IsNull(Me.Status.Value) Then
        strWhere = strWhere & "[Status]= '" & Me.Status.Value & "' And "
    End If
    If Not IsNull(Me.Month.Value) Then
    Set ctl = Me.Month
    For Each varItem In ctl.ItemSelected
       strWhere = strWhere & "(Month([ContEndDate]) Like " & ctl.ItemData(varItem) & "," & ") AND "
    Next varItem
    End If
    If Not IsNull(Me.Vendor.Value) Then
        strWhere = strWhere & "([Vendor] Like ""*" & Me.Vendor.Value & "*"") AND "
    End If
    If Not IsNull(Me.Contract.Value) Then
        strWhere = strWhere & "([Contract] Like ""*" & Me.Contract.Value & "*"") AND "
    End If
 
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
 
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'No: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
     With Reports![rptCustom]
        .Filter = strWhere
        .FilterOn = True
    End With
    End If
End Sub

 

[pbaldy]

No, you don't have to use a listbox; it's probably the most common way of handling multiple selections though. What is Me.Month (textbox, listbox)? By the way, that's a bad name for a field/control as Access can confuse it with the Month() function. If it's a textbox, it can't be looped. If it's a multiselect listbox, its value will always be Null. You have to test to see if any selections were made like I did.

 

[Stephd8459]

Thanks for the details.
So, the box was a textbox, I did try changing it to a listbox, but when I select more than 1 values I get all results instead of just the selected.

example if I select 6 I get all the lines for any item in month 6 ... but if I select 6 and 7 I get lines for items in month 1 thru 12.

Any thoughts as to why multiple values would result in invalidating the filter?

Thanks agian for the help,
Steph

 

[pbaldy]

Can you post the code, or the db?

 

[Stephd8459]

the code is in the above post...

If Not IsNull(Me.Month.Value) Then
Set ctl = Me.Month
For Each varItem In ctl.ItemSelected
strWhere = strWhere & "(Month([ContEndDate]) Like " & ctl.ItemData(varItem) & "," & ") AND "
Next varItem
End If

this is the piece I've been tweaking

 

[pbaldy]

Like I said earlier, a multiselect listbox will ALWAYS be Null; this test will never be met:

If Not IsNull(Me.Month.Value) Then

You need to test for selections the way I did.

 

[Stephd8459]

ok, so I completely didn't get the NULL thing the first time

But I change the code to

If IsNull(Me.Month.Value) Then
    For Each varItem In Me.Month.ItemsSelected
           strWhere = strWhere & "(Month([ContEndDate]) Like " & Me.Month.ItemData(varItem) & ") AND "
    Next varItem
    End If

And now the filter is working BUT it is using 'AND' not OR... how do I get it to OR?

The immediate results show
[Status]= 'Active' AND (Month([ContEndDate]) Like 3) AND (Month([ContEndDate]) Like 4)

What I want is
[Status]= 'Active' AND (Month([ContEndDate]) Like 3) OR (Month([ContEndDate]) Like 4)

I tried making some changes

 If IsNull(Me.Month.Value) Then
    'Set ctl = Me.Month
    For Each varItem In Me.Month.ItemsSelected
           strWhere = strWhere & "(Month([ContEndDate]) Like " & Me.Month.ItemData(varItem) & ") OR"
    Next varItem
    End If

The Immediate shows

[Status]= 'Active' And (Month([ContEndDate]) Like 3) OR (Month([ContEndDate]) Like 4

I can't figure out how to get the closing ) in there

 

[Stephd8459]

Paul,
thank you for all your help... I did some google searchs on using a Multi select textbox and came up with what I needed

So Mike to answer your questions....
I used 'IN'

My code for the syntax

If Not IsNull(Me.Month.Value) Then
           strWhere = strWhere & "(Month([ContEndDate]) In (" & Me.Month.Value & ")) AND "
    End If

 

[pbaldy]

My point was you can't test for the listbox being Null; it will always be Null, whether selections have been made or not. You use this to test:

If Me.lstEmployees.ItemsSelected.Count > 0 Then

 

[Stephd8459]

Paul,
yes, and thank you ... the first time I read the

If it's a multiselect listbox, its value will always be Null.

it made zero sense to me
When I went back again and figured out the If Not IsNull needed to be changed to your test that put me onto a differnet thinking and I found the code and syntax I needed to keep it a text box... the textbox is more in the format of the form I created.
Again... thank you very much for the assistance I couldn't have figured it out without the link and guidance you gave. I'm still pretty new to VB and the syntax stuff seems to be the last piece to fall into place for me

 

[DCrake]

This is the code you need for your delete button

Private Sub Command61_Click()
On Error GoTo Err_Exit_Click
Dim strIDs As String
Dim strSQL As String
Dim varItem As Variant

strSQL = "DELETE * FROM [W1_Budget Input] "

For Each varItem In Me.ListInputBudget.ItemsSelected
    strIDs = strIDs & "," & Me.ListInputBudget.ItemData(varItem)
Next varItem

strIDs = Mid(strIDs, 2)
    
strIDs = "WHERE BIID In (" & strIDs & ")"

strSQL = strSQL & strIDs


CurrentDb.Execute strSQL, dbFailOnError
Me.ListInputBudget.Requery

Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub