Criteria for requery

[philippe_b]

Hi !

Got a small problem, I have a datebase for UK contacts with UK
postcodes.
eg: "GU35 9LW"

As the first two letters indicates an area, I want to be able to use a
listbox to search in my query these first two letters, one of them or
any combination.
I did a first query to extract these first two letters then I created a
form with a multiple selection listbox. As the result of the listbox
cannot be use as such, I have a textbox showing the result of my
selection using this code:

Private Sub List17_AfterUpdate()

Dim varItem
Me.Text22 = ""
For Each varItem In Me.List17.ItemsSelected
If Len(Me.Text22) = 0 Then
Me.Text22 = """" & Me.List17.ItemData(varItem) & """"
Else
Me.Text22 = Me.Text22 & "," & """" &
Me.List17.ItemData(varItem) & """"
End If
Next varItem

End Sub

At the end my problem is that I cannot have the In(..) function
working.I also tried using the OR operator but still the same problem.

If anyone can help.

 

[kingston via AccessMonster.com]

Is your question why can't the query be used as the list box's record source
or is it how to use the list box entry after a selection has been made?
First, I'm guessing that your query looks like:

SELECT Left([PostCode],2) AS UKArea From Table GROUP BY Left([PostCode],2);

Your list box would use this as its row source. When a selection is made,
the value Me.List can be used to perform what you need. HTH

 

[philippe_b]

That nice to have someone that can help.

Indeed my listbox is based on a query and it is set on multiple
selection extended, then I have a textbox getting the result of the
selection made in the listbox. In my textbox it looks like:
"GU","SE", ... and can have as many arguments as I've selected, could
be one or a hundred.

This what I wanna set as a criteria for my query so I get onlyu the
poscode that starts by either of those I've selected. I tried the In()
function and the OR operator but I can't make it happened. Perhaps I
did a mistake somewhere.
Or is it just not possible ?

I welcome suggestions from any experts! And I think that's a tough one.

 

[kingston via AccessMonster.com]

I think I understand now. Instead of making this criteria string:
"GU","SE",...
make this:
Like "GU*" OR Like "SE*" OR Like...
In other words, change the comma into the word (OR) Like and add an asterisk
after the code. If you are creating an SQL string, you'll need it to look
like this:

"SELECT ... FROM ... WHERE (... Like 'GU%' OR Like 'SE%' OR...);"

Be careful of the single vs. double quotations, and in ADO, the wildcard is %
not *.

 

[philippe_b]

I've just tried that solution as well but I can't make it work !

Looks like using a multiple selection listbox as a search criteria is
harder than it appears !

 

[Gary Walter]

I've just tried that solution as well but I can't make it work !

Looks like using a multiple selection listbox as a search criteria is
harder than it appears !

It sounds like you have a table (say "tblContacts")
with a field (say "PostCode") and first 2 chars of
[PostCode] are meaningful for a criteria/search query.

Helen Feddema wrote an excellent article for
WoodysAccessWatch on Paired MultiSelect Listboxes.
Windows and Access use them alot and they are "kind" to the user.
The listbox on the left presents "Available" items (like first 2 chars of PostCode).
The listbox on the right shows "Selected" items.
The user selects one or more items in a listbox, then clicks on "arrow"
command buttons between the lists to move from Available to Selected
(or vica versa)
Search for Vol 2, No.22 on the following site:
http://www.woodyswatch.com/access/archives.asp

or "Access Archon Column #72"

Behind the scenes you have two tables (tblAvailableItems and tblSelectedItems,
each with a single field "PCode").


The two listboxes (lstAvailableItems and lstSelectedItems) are bound to these two tables.

When you load your "Select" form that the two listboxes are on,
you empty the the two tables and then assign "available field" to tblAvailableItems.

Using what assumed above, here be *untested* sample code:

'********* start form load code ********************
Private Sub Form_Load()
On Error GoTo ErrorHandler
Dim dbs as DAO.Database

Set dbs = CurrentDb()

'Clear tables of available and selected items
dbs.Execute "DELETE * FROM tblSelectedItems", dbFailOnError
dbs.Execute "DELETE * FROM tblAvailableItems", dbFailOnError

