Access: Runtime error 3075 (missing operator) in SQL update query

Question

First time using Access and wanted to make an update query that uses a variable for its table name. Now, I've gotten myself into a web of nothing good. When I get to the part the SQL code is needed for, I get Runtime error 3075 - Missing operator in '(((" + enteredid + ".todayDate)=Format(Now()','""Short Date"")))' I've never coded in SQL, so I have no clue what operators are needed.

My code: strSQL = "UPDATE " + enteredid + " SET " + enteredid + ".signIn = Format(Now(),""Short Time"") WHERE (((" + enteredid + ".todayDate)=Format(Now()','""Short Date"")));"

You've got some random apostrophes in the last 'Format' function - see what happens without them :) — Tim
– Tim, Commented Sep 13, 2014 at 7:56

Gord Thompson · Accepted Answer · 2014-09-13 14:23:21Z

2

My suggestions:

You can avoid the whole Format() issue in the WHERE clause by using the Date() function instead of trying to extract just the date part of Now().
Since you are doing an UPDATE on a single table you can just use the field (column) names without the TableName. prefix.
To make your code more robust, enclose the table name in square brackets so it won't crash if the table name contains spaces or other "funny" characters.

So, the revised code would look more like this:

strSQL = _
        "UPDATE [" + enteredid + "] SET " + _
            "signIn = Format(Now(),""Short Time"") " + _
        "WHERE todayDate = Date()"

answered Sep 13, 2014 at 14:23

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

2 Comments

HansUp Over a year ago

Excellent suggestions. Since Gandalf is new to Access, he should learn how to inspect the UPDATE statement his code builds. Debug.Print strSQL would help. After running the code, Ctrl+g to view the statement text in the Immediate window. For troubleshooting, he can copy that statement text and paste it into SQL View of a new query.

Gandalf the White Over a year ago

Actually, the code that I was using was from a regular update query that I put into SQL View and copied. Weird how it didn't work...

Collectives™ on Stack Overflow

Access: Runtime error 3075 (missing operator) in SQL update query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related