Find "11*" in a table, but not 11* using a wildcard | Newbie | | Join Date: Oct 2007
Posts: 14
| | |
I have another newbie question for you guys... I apologize in advance for how simple it is...
I've got a string of data, included in that string is 11* as it appears, the * is not a wildcard. I'm using the function
Like "*" & "11*" & "*" to find any records that have "11*", again, the * is not a wildcard. But it's interpreting it as a wildcard, so I'm getting 1105 and 11X and so on. How can I change the formula so that it will return 11* in the middle of the string?
Thanks in advance for the help.
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Find "11*" in a table, but not 11* using a wildcard
Quote:
Originally Posted by BlackJack17 I have another newbie question for you guys... I apologize in advance for how simple it is...
I've got a string of data, included in that string is 11* as it appears, the * is not a wildcard. I'm using the function
Like "*" & "11*" & "*" to find any records that have "11*", again, the * is not a wildcard. But it's interpreting it as a wildcard, so I'm getting 1105 and 11X and so on. How can I change the formula so that it will return 11* in the middle of the string?
Thanks in advance for the help. Avoid the confusion with treating the * Wildcard as a Literal. This will accomplish exactly what you are requesting, but only specifying an explicit Criteria instead of using the Like Operator: - InStr([Your String],"11*") > 0
| | Newbie | | Join Date: Oct 2007
Posts: 14
| | | re: Find "11*" in a table, but not 11* using a wildcard
Can I put that on the Criteria line of my query? Or do I put it somewhere else?
| | Newbie | | Join Date: Nov 2007 Location: Linden, NJ
Posts: 1
| | | re: Find "11*" in a table, but not 11* using a wildcard
Select * From TABLENAME Where substr(FIELDNAME,1,3) = '11*'
Substitute TABLENAME and FIELDNAME according to what table and field your looking at.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Find "11*" in a table, but not 11* using a wildcard
Quote:
Originally Posted by goodmanstl7 Select * From TABLENAME Where substr(FIELDNAME,1,3) = '11*'
Substitute TABLENAME and FIELDNAME according to what table and field your looking at. goodmanstl7, there is no substr Function that I am aware of. Are you referring to the Transact-SQL Function SubString()?
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Find "11*" in a table, but not 11* using a wildcard
Quote:
Originally Posted by BlackJack17 Can I put that on the Criteria line of my query? Or do I put it somewhere else? Assuming you are looking for the String '11*' anywhere in the [LastName] Field of tblEmployees: - You can place it in the Criteria Row of the Query Grid, in this demo in the [LastName] Field:
- InStr([LastName],"11*")>"0"
- You can place it in an SQL Statement directly and return all Records meeting the Criteria:
- SELECT tblEmployeePFD.LastName, *
-
FROM tblEmployeePFD
-
WHERE ((InStr([LastName],"11*")>"0"));
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,002
| | | re: Find "11*" in a table, but not 11* using a wildcard
Just out of curiosity, ADezii, could you have used - InStr([LastName],"11" & Chr(42))>"0"
as well?
Linq ;0)> | | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Find "11*" in a table, but not 11* using a wildcard
Quote:
Originally Posted by missinglinq Just out of curiosity, ADezii, could you have used
- InStr([LastName],"11" & Chr(42))>"0"
as well?
Linq ;0)> Sorry Linq, don't know how I missed this one but InStr([LastName],"11" & Chr(42))>"0" will work equally as well.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
