Use "Like" Statement in Combo Look-Up Filter

[Nick X]

Hello Everybody,
I would like to include a like statement in my combo look up filter. This
is what is in the criterion now:
[forms]![frmSearch]![cboStreetNames]

This is the SQL I've used in the past, it works great but I would rather use
a combo box instead of a parameter prompt:
SELECT tblCuts.DateCalled, *
FROM tblCuts
WHERE (((tblCuts.Address1) Like "*" & [STREET NAME] & "*")) OR
(((tblCuts.StreetName) Like "*" & [STREET NAME] & "*")) OR (((tblCuts.From)
Like "*" & [STREET NAME] & "*")) OR (((tblCuts.To) Like "*" & [STREET NAME] &
"*"))
ORDER BY tblCuts.DateCalled DESC;

It would be great if I could select from the combo if there were something
available, but also have the like statement to filter on if there are no
matches.

Thanks,
NX

 

[Klatuu]

Your SQL, as is, can be used as the row source for your combo.

 

[Allen Browne]

Query parameters are not powerful enough to offer drop-down lists.

However, you can open a form that has a combo to select the value, and refer
to the combo on the form in your query.

It would end up something like this:

SELECT tblCuts.*
FROM tblCuts
WHERE ([Forms].[Form1].[Combo1] Is Null)
OR (tblCuts.Address1 Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.StreetName Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.[From] Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.[To] Like "*" & [Forms].[Form1].[Combo1] & "*")
ORDER BY tblCuts.DateCalled DESC;

Note that FROM and TO are reserved words in JET, so not a good choice for
field names. You can get away with it in this context by putting them in
square brackets and including the table name, but for a list of the names to
avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[Nick X]

Thanks Allen, that makes perfect sense now that I see it written out. Now, my
next question would be, how do I rewrite my code to have look at four fields?

Private Sub cboStreetNames_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[?] = " & Str(Me![cboStreetNames])
Me.Bookmark = rs.Bookmark
Me!cboFindWO.Value = ""

End Sub

Thanks,
NX

Query parameters are not powerful enough to offer drop-down lists.

However, you can open a form that has a combo to select the value, and refer
to the combo on the form in your query.

It would end up something like this:

SELECT tblCuts.*
FROM tblCuts
WHERE ([Forms].[Form1].[Combo1] Is Null)
OR (tblCuts.Address1 Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.StreetName Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.[From] Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.[To] Like "*" & [Forms].[Form1].[Combo1] & "*")
ORDER BY tblCuts.DateCalled DESC;

Note that FROM and TO are reserved words in JET, so not a good choice for
field names. You can get away with it in this context by putting them in
square brackets and including the table name, but for a list of the names to
avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Everybody,
I would like to include a like statement in my combo look up filter. This
is what is in the criterion now:
[forms]![frmSearch]![cboStreetNames]

This is the SQL I've used in the past, it works great but I would rather
use
a combo box instead of a parameter prompt:
SELECT tblCuts.DateCalled, *
FROM tblCuts
WHERE (((tblCuts.Address1) Like "*" & [STREET NAME] & "*")) OR
(((tblCuts.StreetName) Like "*" & [STREET NAME] & "*")) OR
(((tblCuts.From)
Like "*" & [STREET NAME] & "*")) OR (((tblCuts.To) Like "*" & [STREET
NAME] &
"*"))
ORDER BY tblCuts.DateCalled DESC;

It would be great if I could select from the combo if there were something
available, but also have the like statement to filter on if there are no
matches.

Thanks,
NX

 

[Nick X]

Figured it out. I just converted an old macro to code:

DoCmd.ShowAllRecords
DoCmd.Requery
DoCmd.ApplyFilter "qSearchStreet"
DoCmd.Requery
cboStreetNames = ""
DoCmd.GoToControl "cboStreetNames"

thank you for your help!
NickX

Query parameters are not powerful enough to offer drop-down lists.

However, you can open a form that has a combo to select the value, and refer
to the combo on the form in your query.

It would end up something like this:

SELECT tblCuts.*
FROM tblCuts
WHERE ([Forms].[Form1].[Combo1] Is Null)
OR (tblCuts.Address1 Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.StreetName Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.[From] Like "*" & [Forms].[Form1].[Combo1] & "*")
OR (tblCuts.[To] Like "*" & [Forms].[Form1].[Combo1] & "*")
ORDER BY tblCuts.DateCalled DESC;

Note that FROM and TO are reserved words in JET, so not a good choice for
field names. You can get away with it in this context by putting them in
square brackets and including the table name, but for a list of the names to
avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Everybody,
I would like to include a like statement in my combo look up filter. This
is what is in the criterion now:
[forms]![frmSearch]![cboStreetNames]

This is the SQL I've used in the past, it works great but I would rather
use
a combo box instead of a parameter prompt:
SELECT tblCuts.DateCalled, *
FROM tblCuts
WHERE (((tblCuts.Address1) Like "*" & [STREET NAME] & "*")) OR
(((tblCuts.StreetName) Like "*" & [STREET NAME] & "*")) OR
(((tblCuts.From)
Like "*" & [STREET NAME] & "*")) OR (((tblCuts.To) Like "*" & [STREET
NAME] &
"*"))
ORDER BY tblCuts.DateCalled DESC;

It would be great if I could select from the combo if there were something
available, but also have the like statement to filter on if there are no
matches.

Thanks,
NX

 

[Nick X]

I made the combo row source the value I wanted to look up from a source table
and created "qSearchStreet" based on the SQL you gave me.

SELECT tblCuts.Address1, tblCuts.StreetName, tblCuts.From, tblCuts.To
FROM tblCuts
WHERE ((([Forms].[fCuts].[cboStreetNames]) Is Null)) OR (((tblCuts.Address1)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.StreetName)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.From) Like
"*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.To) Like "*" &
[Forms].[fCuts].[cboStreetNames] & "*"))
ORDER BY tblCuts.DateCalled DESC;

I placed the following code into the After Update:


DoCmd.ShowAllRecords
DoCmd.Requery
DoCmd.ApplyFilter "qSearchStreet"
DoCmd.Requery
cboStreetNames = ""
DoCmd.GoToControl "cboStreetNames"