Ampersand and other symbols in text

[MechEng]

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.

 

[Ken Snell \(MVP\)]

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

 

[Ken Snell \(MVP\)]

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>

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:

< snipped >

 

[MechEng]

Wow!... Exactly Perfectly Perfect... Thank you.

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>

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:

< snipped >

 

[Ken Snell \(MVP\)]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Wow!... Exactly Perfectly Perfect... Thank you.

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