Like Function (1 Viewer)

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
Hi All,

Please could you assist

I have a string value of 2010-0006-CN, I want the query to show me all records like this one

When using Like "*2010-0006-CN*" I only get 2010-0006-CN but i know I also have a record 2021-0006

When I link this form to another form I would like to use below but the result is null
Like "*[Forms]![SB Detail - Not Loaded]![SB/AD No]*"

The 2010-0006 would be m primary search, I could even have a 2010-0006-A or 2010-0006-1 as a record set

Please could you advise
 

Minty

AWF VIP
Local time
Today, 10:54
Joined
Jul 26, 2013
Messages
10,355
The wild card comes after the -CN at the end of your string so in your first example it won't return 2021-0006.

Your other expression would require concatenation;

Code:
Like "*" & [Forms]![SB Detail - Not Loaded]![SB/AD No] & "*"
 

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
The wild card comes after the -CN at the end of your string so in your first example it won't return 2021-0006.

Your other expression would require concatenation;

Code:
Like "*" & [Forms]![SB Detail - Not Loaded]![SB/AD No] & "*"
How would I get 2010-0006 or any other string?
 

Minty

AWF VIP
Local time
Today, 10:54
Joined
Jul 26, 2013
Messages
10,355
If you specify

Like "*2010-0006-CN*"

Then the results MUST include all the parts in between the wildcards for it to return results e.g. including the -CN
If you specify

Like "*2010-0006*"

You would get all the results you would expect.
If these are existing known values why not present them in a combo or list box - you could link all the results to a 'Find As You Type' style filter, have a search on here for examples by MajP
 

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
If you specify

Like "*2010-0006-CN*"

Then the results MUST include all the parts in between the wildcards for it to return results e.g. including the -CN
If you specify

Like "*2010-0006*"

You would get all the results you would expect.
If these are existing known values why not present them in a combo or list box - you could link all the results to a 'Find As You Type' style filter, have a search on here for examples by MajP
No these are not existing known value, this was just an example

Any value in my main form must have a lookup for like values in the second form
So any value in the main form should be a lookup from
Like "*" & [Forms]![SB Detail - Not Loaded]![SB/AD No] & "*"
So a combo box wont work

I am just not getting any other result than 2010-0006-CN, not even 2010-0006 is displayed but is an existing value
 

Minty

AWF VIP
Local time
Today, 10:54
Joined
Jul 26, 2013
Messages
10,355
Okay, I think I will need to see some actual data. Ideally will need three things;
Original data values, a search value and your expected results.

Enough sample data to prove how it should work.
 

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
Okay, I think I will need to see some actual data. Ideally will need three things;
Original data values, a search value and your expected results.

Enough sample data to prove how it should work.
With reference to just this sample

see below

1631101438256.png


When the main form has focus on 2010-0006 or 2010-0006-CN, I would like to see both on the second form
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:54
Joined
May 21, 2018
Messages
8,463
That is one example, may need more. Does your data follow this pattern throughout?
not sure why your are not simply searching for like "2010-0006*" which brings in all results.
 

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
With reference to just this sample

see below

View attachment 94222

When the main form has focus on 2010-0006 or 2010-0006-CN, I would like to see both on the second form

Hi,

There is no pattern in any of the data.

I can not hard code, I have 8025 record with no specific format

