MS Access 2013 SQL Type mismatch error

Question

I have a table called test with a column of type number (Long Integer) and name CHANGEME. This column has values ranging from null to large integers. I'm attempting to select all null values and update them to 0. I'm doing this in VBA code.

SQL = "UPDATE test SET test.CHANGEME = 0 WHERE test.CHANGEME Is Null" DoCmd.RunCommand SQL

This produces the type mismatch error.

However if I create a query in the user interface and copy and paste the SQL statement and add a ; at the end and remove the "'s it works perfectly.

Any thoughts?

Simon1979 · Accepted Answer · 2015-03-02 21:24:21Z

I'm not sure that is how you use DoCmd.RunCommand; it probably expects a constant, not a string.

Instead I prefer to use something like:

Dim strSQL as String strSQL = "UPDATE test SET test.CHANGEME = 0 WHERE test.CHANGEME Is Null" With CurrentDb .Execute strSQL, dbFailOnError Msgbox .RecordsAffected & " records were updated" End With

The dbFailOnError is important; if you omit it and there is a syntax error etc you won't be alerted. Using .Execute as a method is good for in your code as it will not prompt you to confirm your action every time you run it.

Hope this helps.

You nailed it! I'll use that method from now on. Thank you very much

Collectives™ on Stack Overflow

MS Access 2013 SQL Type mismatch error

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related