VBA - SQL - As String Questions

[mickmullen]

Couple questions.



When a Dim is defined as String (Dim strCriteria As String)


and then I want to use the String in a SQL statement


What does the Quotation and ampersand mean here:


" & strCriteria & "


Context: WHERE ((" & strCriteria & ") AND(" & strCriteria2 & ")

 

[pbaldy]

You are concatenating literal text (whatever is between the quotes) with whatever is contained by the variable.

 

[plog]

Variable1 = "This is in the first variable"

Variable2 = "this is in the second variable"

Variable3 = Variable1 & ", " & Variable2 & " and together, along with this text, they create the value inside the third variable"

 

[tomasm]

When building an SQL string, form controls must be concatenated as strings but functions do not. Why?

In the examples below, textbox contains text and MyFunction returns a string.

Example with form control:

db.Execute "UPDATE table SET field = 'some text" & Forms!MyForm!textbox & "';", dbFailOnError

Example with function:

db.Execute "UPDATE table SET field = 'some text' & MyFunction();", dbFailOnError

 

[June7]

Because the SQL engine will process the SQL statement and "find" the function and evaluate. Just as if you had referenced the function in a query object. Semi-colon is actually not necessary but doesn't hurt.

Also, DoCmd.RunSQL will allow form control reference to be embedded and evaluated. CurrentDb.Execute doesn't. Function call works in both.

 

[isladogs]

If you are interested in the above SQL <=> VBA converter, please note that the versions in that thread date back to 2018.
The latest version is available as a free add-in from:

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

www.isladogs.co.uk

 

[cheekybuddha]

''Format Text Correctly for the SQL Statement strVar = Chr(34) & strVar & Chr(34) ' "

This will barf if strVar contains a double quote.