Using wildcards with all numeric strings

[kballing]

I have a field that holds item numbers. They are usually a number, but sometimes contain other characters and/or spaces.

e.g.
123456
456789
789123
ABC123
BLAH01
456 XYZ

When I query this field using LIKE and a wildcard, I am not seeing any all numeric results:

WHERE ItemNumber LIKE '*A*' returns "ABC123" and "BLAH01"
WHERE ItemNumber LIKE '*123*' returns "ABC123" but not 123456 or 789123
WHERE ItemNumber LIKE '*456*' returns "456 XYZ" but not 456789

What's going on here? Is there some wildcard syntax that I'm not aware of or is access secretly formatting all numeric strings as numbers?

(I am using this query to populate a list box based on user input. Whatever the user types in would get placed between the asterisks. e.g. '*'&Input&'*')

 

[llkhoutx]

Your're misunderstanding the search:

"*" & Input & "*" produces values in the middle of Input, exclude beginnng with Inputand ending with Input.

Input & "*" produces end with values.

"*" & Input produces beginging with values.

 

[Galaxiom]

"*" & Input & "*" produces values in the middle of Input, exclude beginning with Input and ending with Input.

Not correct.
The "*" wildcard means any number (including zero) of any character.
AFAIK, the behaviour is the same for strings or numbers as the context treats any type of field, even dates, as a string when using the Like operator.

BTW. To exclude the strings beginning with the Input you should have to use:
Where Like "*" & Input & "*" And Not Like Input & "*"

Are you sure the query is not being limited by Where criteria on other fields?
Otherwise, please post an example because there would have to be something unusual in this problem.

 

[llkhoutx]

Well, I be Galaxiom. I ran test cases and you are right.

 

[Galaxiom]

Well, I be Galaxiom. I ran test cases and you are right.

I knew the behaviour of the * wildcard from DOS back in the days when there wasn't so much to know.

We are probably all carrying around assorted myths and phobias from the painful days of our Access learning curve. I know I have had a few of mine blown away here.

It is one of the great things about this site. Someone will almost always post a correction to a misunderstanding and that makes it worth posting. Sometimes they post a better way.

 

[kballing]

I figured it out.

I had a join in my query that should have been a right join but was an inner join and that was filtering out the majority of records from my table.