Item to append containing square brackets not appending

[wvandenberg]

Hi all,

I have a query that checks one table against another then appends any missing items. However, if a missing item contains square brackets (eg. Benzo[e]pyrene), then it is not recognized as "missing". Could anyone suggest how to fix this problem? Here is the SQL:

INSERT INTO tblAnalytes ( AnalyteName, ParentAnalyteID )
SELECT tblWizParamSpecs.pkAnalyteSpec, tblWizParamSpecs.pkAnalyteID
FROM tblWizParamSpecs LEFT JOIN tblAnalytes ON tblWizParamSpecs.pkAnalyteSpec = tblAnalytes.AnalyteName
WHERE (((tblAnalytes.AnalyteName) Is Null) AND ((tblWizParamSpecs.Action)<>4))
ORDER BY tblWizParamSpecs.pkAnalyteSpec;

Thanks in advance,
Wendy

 

[MajP]

I could not repeat the problem. I still linked. Maybe create a function

Public Function noBrackets(strAnalyte As String) As String
noBrackets = Replace(strAnalyte, "[", "")
noBrackets = Replace(noBrackets, "]", "")
End Function

Use the function to link the two tables:

SELECT A.Id, A.analyte, B.analyteFK, AS NoBAnalyte
FROM tblAnalyte AS A, tblAnalyte2 AS B
WHERE (((noBrackets([A].[Analyte]))=noBrackets(.[analyteFK])));

 

[wvandenberg]

Thanks, I will give it a try.

 

[Golom]

If you really need the square brackets to be there then you need to enclose them in square brackets because SQL interprets square brackets as potentially a pattern matching instruction, for example

SomeField LIKE '*[A-Z]*'

Which would be TRUE if SomeField contained an alphabetic character.

Here's a bit of code that I use to adjust for square brackets (and a few other things)

SearchString = Replace(Trim$(txtStockCode.Text), "'", "''")
SearchString = Replace(SearchString, "[", Chr$(6))
SearchString = Replace(SearchString, "]", Chr$(7))
SearchString = Replace(SearchString, Chr$(6), "[[]")
SearchString = Replace(SearchString, Chr$(7), "[]]")
SearchString = Replace(SearchString, "#", "[#]")
SearchString = Replace(SearchString, "?", "[?]")