"#" from combo box causing problem with query?

[Jason Gyetko]

I've got a form with with a combo box on it. The combo box is being
populated with data that has a "#" sign in it.

Ex: TONSIL TRAY #02

I'm then running a query that uses the combo box value for its criteria.

Ex: Like [forms]![MyForm]![cboComboBox]

This works fine for values that don NOT have a "#" in them, but nothing is
returned for the values that do. Does anyone know how to get around this?
Thanks.

 

[Ken Snell]

Two ways to do this:

(1) change your control source to this:
= [forms]![MyForm]![cboComboBox]

The above should also eliminate similar problems that would result from
embedded *, !, etc. characters that may cause same type of errors.


(2) You need to surround the # character with [ ] characters in the text
string.

Put this function in a regular module:

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)
Err.Clear
Exit Function

Err_PoundAddBrackets:
PoundAddBrackets = xstrReplaceStringValue
Resume Next
End Function


Then, use this function in your control source:

Like PoundAddBrackets([forms]![MyForm]![cboComboBox])

 

[Jason Gyetko]

Thanks, I used the function and it works great!!

Two ways to do this:

(1) change your control source to this:
= [forms]![MyForm]![cboComboBox]

The above should also eliminate similar problems that would result from
embedded *, !, etc. characters that may cause same type of errors.


(2) You need to surround the # character with [ ] characters in the text
string.

Put this function in a regular module:

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)
Err.Clear
Exit Function

Err_PoundAddBrackets:
PoundAddBrackets = xstrReplaceStringValue
Resume Next
End Function


Then, use this function in your control source:

Like PoundAddBrackets([forms]![MyForm]![cboComboBox])


--

Ken Snell
<MS ACCESS MVP>

I've got a form with with a combo box on it. The combo box is being
populated with data that has a "#" sign in it.

Ex: TONSIL TRAY #02

I'm then running a query that uses the combo box value for its criteria.

Ex: Like [forms]![MyForm]![cboComboBox]

This works fine for values that don NOT have a "#" in them, but nothing is
returned for the values that do. Does anyone know how to get around this?
Thanks.