1631102085444.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:54
Joined
Sep 21, 2011
Messages
14,050
So, just enter enough characters to get what you want? :(

62-00
-00

etc, etc
 

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
So, just enter enough characters to get what you want? :(

62-00
-00

etc, etc
I am not entering any characters, when any of the above list is in focus on the main form, any like records should reflect in the second form
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:54
Joined
May 21, 2018
Messages
8,463
So if you select 292 72 0276 you want to return anything with 292? anyting with 292 72?
If I pick 62-00-38 do you want anything with 62?
You have to have some kind of rule, if not you need some kind of user interface to help you search. You want this but not that
This is pretty close to allowing you to tailor the search.
Here is an example interface where you can pick multiple words to search and choose some or all. Then pick AND (must have) or OR (may have)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:54
Joined
May 21, 2018
Messages
8,463
list is in focus on the main form, any like records should reflect in the second form
Do you mean when one record has the focus you want like records in the other form or all we talking all records in the first form?

Also You have not defined what it means to be "like". The best I can think if you select
2010-006-cn You could return all records that contain 2010, OR 2010-006, OR 2010-006-CN, OR 006-CN, OR CN.
In other words how much LIKE is like?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:54
Joined
May 7, 2009
Messages
19,175
add code to the Current Event of the Main form so it will filter the Second form:

Code:
private sub form_current()
dim s as string
dim ln as integer
s = Me![SB/AD No] & ""
ln=len(s)
do while ln > 0
    if IsNumeric(Right$(s,1)) then
        exit do
    end if
    s = left$(s,len(s)-1)
loop
if len(s) then
    If Right$(s, 1)="-" then
        s = Left$(s, Len(s)-1)
    End If
    [secondForm].Form.Filter = "[SB/AD No] Like '" & s & "*'"
end if
end sub
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:54
Joined
May 21, 2018
Messages
8,463
add code to the Current Event of the Main form so it will filter the Second form
Gismo, this is my whole point. Your example is specific to one possible case so you need to provide more information. ArnelGP's provided code solves the one pattern that you provided, and that does not address the majority of cases in your updated data set. You need to provide a more general description of possible problems. Most of the data does not follow that pattern originally provided. So @Gismo needs to respond if we are just talking about this one pattern, and I think the answer is no.

So Gismo, if the record is 292 72 0276, do you want all the 292 72 records?
What about
62A014 do you want to see things in the same pattern 67A018
2010-0006-CN but i know I also have a record 2021-0006
In that case the only thing the same is -0006- . The years are different and so is the pattern.
 
Last edited:

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
Do you mean when one record has the focus you want like records in the other form or all we talking all records in the first form?

Also You have not defined what it means to be "like". The best I can think if you select
2010-006-cn You could return all records that contain 2010, OR 2010-006, OR 2010-006-CN, OR 006-CN, OR CN.
In other words how much LIKE is like?
How much like is Like, agree, so many different variants

Yes the first form is just one record, second for could display multiples
I do have another layer of filtering so the options in the second could be limited somewhat

I understand what you are saying and I am not sure on how to respond
So if the record is 292 72 0276, do you want all the 292 72 records? - This would be a correct assumption

62-00-38 would have 62-00-38 as reference and A B or c as variants

So none of my sample data could really by categorized as a rule of thumb
 

Minty

AWF VIP
Local time
Today, 10:54
Joined
Jul 26, 2013
Messages
10,355
I don't think there is any simple way to do what you want.

Unless you can create a relationship between the disparate identifiers, and maintain it in a table where you could look up the "similar" records, you would need some kind of fuzzy logic to provide a lookup.

This is one of those instances where to the human eye, it's quite simple, but to a hard rule-based set of instructions, it is really difficult.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:54
Joined
Sep 21, 2011
Messages
14,050
Might even be worth ACTUALLY typing in what you want? :)
 

Gismo

Registered User.
Local time
Today, 12:54
Joined
Jun 12, 2017
Messages
1,298
Might even be worth ACTUALLY typing in what you want? :)
I would not know what i need, that is why the second form should suggest all possible records based on the record in the main form
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:54
Joined
May 21, 2018
Messages
8,463
I think you need a robust control like I demoed.
Here i click on an item in the list on left. Then It provides me all possible choices and I can pick one or more.

Search.jpg

So I "Select Abbott - Keeling" Limited and then provided with many variations to choose to search. Each individual word, Each combination of a words, the complete word, then letter by letter. So you can click on any of those patterns to choose from. This way you can narrow done.
 

Users who are viewing this thread

Top Bottom