using wildcards in a like statement with variables 2

[aeroposcot]

I have a combo box that is filtering a form. The combo box contains employee's last name. However, the field that it is looking up can contain more than one name (e.g., possibilities include just Smith, Smith & Johnson, Smith & Jones & Johnson, etc.).

Thus, when I click on the combo box, I want all records containing "Smith" to show up. However, I have only been able to bring up exact matches. Here is my code. Thanks in advance.

Here's my code:

Dim strSQL As String
Dim strSQLSF As String

Status_CB = Null

strSQL = "SELECT * FROM Pipeline"
strSQL = strSQL & " WHERE Pipeline.[Lead_Person(s)] LIKE '" & S_MD_CB & "' "
strSQL = strSQL & " ORDER BY Pipeline.Status;"

Status_CB.RowSource = strSQL

strSQLSF = "SELECT * FROM Pipeline "
strSQLSF = strSQLSF & "WHERE Pipeline.[Lead_Person(s)] LIKE '" & S_MD_CB & "' "


Me!Data_Entry_SF.LinkChildFields = "[Lead_Person(s)]"
Me!Data_Entry_SF.LinkMasterFields = "[Lead_Person(s)]"
Me.RecordSource = strSQLSF
Me.Requery

 

[CautionMP]

aeroposcot,
[tt]" WHERE Pipeline.[Lead_Person(s)] LIKE '*" & S_MD_CB & "*' "[/tt]?

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[aeroposcot]

When I do that, not all of the results are returned. Only a selected few are returned.

 

[Golom]

What's the value in S_MD_CB?

 

[aeroposcot]

S_MD_CB is a combo box whose values are linked to a table called S_MD_Names that contains last names.

Row Source: SELECT S_MD_Names.LastName FROM S_MD_Names ORDER BY S_MD_Names.LastName;

I am then taking this input and trying to search another table called Pipeline to find all records with the person's last name in the Field [Lead_Person(s)].

 

[Golom]

LIKE shouldn't be case-sensitive but just in case (no pun intended)

WHERE UCase(Pipeline.[Lead_Person(s)]) LIKE '*" & UCase(S_MD_CB) & "*'

 

[aeroposcot]

Since it's a small DB, all of the entries are typed correctly, but I'll add the additional code for future. So, that's not the problem. Any other thoughts? Thanks for your help.

 

[CautionMP]

aeroposcot,
Is it possible that this is also limiting the records returned?

...
     Me!Data_Entry_SF.LinkChildFields = "[Lead_Person(s)]"
     Me!Data_Entry_SF.LinkMasterFields = "[Lead_Person(s)]"
...

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[aeroposcot]

I don't think so. I think something in these two lines of code is limiting the results:

strSQLSF = "SELECT DISTINCT Pipeline.[Lead_Person(s)] FROM Pipeline "
strSQLSF = strSQLSF & " WHERE Pipeline.[Lead_Person(s)] LIKE '*" & S_MD_CB & "*'

 

[Golom]

If there are duplicates in [blue]Pipeline.[Lead_Person(s)][/blue] then they will be reduced to a single occurrence because of the DISTINCT keyword.

Build yourself a simple test query like this

Select Pipeline.[Lead_Person(s)] 

From Pipeline

Where Pipeline.[Lead_Person(s)] LIKE '*Smith*'

and see what you get (substitute some name from your data for "Smith" in the above.)

Does the output of that completely unfiltered query match what you are getting from your main query?

 

[aeroposcot]

Even when I hard code in the name and take out the DISTINCT, my results are still getting filtered. Instead of returning 19 entries, I get two. I've taken out the cascading feature too in attempt to simplify things, but I still can't get it to work. Sigh.

Here's my latest code.

Private Sub S_MD_CB_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

Status_CB = Null

strSQL = "SELECT DISTINCT Pipeline.Status FROM Pipeline"
strSQL = strSQL & " ORDER BY Pipeline.Status;"

Status_CB.RowSource = strSQL

strSQLSF = "SELECT Pipeline.[Lead_Person(s)] From Pipeline Where Pipeline.[Lead_Person(s)] LIKE '*Smith*'"

Me!Data_Entry_SF.LinkChildFields = "[Lead_Person(s)]"
Me!Data_Entry_SF.LinkMasterFields = "[Lead_Person(s)]"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

Private Sub Status_CB_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

strSQLSF = " SELECT * FROM Pipeline "
strSQLSF = strSQLSF & " WHERE Pipeline.[Lead_Person(s)] LIKE '*" & S_MD_CB & "*' And "
strSQLSF = strSQLSF & " Pipeline.Status = '" & Status_CB & "'"


Me!Data_Entry_SF.LinkChildFields = ""
Me!Data_Entry_SF.LinkMasterFields = ""

Me!Data_Entry_SF.LinkChildFields = "[Lead_Person(s)];Status"
Me!Data_Entry_SF.LinkMasterFields = "[Lead_Person(s)];Status"
Me.RecordSource = strSQLSF
Me.Requery

End Sub

 

[aeroposcot]

CautionMP,

I think you were correct in your assessment about the LinkChildFields & LinkMasterFields limiting my results. However, I can't figure out how to correct it.

 

[CautionMP]

aeroposcot,
It sounds like the [tt]Lead_Person(s)[/tt] field is some type of text field that could hold more that one persons name in some type of delimited format?

I'm also guessing that you have some type of sub-form (hence the Master/Child relationship), so you need a field with a single persons name so you can tie the data from the two forms together.

...
  strSQLSF = "SELECT [b]" & Me.S_MD_CB & " AS ThisPerson,[/b] * " & _
             "FROM Pipeline " & _
             "WHERE Pipeline.[Lead_Person(s)] LIKE '*" & S_MD_CB & "*' " & _
             "AND Pipeline.Status = '" & Status_CB & "';"
 ...
 Me!Data_Entry_SF.LinkChildFields = "[b]ThisPerson[/b]];Status"
 Me!Data_Entry_SF.LinkMasterFields = "[b]???[/b];Status"
 Me.RecordSource = strSQLSF
...

Now the question is how do you incorporate the [tt][ThisPerson][/tt] field into the recordset of your sub-form.

CMP

P.S. I should probably say something about 'normalized data structures' here, but...

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)