Search code examples
sqlvbams-access

My Microsoft Access Append Query (VBA and SQL) works for a literal, but not for a variable

This is Microsoft Access, VBA and SQL related.

In VBA I make this SQL statement (see below) and run it. This correctly appends to testTable. (The field in testTable is Date/Time and it has format 'mm/dd/yyyy hh:nn:ss')

sql = "INSERT INTO testTable (DateOpen) VALUES ('12/01/2021 12:13:14')

However, when I do this (below), the code prompts me with a "Enter Parameter Value" for dateVar. Why? I just told it what dateVar was in the SQL.:

DIM dateVar as string

dateVar = "12/01/2021 12:13:14"

sql = "INSERT INTO testTable (DateOpen) VALUES (dateVar)

When I add quotes (see below) the code does not prompt me with an Enter Parameter Value msgbox. I get a msgbox that says You Are About To Append. But when it tries to append it fails with "Microsoft Access Can't Append the Records" due to a "type conversion error"

DIM dateVar as string

dateVar = "12/01/2021 12:13:14"

sql = "INSERT INTO testTable (DateOpen) VALUES ('dateVar')

Help Please??

Solution

  • Your concatenation of the dateVar variable into the SQL string needs to be fixed:

    Dim dateVar as string

dateVar = "12/01/2021 12:13:14"

sql = "INSERT INTO testTable (DateOpen) VALUES ('" & dateVar & "')"

    If you're having problem building your SQL then Debug.Print sql will let you see what's actually getting executed.