'Fill table of available items from your table
dbs.execute "INSERT INTO tblAvailableItems " _
& "SELECT DISTINCT Left([PostCode],2) FROM tblContacts " _
& "WHERE Len([PostCode]) > 1;", dbFailOnError

dbs.Close

Me![lstAvailableItems].Requery
Me![lstSelectedItems].Requery

ErrorHandlerExit:
Set dbs = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
'********* end load form code ***************************

'The "arrows" (cmdAdd and cmdRemove) delete user-selected value(s)
from one table and put them in the other.

'******* start of Add/Remove code ***************
Private Sub cmdAdd_Click()
On Error GoTo ErrorHandler
Dim dbs as DAO.Database
Dim varItem As Variant
Dim rstSelected as DAO.Recordset
Dim rstAvailable as DAO.Recordset
Dim lstAvailable As Access.ListBox
Dim lstSelected As Access.ListBox

Set lstSelected = Me![lstSelectedItems]
Set lstAvailable = Me![lstAvailableItems]
'Check that at least one item has been selected
If lstAvailable.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
lstAvailable.SetFocus
Exit Sub
End If

Set dbs = CurrentDb
Set rstSelected = dbs.OpenRecordset("tblSelectedItems", dbOpenDynaset)
Set rstAvailable = dbs.OpenRecordset("tblAvailableItems", dbOpenDynaset)

'Iterate through collection of items selected in the listbox
For Each varItem In lstAvailable.ItemsSelected
strItem = lstAvailable.ItemData(varItem)
'Debug.Print "Selected item: " & strItem
'Append selected item to Selected Items list
With rstSelected
strSearch = "[PCode] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![PCode] = strItem
.Update
End If
End With
'Delete selected item from Available Items list
With rstAvailable
strSearch = "[PCode] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = False Then
.Delete
End If
End With
Next varItem

rstSelected.Close
rstAvailable.Close
lstAvailable.Requery
lstSelected.Requery
ErrorHandlerExit:
Set rstSelected = Nothing
Set rstAvailable = Nothing
Set dbs = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Private Sub cmdRemove_Click()
On Error GoTo ErrorHandler
Dim dbs as DAO.Database
Dim varItem As Variant
Dim rstSelected as DAO.Recordset
Dim rstAvailable as DAO.Recordset
Dim lstAvailable As Access.ListBox
Dim lstSelected As Access.ListBox

Set lstSelected = Me![lstSelectedItems]
Set lstAvailable = Me![lstAvailableItems]
'Check that at least one item has been selected
If lstSelected.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
lstSelected.SetFocus
Exit Sub
End If
Set dbs = CurrentDb
Set rstSelected = dbs.OpenRecordset("tblSelectedItems", dbOpenDynaset)
Set rstAvailable = dbs.OpenRecordset("tblAvailableItems", dbOpenDynaset)
'Iterate through collection of items selected in the listbox
For Each varItem In lstSelected.ItemsSelected
strItem = lstSelected.ItemData(varItem)
Debug.Print "Selected item: " & strItem
'Append selected item to Available Items list
With rstAvailable
strSearch = "[PCode] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![PCode] = strItem
.Update
End If
End With
'Delete selected item from Selected Items list
With rstSelected
strSearch = "[PCode] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = False Then
.Delete
End If
End With
Next varItem

rstSelected.Close
rstAvailable.Close
lstAvailable.Requery
lstSelected.Requery

ErrorHandlerExit:
Set rstSelected = Nothing
Set rstAvailable = Nothing
Set dbs = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
'********** end of add/remove code **************

So.....
in your "filter" query, add "tblSelected,"
join tblContacts.PostCode to tblSelected.PCode,
then go into SQL View and change join from

ON
tblContacts.PostCode = tblSelected.PCode

to

ON
Left(tblContacts.PostCode,2) = tblSelected.PCode

 

[kingston via AccessMonster.com]

Can you provide more details? I am making a number of assumptions about what
you are doing but the process logic is sound. If you can create a string for
the criteria (Like "GU*" OR Like ...), you can create a string for the select
query (IN is not necessary). So the only code you need is something similar
to what you already had to build your original criteria string, something to
complete the SQL string, and something to return the query results.