If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"#" from combo box causing problem with query?
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. |
#2
|
|||
|
|||
"#" from combo box causing problem with query?
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 "Jason Gyetko" wrote in message ... 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. |
#3
|
|||
|
|||
"#" from combo box causing problem with query?
Thanks, I used the function and it works great!!
"Ken Snell" wrote in message ... 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 "Jason Gyetko" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help with cascading combos | Tom | Using Forms | 19 | July 1st, 2004 11:11 PM |
Query reporting problem | Andy | Running & Setting Up Queries | 1 | June 4th, 2004 10:44 PM |
Query Problem | Niall | Running & Setting Up Queries | 1 | June 3rd, 2004 01:24 PM |
Combo Box Problem | Charles | Using Forms | 0 | May 28th, 2004 09:15 PM |
Query problem | MBison80 | New Users | 2 | May 5th, 2004 06:00 PM |