'Like' function

[fat controller]

I have never used the Like function yet, so have no concept of how it can or should be used - so, I'd be grateful for any pointers?

Can it be used alongside DCount (to find how many records match or nearly match the search text), or is it restricted to using it with the FindRecord function?

 

[jdraw]

Here's some examples and description using Like.

 

[fat controller]

Thanks

Not sure how I can use that to achieve what I want.

Essentially, I am going to have an unbound textbox on a form that a user can enter a customer name into, and I would like it to then go and find all records that match or are like the value in the text box?

Would it be more appropriate to use the acAnywhere option within the FindRecord function?

 

[jdraw]

Perhaps something like

SELECT * (or specific fields) FROM YourTable
WHERE YourField Like "*" & me.TextBox & "*"

to find records in Yourtable where yourField contains the string in the textbox on a form.

You use yourField &"*" to find a record(s) that starts with the characters in the textbox.

You use "*" & yourField to find a record(s) that ends with the characters in the textbox.

 

[Galaxiom]

A minor point but Like is not a function. It is an operator.

It is native to SQL and as such is processed very efficiently by database engines.

 

[Dick7Access]

A minor point but Like is not a function. It is an operator.

It is native to SQL and as such is processed very efficiently by database engines.

Operators kind of went out with the touch tone phones!

 

[Galaxiom]

Operators kind of went out with the touch tone phones!

I dare say there are some here who have never experienced decadic dialling let alone operator connections.

I can remember four digit phone numbers.

The older members probably remember using party lines.

 

[namliam]

It is native to SQL and as such is processed very efficiently by database engines.

hmz, like isnt very efficient.... that is compared to = or <>

 

[Galaxiom]

hmz, like isnt very efficient.... that is compared to = or <>

It can be compared to using some Access functions.

If one considers what it must do and the fact that it can't use the field's index it does pretty well.

 

[namliam]

True, though calling it efficient is overstating it IMHO

Like "namliam"
would be (much) better of as
= "namliam"
Better yet if it is an indexed column

 

[Galaxiom]

True, though calling it efficient is overstating it IMHO

Like "namliam"
would be (much) better of as
= "namliam"
Better yet if it is an indexed column

Yes but "=" won't find anything at all if the match isn't exact.

Do show us a more efficient way to return partial matches.

You need to compare like with like. Pun intended.

 

[namliam]

Thats the point, people using like instead of =
Offcourse like has its place

 

[Dick7Access]

I dare say there are some here who have never experienced decadic dialling let alone operator connections.

I can remember four digit phone numbers.

The older members probably remember using party lines.

I not only remember party lines, I remember having to climb the pole next to the house to answer the phone.

 

[CazB]

I also remember 4-digit phone numbers

My mum and dad's was 3210.... our local GP was 3120.... it was a cool number to have but we changed it after getting a large number of misdials at all times of day and night, and a large number of calls from toddlers!!

 

[Dick7Access]

I also remember 4-digit phone numbers

My mum and dad's was 3210.... our local GP was 3120.... it was a cool number to have but we changed it after getting a large number of misdials at all times of day and night, and a large number of calls from toddlers!!

I was, of course, just kidding about climbing the pole, but it is true that there were only two phones on the street, 4 party line (Bellingham, MA 1948). We had one of them and had to hold the reciver to the ear and the mouth piece separate. When you lifted the ear piece the operator would come on the line and say "number please". I will never forget my number because my mother had me repeat it every morning before I left for school. Canal 862J3.

Of course I had to walk 5 miles to school up hill both going and returning. In the snow winter and summer.

 

[AOB]

I had a similar issue whereby I wanted users to be able to look up account numbers but may not necessarily know precisely the full string. Usually, they know them by the last 4 or 5 digits. Also, an account number could be in IBAN format, but the user knows only the 'local' version (so again, while the strings are 'similar', they are not identical, and therefore logical operators are rendered pretty much useless)

So I put together a 'fuzzy match' function which compared a string entered by the user against all of the available options (i.e. all account numbers) and scored each pair based on the number of consecutive characters (excluding spaces and non-alphanumeric characters such as hyphens and slashes) which were common to both.

The function fired on the Change event of the textbox so that as the user types, the top 5 matches appear in a listbox below, from which they can then select as appropriate.

As long as the lookup list is reasonable in size (mine is about 2,000-odd), it's surprisingly quick and the results are usually quite reliable for 4 or more characters. It's not perfect but it does the job for me? I use the same function for names and it also works there quite well.

Happy to share the function here if you think it maybe useful?