CODEWITHSUNDEEP
vbams-access
jm_lux8832
jm_lux8832

Reputation: 17

Passing SQL parameters containing spaces or hyphens

A) I'm collecting data from legacy applications; data elements are containing spaces and hyphens (CBR - CR - 22 - 2) or (CBR-CR-22-2)

B) In VBA, when building function and passing SQL parameters with those data elements, the code is generating errors (many!)

C) I narrowed down to identify that ALL errors were caused by the format of the data elements

D) The data element causing the problem is a PRIMARY KEY in about 30 tables and is generated by the legacy applications

(see code) I tried syntax:

A) strDataElm

B) [strDataElm]

C) & """ & strDataElm & """

strSourceSql = "SELECT " & strSourceFld & " FROM " & strSourceTbl & " WHERE " & strSourceFld & " = " & strDataElm

in the immediate window it gives

    SELECT NumeroCtl FROM tblLnkCtrl WHERE RISKID = CBR - CR - 22 - 2

In a ideal world, I'd like the strDataElm being passed as is i.e. CBR - CR - 22 2 or without spaces CBR-CR-22-2; removing the hyphens would bring issues because the data is a primary key and reused in other applications..

Upvotes: 0

Views: 730

Answers (1)

Van Ng
Van Ng

Reputation: 803

As it is mentioned it is better to use parametrized queries, but I know - way to concatenate string is too pleasurable.

Try this:

strSourceSql = "SELECT " & strSourceFld & " FROM " & strSourceTbl & " WHERE [" & strSourceFld & "] = [" & strDataElm & "]"

if you pass the field reference

And this, if you pass the value itself

strSourceSql = "SELECT " & strSourceFld & " FROM " & strSourceTbl & " WHERE " & strSourceFld & " = " & "'" &  strDataElm & "'"

Upvotes: 2

Related Questions