Multiple Criteria in single field

[Toullie1]

I have a database that contains postcodes. I want to be able to search for
multiple postcodes (the search criteria will change each time I run the Query)
What is the simplest way to do this????

 

[Smartin]

I have a database that contains postcodes. I want to be able to search for
multiple postcodes (the search criteria will change each time I run the Query)
. What is the simplest way to do this????

How are you obtaining the search criteria?

In general, this might work:

SELECT *
FROM Postcodes
WHERE MyPostCode IN (<reference to search criteria>);

 

[Toullie1]

I haven't decided how to obtain the search criteria, I was hoping to use a
form or similar.

 

[Guest]

For this situation, I use a Multi Select Extended List Box to select the
values to filter for. Then when I am ready to build the search criteria, I
use this function:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Pass the Name of the control to the function and it will retun a string with
the critera set. It does not include the word WHERE, because I use it for
different situations where it my be HAVING or as a report filter, etc.

As to using this in a query, that will depend on how you are using the
query. If it is to filter a report, I would recommend using the Where
argument of the OpenReport method rather than filtering the query.