error when executing a dynamic sql string

Question

I am getting an error when attempting to execue a dynamic sql string in MS Access (I am using VBA to write the code).

Error:

Run-time error '3075': Syntax error (missing operator) in query expression "11/8/2013' FROM tbl_sample'.

Here is my code:

Sub UpdateAsOfDate()

    Dim AsOfDate As String
    AsOfDate = Form_DateForm.txt_AsOfDate.Value
    AsOfDate = Format(CDate(AsOfDate))

    Dim dbs As Database

    Set dbs = OpenDatabase("C:\database.mdb")

            Dim strSQL As String
            strSQL = " UPDATE tbl_sample " _
                    & "SET tbl_sample.As_of_Date = '" _
                    & AsOfDate _
                    & "' " _
                    & "FROM tbl_sample " _
                    & "WHERE tbl_sample.As_of_Date IS NULL ;"


        dbs.Execute strSQL

    dbs.Close 
End Sub

I piped the strSQL to a MsgBox so I could see the finished SQL string, and it looks like it would run without error. What's going on?

IIRC, Access likes to have it's date literals quoted with "#" instead of apostrophes. Like ` ... = #11/8/2013#`. — RBarryYoung
– RBarryYoung, Commented Nov 12, 2013 at 22:23

shf301 · Accepted Answer · 2013-11-12 22:24:11Z

3

Get rid of & "FROM tbl_sample " _. A from clause isn't valid in your update statement.

answered Nov 12, 2013 at 22:24

shf301

31.5k2 gold badges57 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2013-11-12 22:32:37Z

Include the # date delimiters into your AsOfDate string.

AsOfDate = Format(Form_DateForm.txt_AsOfDate.value, "\#yyyy-m-d\#")

Then use that pre-formatted AsOfDate string when you build the UPDATE statement.

strSQL = "UPDATE tbl_sample AS t" & vbCrLf & _
    "SET t.As_of_Date = " & AsOfDate & vbCrLf & _
    "WHERE t.As_of_Date IS NULL;"

Also notice I discarded the FROM clause because it's not valid in an Access UPDATE, as the others have mentioned.

Or you could convert to a parameter query, and avoid dealing with the date delimiters.

Gord Thompson · Accepted Answer · 2013-11-12 23:14:00Z

You really should be using a parameterized query because

they're safer,
you don't have to mess with delimiters for date and text values,
you don't have to worry about escaping quotes within text values, and
they handle dates properly so your code doesn't mangle dates on machines set to dd-mm-yyyy format.

In your case you would use something like this:

Sub UpdateAsOfDate()
    Dim db As DAO.Database, qdf As DAO.QueryDef
    Dim AsOfDate As Date

    AsOfDate = DateSerial(1991, 1, 1)  ' test data

    Set db = OpenDatabase("C:\Users\Public\Database1.accdb")
    Set qdf = db.CreateQueryDef("", _
            "PARAMETERS paramAsOfDate DateTime; " & _
            "UPDATE tbl_sample SET As_of_Date = [paramAsOfDate] " & _
            "WHERE As_of_Date IS NULL")
    qdf!paramAsOfDate = AsOfDate
    qdf.Execute
    Set qdf = Nothing
    db.Close
    Set db = Nothing
End Sub

db9dreamer · Accepted Answer · 2013-11-12 22:32:29Z

0

try:-

    strSQL = " UPDATE t " _
            & "SET t.As_of_Date = '" _
            & AsOfDate _
            & "' " _
            & "FROM tbl_sample t " _
            & "WHERE t.As_of_Date IS NULL ;"

The problem may be that your Access driver is misunderstanding the alias you are using (because it exactly matches a table name).

answered Nov 12, 2013 at 22:32

db9dreamer

1,7152 gold badges20 silver badges27 bronze badges

1 Comment

db9dreamer Over a year ago

Unfortunately, as others have pointed out, Access doesn't appear to allow this syntax for the update statement. Today, as usual, I learned something...

Collectives™ on Stack Overflow

error when executing a dynamic sql string

4 Answers 4

Comments

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Related