Syntax problem on building SQL using wildcard * on a numeric field (1 Viewer)

[DBApprentice]

Hi!

I am having a hard time with this string:

Dim strSQL As String

strSQL = "SELECT tbl1Cards.CardID, tbl1Cards.CardNumber, StatusCard AS Status, tbl1Categories.Category, tbl1Observers.[FirstName] & "" "" & [MiddleName] & "" "" & [Lastname] AS FullName " _
            & "FROM tbl1Categories INNER JOIN (tbl1StatusCard INNER JOIN (tbl1Observers INNER JOIN tbl1Cards ON tbl1Observers.ObserversID = tbl1Cards.ObserverFK) ON tbl1StatusCard.StatusCardID = tbl1Cards.StatusCardFK) ON tbl1Categories.CategoryID = tbl1Cards.CategoryFK " _
            & "WHERE [tbl1Cards.CardNumber] LIKE [ " & "*" & Me.txtSearchcard & " ] " _
            & "ORDER BY tbl1Cards.ObservationOn DESC; "
            
Me.subFindcards.Form.RecordSource = strSQL
Me.subFindcards.Form.Requery

The user should enter the number on a unbound textbox on form and I want to use the LIKE operator plus the wildcard * but I get an error on the WHERE clause when I run this code:

"WHERE [tbl1Cards.CardNumber] LIKE [ " & "*" & Me.txtSearchcard & " ] "

_

What is the right syntax for the LIKE + wildcard on a number value?

Thanks,

 

[arnelgp]

remove the Squre bracket:

LIKE '*" & Me.txtSearchcard & "*'"

 

[DBApprentice]

Thanks, arnelgp!!!

 

[isladogs]

One thing to be aware of is that using wildcards at the start and end will be very slow on large recordsets. If you don't need both it would be better to remove one *

 

[DBApprentice]

If you don't need both it would be better to remove one *

Hi Colin!

From any side?

Cheers!

 

[isladogs]

Depends how it will be used.
What you have now will search for selected numbers anywhere in a group of numbers.

For example:
15* will pick up 15, 153, 1594 but not 215 or 8152
*15 will get 15, 215 & 8152 but not 153 or 1594.
*15* will get all of them but will take longer.
15 with no wildcards will only get that exact value but will be fast

Assuming the numbers are sorted in order, the leading wildcard will add longer to the search time.

I would also recommend indexing fields used in searches.

 

[DBApprentice]

Depends how it will be used.
...
Assuming the numbers are sorted in order, the leading wildcard will add longer to the search time.

I would also recommend indexing fields used in searches.

I was not aware of that perfomance issue, at least the field I am using is indexed.

On a second thought, As per the owner explained to me indeed there is no point in using wildcards on this particular form because it needs to return only exact match so I will use the "=" operator and remove the wildcards. Should the user enter the wrong value or the search return nothing I will pop a msgbox.

I will save the wildcard for another form and use anerlgp´s answer.


Thanks for the valuable insight!

 

[The_Doc_Man]

Diogo, glad you recognized that the index was there.

As a rule of thumb: If you are going to do a lot of searches on a given table, your best performance ALWAYS occurs if the most commonly searched fields are indexed. NOTE, however, that nothing is "free." Each index you add increases the speed of searches but DECREASES the speed of updates (since you now have to update both the table AND its indexes). Access places a limit on the number of indexes per table, so they are a limited resource.

In another thread, we discussed that there are tradeoffs or "balancing points" regarding normalization. This is another one of those "balancing points." The idea of this balance is to ask the question "how often do I search vs. how often do I update?" Where the search/update ratio is heavily favored towards searching, consider an index. Where the search/update ratio is closer to even or balanced towards updates, perhaps you should do without the particular index. If you have a lot of updates and few searches, drop the index like a hot rock. Where is the break-even? Damned if I know! That's why it is a balancing act.

Here is WHY you want to be careful about wildcard searches: When you have a leading wildcard, the normal index-search algorithm cannot be used because that search is based on a method that expects a definite beginning to a search key. With a wild card at the front, that means you have to linearly scan the table or index for whether the search key is contained in the indexed field. With a definite beginning of the field, you can use a more binary-style search to get close, then scan a more limited range to finish the search.

Here's the performance difference. For a table with N entries, if you skip the wildcard, you can do a binary search for which the expected number of probes is log(base 2) N (rounded up); with the wildcard, the expected number of probes is N/2. So for a table of 256K records, your binary search is 18 probes; the same search using a linear scan is 128K probes.

 

[DBApprentice]

Here's the performance difference. For a table with N entries, if you skip the wildcard, you can do a binary search for which the expected number of probes is log(base 2) N (rounded up); with the wildcard, the expected number of probes is N/2. So for a table of 256K records, your binary search is 18 probes; the same search using a linear scan is 128K probes.

Hello, The_Doc_Man!

I wonder how you and the rest of the heavyweights here get information like this... In fact I wonder how you guys reached such a high level on this. It´s amazing how all of you have answers and insights for all my questions.

It´s funny that when I run this search on the Excel counterpart I could notice that because I used the wildcard as well, but I have not noticed this on Access yet because I have not imported the database yet. I guess that I would notice that too and would be wondering what was wrong...

