Use "Like" Statement in Combo Look-Up Filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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

Allen Browne said:
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.

Nick X said:
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
Click to expand...
Click to expand...
 
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

Allen Browne said:
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.

Nick X said:
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
Click to expand...
Click to expand...
 
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"
 
Back
Top