character #

  • Thread starter Thread starter Michael Wong
  • Start date Start date
M

Michael Wong

I have a textbox TxtFilter that I use to filter a datasheet using the
following query:

"SELECT * from TProduct WHERE TProduct.ArtNo LIKE '*" & TxtFilter & "*'"

However, I cannot include character # in the TxtFilter because it is used to
delimiter dates in a query.

What can I do?
 
The # character must be surrounded by [ ] characters if you want to include
it as a literal character.

I overcome this by using a public function that does this (if you have
ACCESS 2000 or ACCESS 2002) when it's called.

Put this function in a regular module:

' *********************************
' ** Function PoundAddBrackets **
' *********************************

Public Function PoundAddBrackets(ByVal xstrReplaceStringValue) As String
'***THIS FUNCTION SURROUNDS ONE "#" CHARACTER WITH [ AND ] CHARACTERS
'***IN A TEXT STRING.

' xstrReplaceStringValue is string variable that contains the text string
that
' needs to be converted
On Error GoTo Err_PoundAddBrackets
PoundAddBrackets = Replace(Nz(xstrReplaceStringValue, ""), "#", "[#]",
1, -1, vbTextCompare)
Exit Function

Err_PoundAddBrackets:
PoundAddBrackets = xstrReplaceStringValue
Resume Next
End Function


Then change your SQL to this:

"SELECT * from TProduct WHERE TProduct.ArtNo LIKE '*" &
PoundAddBrackets(TxtFilter) & "*'"
 
Michael said:
I have a textbox TxtFilter that I use to filter a datasheet using the
following query:

"SELECT * from TProduct WHERE TProduct.ArtNo LIKE '*" & TxtFilter & "*'"

However, I cannot include character # in the TxtFilter because it is used to
delimiter dates in a query.

What can I do?
Click to expand...

While it's true that # is the date deliminator, it won't be
interpreted that way in this context.

Since your text box is supplying part of the pattern to the
LIKE operator, you have the benefit(?) of being able to use
wildcard characters in the string in the text box. # is a
wildcard character that matches a decimal digit. There are
other wildcards such as *, ? and [ ] See Wildcard
Characters in Help for details.

If you want to match a character that is also a wildcard,
then you should enclose it in [ ]. For example, if you want
to filter for records that conatin XY#12 within a field,
then you should enter XY[#]12 in the text box.

This kind of thing is both a curse and a blessing. While it
can be very confusing to users, it does provide them with a
very powerful search feature.

If you want to eliminate this kind of thing, then you'll
have to check the string from the text box and replace any
wildcards with whatever it takes to neutralize them. Iin
your example, you could use:

. . . TProduct.ArtNo LIKE '*" &
Replace(TxtFilter,"#","[#]") & "*'"
 
Thanks.

Ken Snell said:
The # character must be surrounded by [ ] characters if you want to include
it as a literal character.

I overcome this by using a public function that does this (if you have
ACCESS 2000 or ACCESS 2002) when it's called.

Put this function in a regular module:

' *********************************
' ** Function PoundAddBrackets **
' *********************************

Public Function PoundAddBrackets(ByVal xstrReplaceStringValue) As String
'***THIS FUNCTION SURROUNDS ONE "#" CHARACTER WITH [ AND ] CHARACTERS
'***IN A TEXT STRING.

' xstrReplaceStringValue is string variable that contains the text string
that
' needs to be converted
On Error GoTo Err_PoundAddBrackets
PoundAddBrackets = Replace(Nz(xstrReplaceStringValue, ""), "#", "[#]",
1, -1, vbTextCompare)
Exit Function

Err_PoundAddBrackets:
PoundAddBrackets = xstrReplaceStringValue
Resume Next
End Function


Then change your SQL to this:

"SELECT * from TProduct WHERE TProduct.ArtNo LIKE '*" &
PoundAddBrackets(TxtFilter) & "*'"

--
Ken Snell
<MS ACCESS MVP>

Michael Wong said:
I have a textbox TxtFilter that I use to filter a datasheet using the
following query:

"SELECT * from TProduct WHERE TProduct.ArtNo LIKE '*" & TxtFilter & "*'"

However, I cannot include character # in the TxtFilter because it is
Click to expand...
used
to
delimiter dates in a query.

What can I do?
Click to expand...
Click to expand...
 
I haven't seen this this way.
Thank you very much.

Marshall Barton said:
Michael said:
I have a textbox TxtFilter that I use to filter a datasheet using the
following query:

"SELECT * from TProduct WHERE TProduct.ArtNo LIKE '*" & TxtFilter & "*'"

However, I cannot include character # in the TxtFilter because it is used to
delimiter dates in a query.

What can I do?
Click to expand...

While it's true that # is the date deliminator, it won't be
interpreted that way in this context.

Since your text box is supplying part of the pattern to the
LIKE operator, you have the benefit(?) of being able to use
wildcard characters in the string in the text box. # is a
wildcard character that matches a decimal digit. There are
other wildcards such as *, ? and [ ] See Wildcard
Characters in Help for details.

If you want to match a character that is also a wildcard,
then you should enclose it in [ ]. For example, if you want
to filter for records that conatin XY#12 within a field,
then you should enter XY[#]12 in the text box.

This kind of thing is both a curse and a blessing. While it
can be very confusing to users, it does provide them with a
very powerful search feature.

If you want to eliminate this kind of thing, then you'll
have to check the string from the text box and replace any
wildcards with whatever it takes to neutralize them. Iin
your example, you could use:

. . . TProduct.ArtNo LIKE '*" &
Replace(TxtFilter,"#","[#]") & "*'"
Click to expand...
 
Back
Top