On a side note: I have indexed the CardNumber because i wanted to ensure it was going to have no duplicates, but after you pointed out the Natural vs Synthetic keys I went back to the table design draft and noticed that CardNumber is a natural key so I decided to leave it indexed. Since that was a natural key and I could ensure via VBA code to generate unique random numbers I think I can get rid of the artificial ID using Autonumbers. what do you think?

 

[Pat Hartman]

Using LIKE almost always forces a full table scan rather than an index search. So, the number of rows in the table determines how slow the search will be. On a thousand records, it will be fine. On a million, not so much.

Also LIKE is used for STRING searches. Your results may not be correct if the field you are searching is a numeric or a date data type.

 

[isladogs]

Also LIKE is used for STRING searches. Your results may not be correct if the field you are searching is a numeric or a date data type.

Hi Pat
I've seen this comment before but never checked it until now.
I've just run tests using both leading & trailing wildcards on 3 number and 1 date fields. The results gave exactly what I would have expected.

Now of course the query was slow - approx 14 seconds to get that output from a table of 2.6 million records.
So whilst I would never use a query like that in the real world, I'm not sure I understand the reasoning behind your comment

I didn't test a SQL statement or other types of wildcard

However. this link also indicates various wildcards such as *,! & # can be used in queries with a wide range of datatypes including number, datetime, currency & autonumber
https://support.office.com/en-us/article/access-wildcard-character-reference-af00c501-7972-40ee-8889-e18abaad12d1

 

[Pat Hartman]

In Access, formatting can affect how LIKE works. I think SQL Server just won't allow you to use a string function (which is what LIKE is) against numeric data. In the case of dates, some users may have their default date set to show leading zeros which affects how searches work.

There is NEVER, EVER a reason to use LIKE against a date field since there are date functions that will extract the part of the date you want to search on.

There may be some reason to use LIKE against a numeric field although I have never found a valid one. Range searches make more sense and will use indexes if they exist.

I attached a database with a couple of queries that show how what you see isn't what you are searching on. It falls into the same category of issues caused by table level lookups when the stored value is numeric but the visible value is a string. Most people have trouble figuring out that they have to search for the numeric value rather than the string value.

Since LIKE is a string function, it is just poor practice to use it on a numeric field. Access allows it but to make it possible, Access MUST internally format the numeric value as a string and it may be something other than what you expect. If you MUST use LIKE against a numeric field, at least format the field yourself so you won't be blindsided.

 

[Galaxiom]

I was led to believe that using like on trailing only wildcards was a lot more efficient than leading wildcards. Some have claimed it is because the index can be used if the leading characters are explicit. I have never tested it but I generally avoid them anyway.

However I recall there is some scenario that does not allow a leading wildcard. Can't exactly remember what right now. Maybe the Find or Filter on ADO recordsets?

 

[The_Doc_Man]

We have to remember that with Access, there is a "silent conversion" in some expressions. It IS possible (just not very pretty) to have a BYTE integer being used as a JOIN against a LONG integer and the JOIN will occur. If you have big integers in the LONG field, you might not see what you expected, but Access will try, particularly if it is a one/many JOIN and the BYTE is the "one" side. I would not be surprised that some kind of conversion occurs for a LIKE against a numeric field but I have no bloody clue as to what part would match.

The comment about at least doing your own formatting so that you absolutely know what is being compared? Solid gold.

 

[isladogs]

@Pat
There is no disagreement that using LIKE with number or date fields is not a good idea
My point was purely that it works correctly for both types of field as my example and the MS article both confirm

The query I posted was just for testing
AFAIK I have NEVER used LIKE on a number or date field in a query before and will probably never do so again.

I agree that using date functions is better than using LIKE
Also that it would be wise to check the query outcomes are what is expected (as you should do for any query)
If formatting helps ensure that, all well & good.

@Greg
As I understand it, using leading wildcards forces Access to check every record.
This is inherently inefficient and should indeed be avoided wherever possible
Using trailing wildcards creates less 'overhead'

 

[DBApprentice]

I would like to thank you all for the information about the use of wildcards on strings, dates and number fields whe running the queries.

I feel it is very important to know why one should NOT DO something as much as knowing one should DO and you always provide both.

Regards,

 

[CJ_London]

However I recall there is some scenario that does not allow a leading wildcard. Can't exactly remember what right now. Maybe the Find or Filter on ADO recordsets?

At one time you could not use leading wildcards in ADO recordsets, but I believe you can now.

As with other responders I avoid leading wildcards like the plague (although I accept there are occasions where they are unavoidable) - most users will know whatever they are looking for starts with and I train them to include a wildcard if necessary. Even starting with an initial letter before including a * will be roughly 26 times faster then just starting with a * - assuming the field is indexed of course, otherwise I guess it doesn't matter since indexed fields will be searched sequentially.

with regards using Like with numbers - yes it works, however I don't know whether indexing is used per se since the number is not stored as separate characters but as its binary equivalent.

 

[DBApprentice]

...
with regards using Like with numbers - yes it works, however I don't know whether indexing is used per se since the number is not stored as separate characters but as its binary equivalent.

Indeed when I first saw Arnelgp´s answer I noticed it was working and it was not necessary to convert that number to a string. The field I was searching was indexed though.

BUT I decided to change the approach to this thanks to Colin´s advise to change that textbox to a combobox plus all the explanation provided by The_Doc_Man and PatHartman convinced me I was going for the wrong game...

Nonetheless I will apply what I have learnt here in another part of my small and first project if necessary.