0

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..

braX
  • 11,506
  • 5
  • 20
  • 33
jm_lux8832
  • 17
  • 3
  • 1
    Start by using actual parameters, and not concatenating strings. See [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/q/49509615/7296893) – Erik A Jun 07 '19 at 08:56
  • 1
    If data type of strSourceFld is string then you should put it in single quotes: ... RISKID = 'CBR - CR - 22 - 2' – viilpe Jun 07 '19 at 08:56

1 Answers1

2

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 & "'"
Van Ng
  • 773
  • 1
  • 7
  • 17
  • Very true... using " ' " did solve the issue... I promise to try parametrized queries; I the present case, I would have to build as many queries as fields in the initial tables.... – jm_lux8832 Jun 07 '19 at 09:19
  • 1
    If you must, then please use Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271) when concatenating variables with SQL. @jm_lux8832 – Andre Jun 07 '19 at 09:54
  • Thanks to All. I made good use of ALL the above including the lovely piece of wonder found at http://allenbrowne.com/func-06.html – jm_lux8832 Jun 07 '19 at 14:27