Trying to use variables as values vba to sql

Question

Dim connection As New adodb.connection        
Dim sql As String
Dim years As Integer
years = 2011

So I've got some nice declared variables here. Now I'm going to show the statement that works perfectly, no problems:

sql = "insert into crimeData(reportYear) values(2011)"
connection.Execute sql

Notice I hardcoded in the year. Now, if I try:

sql = "insert into crimeData(reportYear) values(years)"
connection.Execute sql

Substituting the variable name in as a value, I get the error: No value given for one or more required parameters

Admittedly, I'm assuming there's got to be a simple syntax issue, or some meta with variables I'm missing. Please enlighten me. How can I correctly swap in a variable here (so I can use this programatically, as opposed to hard coding in values)

It's enclosed in a string so it really reads as it is: `insert into crimeData(reportYear) values(years)` you want to concatenate it with `&` — findwindow, Apr 07 '16 at 23:05
Try `sql = "insert into crimeData(reportYear) values(" & years & ")"` — findwindow, Apr 07 '16 at 23:06
What am I missing here: `sql = "insert into crimedata(reportYear, state) values(" & years & "," & states & ")"` — Joseph Erickson, Apr 07 '16 at 23:13
Well, what I'm saying is your response was correct. However, when I try it with multiple variables it breaks. Is the `","` the correct delimiter? Again, this is just an issue with escaping and ampersands. — Joseph Erickson, Apr 07 '16 at 23:23
Uh if you're passing arguments, don't you need space between `,`? so Perhaps try `, `? — findwindow, Apr 07 '16 at 23:24
Also you forgot to concatenate `crimedata(reportYear, state`? Edit: I am going home though so hope you figure it out ^_^; — findwindow, Apr 07 '16 at 23:25
If `state` is a string, you need to build the text qualifiers into your query = `...years & ",'" & states & "')"` — Comintern, Apr 08 '16 at 01:18

Gustav · Accepted Answer · 2019-12-31T17:32:53.667

If you have a more demanding need for concatenating SQL, you may benefit from this function:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
' 2019-12-31. Modified to compile in both 32- and 64-bit VBA.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    #If Win32 Then
        ' Serves only to make the code compile unmodified in 32-bit VBA
        ' which misses the constant VBA.vbLongLong.
        Const vbLongLong    As Integer = 20
    #End If

    Const SqlNull           As String = " Null"

    Dim Sql                 As String

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case vbLongLong         '   20  LongLong integer (Relevant in 64-bit VBA only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array. Ignored.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function

@ComputerVersteher: I don't think so, but if you could provide a full example where the output is not as expected, I'll certainly look into it. Feel free to use data from the Northwind sample database. — Gustav, Mar 29 '18 at 17:31
I've made a minor edit to avoid `LongLong` as a variable name since it's a keyword. I hope you don't mind, else you can always roll back. There's still the issue that arrays are combined types (e.g. `VarType(Array(0)) = vbVariant And vbArray '8204 Variant Array`) and fall into your `Case Else` while nothing falls into your `Case vbArray` afaik, but since we can't handle arrays anyway that's OK I guess. — Erik A, Dec 31 '19 at 14:31
@ErikA: In fact I do mind, as I at all times try to avoid prefixing variable names. That said, I'm aware that many love this left-over from the old naming style and I accept that. We all have our preferences. However, using `Dim LongLong As Integer` "reserves" it as a local variable name. You are right, that arrays will be ignored; I can't see a (simple) way to do differently, indeed not as an array item can hold yet an array. Happy New Year! — Gustav, Dec 31 '19 at 15:38
the `LongLong` name causes a compile issue on 64-bit builds of recent Office 365 versions (can't compile on 64-bits Access, version 1902, build 11328.20492 CTR). You can roll the edit back, but please consider picking a variable name that's not a keyword. Any naming convention will do since it's your post, as long as it'll compile. And of course, happy new year to you too! — Erik A, Dec 31 '19 at 15:45
@ErikA: Oh, you are right, no compile is bad. But I took another approach, as the core issue is the missing constant `VBA.vbLongLong` in 32-bit VBA. Much simpler, as you can see. Thanks for addressing the issue. — Gustav, Dec 31 '19 at 17:36
Re `Replace(Trim(Value), "'", "''")`: https://stackoverflow.com/q/15537368/11683 — GSerg, Jun 08 '21 at 08:31

Trying to use variables as values vba to sql

1 Answers1

Linked