MS Access 2007
What is the syntax for finding a wild card in a text string.
Specifically, I'm trying to find a # sign when a user submits it in an address.
No luck with these...
Like '*' & Chr(35) & '*'
Like '*' & '#' & '*'
thanks!
MS Access 2007
What is the syntax for finding a wild card in a text string.
Specifically, I'm trying to find a # sign when a user submits it in an address.
No luck with these...
Like '*' & Chr(35) & '*'
Like '*' & '#' & '*'
thanks!
you enclose with square brackets
Like '*[#]*'
see this link for all about using wildcards https://support.office.com/en-us/art...3-61897c87b3f4
Thanks! Much appreciated!
Sorry, I had to come back to this. This doesn't work in an MS Access query. Anything inside of [] brackets is a prompt for user input.
Like '*[#]*'
Thanks
Perhaps you could show us a sample of what you are looking for.
If you just want to find a "#" in a string, this might help with syntax.
As Ajax said for wildcards you enclose in square brackets.Code:Sub testNumsign() Dim x As String x = "Bob Jones, #124 Smith Street, Centerville" 'sample address Debug.Print x, IIf(InStr(x, "#") > 0, " in position " & InStr(x, "#"), " -#-Not in x") End Sub
works OK for me
so does this
Like '*' & '[#]' & '*'
but this will come back with a prompt
Like '*' & [#] & '*'
This is my query. [#] Prompts for user input.
SELECT Nz(Count([DMImports]![OrderNo])) AS OrderNoCount
FROM Address1CheckData, [DMImports]
WHERE (((StrConv([DMImports]![ShipToAdd1],1)) Like '*' & '#' & '*'));
thanks
Original:
It appears you are trying to get the user to Enter a Parameter. This not a wild card issue as I understand your set up.
It is not clear why you would do the StrConv ( to get upper case) since Access is case insensitive.
It would help readers if you could show us a sample record showing the data you would query with this SQL.
Update: After reviewing subsequent posts:
The OP wants to select those records where the ShipToAdd1 contains an octothorpe/hash/number sign or
possibly any number???
Last edited by orange; 12-05-2018 at 01:36 PM. Reason: clarification
@Shank - you have not looked at post #6.
What you say prompts for a parameter is not what was suggested in post #2 and is not demonstrated as being used in your post #7. If something isn't working, provide the code for what isn't working, not something that does not quite do what you want and a comment about what doesn't.
I should have elaborated in the original post. My effort is to clean up address1 field.
Example: 12345 Anywhere St #4
I want to search for any address1 field that contains the character #.
I hope that clarifies.
thanks
I understand that - but you have not done what I asked you to do
1. look at post 2
2. look at post 6
3. provide the code you are actually using that prompts you for a parameter
Sorry! It would probably help if I actually read what was offered instead of glossing over it.
This works as I wanted.
SELECT Nz(Count([DMImports]![OrderNo])) AS OrderNoCount
FROM [DMImports]
WHERE (((StrConv([DMImports]![ShipToAdd1],1)) Like '*[#]*'));
Thanks all!