Global Variables in SQL statement

Question

I have the following code in VBA:

Dim strSQL As String
strSQL = "UPDATE Workstations SET MID = newvalue WHERE MID = tempvalue"
DoCmd.RunSQL strSQL

newvalue and tempvalue are both global variables and have already been set values. Syntax wise, does this make sense? or am I missing quotation marks?

Dmitry Pavliv · Accepted Answer · 2014-03-21 16:47:13Z

9

Try this one:

If MID is number:

Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = " & newvalue & " WHERE [MID] = " & tempvalue
DoCmd.RunSQL strSQL

If MID is string (if newvalue/tempvalue doesn't contain single quote '):

Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = '" & newvalue & "' WHERE [MID] = '" & tempvalue & "'"
DoCmd.RunSQL strSQL

If MID is string (if newvalue/tempvalue contains single quote ' like newvalue="Mike's car"):

Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = '" & Replace(newvalue, "'", "''") & "' WHERE [MID] = '" & Replace(tempvalue, "'", "''") & "'"
DoCmd.RunSQL strSQL

If MID is date:

Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = #" & newvalue & "# WHERE [MID] = #" & tempvalue & "#"
DoCmd.RunSQL strSQL

Thanks to @HansUp for pointing out in comments, that

MID is the name of a function, so it would be safer to bracket or alias that field name in the SQL statement: SET [MID]

edited Mar 21, 2014 at 16:47

answered Mar 21, 2014 at 16:13

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

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

5 Comments

HansUp Over a year ago

MID is the name of a function, so it would be safer to bracket or alias that field name in the SQL statement: SET [MID] and WHERE [MID] Better yet, rename the field with a name which is not a reserved word.

Dmitry Pavliv Over a year ago

Thanks, @HansUp, that make sence. Updated my answer.

Chris Nielsen Over a year ago

What's going to happen if newvalue or tempvalue are strings with single quotes? You should also escape them, or better, switch to parameterized statements.

santaaimonce Over a year ago

thank you so much! really went in depth here. I probably should have specified MID is a number field. Thanks again!

Fionnuala Over a year ago

Rather than bracketing fields / columns, it is best to prefix with an alias UPDATE Workstations w SET w.MID , this scales nicely to other servers and you need never worry about reserved words again.

Collectives™ on Stack Overflow

Global Variables in SQL statement

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related