CODEWITHSUNDEEP
sqlvbams-access
user8603758
user8603758

Reputation:

pass string variable without quotes in query vba

Dim StoreNoToUpdate As String
Dim Marsha As String

StoreNoToUpdate = "ABCXYZ123"

Marsha = "hi"

db.Execute "Update TblLodgingReport set [MarshaCode]=  'Marsha'  where [Store Number ID]= 'ABCXYZ123'"

I am trying to update "hi" at "ABCXYZ123" location. but its not pasting "hi", instead its pasting "Marsha".

Upvotes: 0

Views: 1432

Answers (3)

Kostas K.
Kostas K.

Reputation: 8518

You need to get away from SQL concatenation and start using parameters.

Query with parameters:

PARAMETERS [prmMarshaCode] Text (50), [prmStoreNoToUpdate] Text (50);
UPDATE TblLodgingReport SET [MarshaCode] = [prmMarshaCode]
WHERE [Store Number ID] = [prmStoreNoToUpdate];

Calling the above query in VBA:

With CurrentDb().QueryDefs("qryName")
    .Parameters("[prmMarshaCode]").Value = Marsha 
    .Parameters("[prmStoreNoToUpdate]").Value = StoreNoToUpdate 
    .Execute dbFailOnError
End With

Upvotes: 5

warner_sc
warner_sc

Reputation: 848

Try:

Dim StoreNoToUpdate As String
Dim Marsha As String

StoreNoToUpdate = "ABCXYZ123"

Marsha = "hi"

Db.Execute "Update TblLodgingReport set [MarshaCode]='" & Marsha & "'where [Store Number ID]= 'ABCXYZ123'"

Upvotes: 0

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

Try changing the following line

db.Execute "Update TblLodgingReport set [MarshaCode]='" & Marsha & "' where [Store Number ID]='" & StoreNoToUpdate & "'"

Its because 'Marsha' means you are literally sending Marsha as string and not using the variable.

Upvotes: 0

Related Questions