Ampersand and other symbols in text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table where the users would like to use the ampersand and “ ‘ “.
For example; in a field named "Company", they would like to be able to use
company names like "Joe & Max's garage". Using the replace command, I can
work with the " ' ", but the "&" is causing real problems in both queries and
code.

Is there any way to let them keep the symbols? You help is appreciated in
advance.
 
You can use the Replace function to delimit the ampersand character with [ ]
characters, and then you can handle the text string in VBA and query.

I use a function as a wrapper for whenever I am concatenating strings into
SQL query statements etc. in code:

strSQL = "SELECT * FROM TableName WHERE FieldName ='" & _
ZZZZAddBrackets(Me.NameOfControl.Value) & "';"

The function (and the functions that it calls) are listed below.


Public Function ZZZZAddBrackets(ByVal xstrReplaceStringValue) As String
' *** THIS FUNCTION SURROUNDS "[", "*", "#", AND "?" CHARACTERS WITH [ AND ]
CHARACTERS
' *** IN A TEXT STRING. ITS USE IS FOR TEXT STRINGS THAT ARE USED AS
PARAMETERS
' *** IN VARIOUS QUERIES' "WHERE" STATEMENTS WHEN "LIKE" IS USED INSTEAD OF
"=".

' *** THIS FUNCTION SHOULD BE USED WHEN "LIKE" IS USED TO MATCH A TEXT
STRING.
' *** THIS FUNCTION IS NOT TO BE USED WHEN "=" OR "<>" IS USED TO MATCH A
TEXT STRING.


' xstrReplaceStringValue is variant (string) variable that contains the
' text string that needs to be converted
On Error GoTo Err_ZZZZAddBrackets

xstrReplaceStringValue = LeftBracketAddBrackets(xstrReplaceStringValue)
xstrReplaceStringValue = AsteriskAddBrackets(xstrReplaceStringValue)
xstrReplaceStringValue = PoundAddBrackets(xstrReplaceStringValue)
xstrReplaceStringValue = QuestionAddBrackets(xstrReplaceStringValue)

ZZZZAddBrackets = xstrReplaceStringValue
Err.Clear
Exit Function

Err_ZZZZAddBrackets:
ZZZZAddBrackets = xstrReplaceStringValue
Resume Next
End Function




' *************************************
' ** Function AsteriskAddBrackets **
' *************************************

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

' xstrReplaceStringValue is variant (string) variable that contains the
' text string that needs to be converted
On Error GoTo Err_AsteriskAddBrackets
AsteriskAddBrackets = Replace(Nz(xstrReplaceStringValue, ""), "*", "[*]",
1, -1, vbTextCompare)
Err.Clear
Exit Function

Err_AsteriskAddBrackets:
AsteriskAddBrackets = xstrReplaceStringValue
Resume Next
End Function



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

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

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

Err_PoundAddBrackets:
PoundAddBrackets = xstrReplaceStringValue
Resume Next
End Function



' *************************************
' ** Function QuestionAddBrackets **
' *************************************

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

' xstrReplaceStringValue is variant (string) variable that contains the
' text string that needs to be converted
On Error GoTo Err_QuestionAddBrackets
QuestionAddBrackets = Replace(Nz(xstrReplaceStringValue, ""), "?", "[?]", _
1, -1, vbTextCompare)
Err.Clear
Exit Function

Err_QuestionAddBrackets:
QuestionAddBrackets = xstrReplaceStringValue
Resume Next
End Function



' *************************************
' ** Function LeftBracketAddBrackets **
' *************************************

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

' xstrReplaceStringValue is variant (string) variable that contains the
' text string that needs to be converted
On Error GoTo Err_LeftBracketAddBrackets
LeftBracketAddBrackets = Replace(Nz(xstrReplaceStringValue, ""), "[", "[[]",
_
1, -1, vbTextCompare)
Err.Clear
Exit Function

Err_LeftBracketAddBrackets:
LeftBracketAddBrackets = xstrReplaceStringValue
Resume Next
End Function
 
I goofed in the VBA step example. And, I've rewritten the function so that
you can have just a single function to do it all.

strSQL = "SELECT * FROM TableName WHERE FieldName Like '" & _
SQLAddBrackets(Me.NameOfControl.Value) & "';"



Public Function SQLAddBrackets(ByVal xstrReplaceStringValue) As String
' *** THIS FUNCTION SURROUNDS "[", "*", "#", AND "?"
' *** CHARACTERS WITH [ AND ] CHARACTERS
' *** IN A TEXT STRING. ITS USE IS FOR TEXT STRINGS THAT
' *** ARE USED AS PARAMETERS IN VARIOUS QUERIES' "WHERE"
' *** STATEMENTS WHEN "LIKE" IS USED INSTEAD OF "=".

