sql where string contains a pound (#)

  • Thread starter Thread starter Krista H
  • Start date Start date
K

Krista H

MS ACCESS 2007, Vista
I have a search form that allows a user to search orders by company name.
One of the company's contains a pound sign (ex. MY PUD CO #1). When the # is
included I get "no search results found". I am assuming i need some special
syntax to process the # but cannot find anything for the life of me (it is
hard to search anything using a #, including this forum)

I have tried:
MyCo = Replace(MyCo, "#", "##")
MyCo = Replace(MyCo, "#", "\#")

I am at the point of dropping the # using MyCo = Rtrim(MyCo, "#") to get a
result but don't think it is the proper thing to do because it will pick up
more than they requested..... but that is better than saying "No Records
Found"

Sorry if this is a duplicate on the board, I have searched the internet and
this forum and have not found any information (I keep getting "no records
match that search" even when I spell out pound).

Thanks in advance :)
 
sanity check things by making a plain old query with vanilla query design,
and using that name as the criteria.....it should definitely work; if not -
the issue isn't the # sign within the syntax of your sql statement;
 
MS ACCESS 2007, Vista
I have a search form that allows a user to search orders by company name.
One of the company's contains a pound sign (ex. MY PUD CO #1). When the # is
included I get "no search results found". I am assuming i need some special
syntax to process the # but cannot find anything for the life of me (it is
hard to search anything using a #, including this forum)

I have tried:
MyCo = Replace(MyCo, "#", "##")
MyCo = Replace(MyCo, "#", "\#")

I am at the point of dropping the # using MyCo = Rtrim(MyCo, "#") to get a
result but don't think it is the proper thing to do because it will pick up
more than they requested..... but that is better than saying "No Records
Found"

Sorry if this is a duplicate on the board, I have searched the internet and
this forum and have not found any information (I keep getting "no records
match that search" even when I spell out pound).

Thanks in advance :)
Click to expand...

Well, of course the text strings "#" and "pound" will not match.

The problem is that # is a wildcard matching any numeric digit. To find a text
string containing an octothorpe (£ is a pound sign <g>), use

Replace(MyCo, "#", "[#]")

to construct the criterion.

If that doesn't help, please post the SQL view of your query.
 
I will give it a try. I guess I assumed it was the pound sign because if I
type "MY PUD CO" in the search query it works fine.
 
I tried the vanilla query first, worked when I used = "MY PUD CO #1" but did
not work with Like "*MY PUD CO #1" in the query, which I thought was weird.

The brackets did do the trick.

Thanks much.

John W. Vinson said:
MS ACCESS 2007, Vista
I have a search form that allows a user to search orders by company name.
One of the company's contains a pound sign (ex. MY PUD CO #1). When the # is
included I get "no search results found". I am assuming i need some special
syntax to process the # but cannot find anything for the life of me (it is
hard to search anything using a #, including this forum)

I have tried:
MyCo = Replace(MyCo, "#", "##")
MyCo = Replace(MyCo, "#", "\#")

I am at the point of dropping the # using MyCo = Rtrim(MyCo, "#") to get a
result but don't think it is the proper thing to do because it will pick up
more than they requested..... but that is better than saying "No Records
Found"

Sorry if this is a duplicate on the board, I have searched the internet and
this forum and have not found any information (I keep getting "no records
match that search" even when I spell out pound).

Thanks in advance :)
Click to expand...

Well, of course the text strings "#" and "pound" will not match.

The problem is that # is a wildcard matching any numeric digit. To find a text
string containing an octothorpe (£ is a pound sign <g>), use

Replace(MyCo, "#", "[#]")

to construct the criterion.

If that doesn't help, please post the SQL view of your query.
Click to expand...
 
Back
Top