' *** THIS FUNCTION SHOULD BE USED WHEN "LIKE" IS USED
' *** TO MATCH A TEXT STRING.
' *** THIS FUNCTION IS NOT TO BE USED WHEN "=" OR "<>" IS
' *** USED TO MATCH A TEXT STRING.


' xstrReplaceStringValue is variant (string) variable that contains the
' text string that needs to be converted
On Error GoTo Err_ZZZZAddBrackets

xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"[", "[[]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"*", "[*]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"#", "[#]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"?", "[?]", 1, -1, vbTextCompare)

SQLAddBrackets = xstrReplaceStringValue
Err.Clear
Exit Function

Err_ZZZZAddBrackets:
SQLAddBrackets = xstrReplaceStringValue
Resume Next
End Function

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
You can use the Replace function to delimit the ampersand character with
[ ] characters, and then you can handle the text string in VBA and query.

I use a function as a wrapper for whenever I am concatenating strings into
SQL query statements etc. in code:
Click to expand...


< snipped >
 
Wow!... Exactly Perfectly Perfect... Thank you.

Ken Snell (MVP) said:
I goofed in the VBA step example. And, I've rewritten the function so that
you can have just a single function to do it all.

strSQL = "SELECT * FROM TableName WHERE FieldName Like '" & _
SQLAddBrackets(Me.NameOfControl.Value) & "';"



Public Function SQLAddBrackets(ByVal xstrReplaceStringValue) As String
' *** THIS FUNCTION SURROUNDS "[", "*", "#", AND "?"
' *** CHARACTERS WITH [ AND ] CHARACTERS
' *** IN A TEXT STRING. ITS USE IS FOR TEXT STRINGS THAT
' *** ARE USED AS PARAMETERS IN VARIOUS QUERIES' "WHERE"
' *** STATEMENTS WHEN "LIKE" IS USED INSTEAD OF "=".

' *** THIS FUNCTION SHOULD BE USED WHEN "LIKE" IS USED
' *** TO MATCH A TEXT STRING.
' *** THIS FUNCTION IS NOT TO BE USED WHEN "=" OR "<>" IS
' *** USED TO MATCH A TEXT STRING.


' xstrReplaceStringValue is variant (string) variable that contains the
' text string that needs to be converted
On Error GoTo Err_ZZZZAddBrackets

xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"[", "[[]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"*", "[*]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"#", "[#]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"?", "[?]", 1, -1, vbTextCompare)

SQLAddBrackets = xstrReplaceStringValue
Err.Clear
Exit Function

Err_ZZZZAddBrackets:
SQLAddBrackets = xstrReplaceStringValue
Resume Next
End Function

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
You can use the Replace function to delimit the ampersand character with
[ ] characters, and then you can handle the text string in VBA and query.

I use a function as a wrapper for whenever I am concatenating strings into
SQL query statements etc. in code:
Click to expand...


< snipped >
Click to expand...
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>


MechEng said:
Wow!... Exactly Perfectly Perfect... Thank you.

Ken Snell (MVP) said:
I goofed in the VBA step example. And, I've rewritten the function so
that
you can have just a single function to do it all.

strSQL = "SELECT * FROM TableName WHERE FieldName Like '" & _
SQLAddBrackets(Me.NameOfControl.Value) & "';"



Public Function SQLAddBrackets(ByVal xstrReplaceStringValue) As String
' *** THIS FUNCTION SURROUNDS "[", "*", "#", AND "?"
' *** CHARACTERS WITH [ AND ] CHARACTERS
' *** IN A TEXT STRING. ITS USE IS FOR TEXT STRINGS THAT
' *** ARE USED AS PARAMETERS IN VARIOUS QUERIES' "WHERE"
' *** STATEMENTS WHEN "LIKE" IS USED INSTEAD OF "=".

' *** THIS FUNCTION SHOULD BE USED WHEN "LIKE" IS USED
' *** TO MATCH A TEXT STRING.
' *** THIS FUNCTION IS NOT TO BE USED WHEN "=" OR "<>" IS
' *** USED TO MATCH A TEXT STRING.


' xstrReplaceStringValue is variant (string) variable that contains the
' text string that needs to be converted
On Error GoTo Err_ZZZZAddBrackets

xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"[", "[[]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"*", "[*]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"#", "[#]", 1, -1, vbTextCompare)
xstrReplaceStringValue = Replace(Nz(xstrReplaceStringValue, ""), _
"?", "[?]", 1, -1, vbTextCompare)

SQLAddBrackets = xstrReplaceStringValue
Err.Clear
Exit Function

Err_ZZZZAddBrackets:
SQLAddBrackets = xstrReplaceStringValue
Resume Next
End Function
Click to expand...
Click to expand...
 
Back
Top