Update Query not working

[BobV]

Group:

I am trying to execute an Update Query using a variable for the Company
Name. The first sub below updates the CompanyOpen field of the MasterData
table correctly. (Note that it does not use a variable for the Company
Name.)

However, the second sub does not update the MasterData table. In the second
sub I am using a variable for the Company Name.

Can someone give me some insight as to why the second sub is not updating
the CompanyOpen field in the MasterData table? Any help will be greatly
appreciated.

Thanks,
BobV


Sub UpdateMasterData1() <-----This sub updates the table correctly
Dim db As Dao.Database
Dim CompanyName As String
Set db = CurrentDb
db.Execute "UPDATE MasterData SET MasterData.CompanyOpen = 1 WHERE
(((MasterData.Name)=""Sample Company 12-31-06""));"
End Sub


Sub UpdateMasterData2() <-----This sub does NOT update the table
correctly
Dim db As Dao.Database
Dim CompanyName As String
CompanyName = "Sample Company 12-31-06"
Set db = CurrentDb
db.Execute "UPDATE MasterData SET MasterData.CompanyOpen = 1 WHERE
(((MasterData.Name)="" & CompanyName & ""));"
End Sub

 

[Douglas J. Steele]

db.Execute "UPDATE MasterData SET MasterData.CompanyOpen = 1 " & _
"WHERE (((MasterData.Name)='" & CompanyName & "'));"

Note that I've used single quotes as the delimiter: exagerated for clarity,
that last line is
"WHERE (((MasterData.Name)= ' " & CompanyName & " ' ));"

If, however, there's a chance that CompanyName is going to include
apostrophe's ("Frank's Bait and Tackle"), use

db.Execute "UPDATE MasterData SET MasterData.CompanyOpen = 1 " & _
"WHERE (((MasterData.Name)=" & Chr$(34) & CompanyName & Chr$(34) "));"

Incidentally, just because Access puts in all of the parentheses (and the
semi-colon) doesn't mean you have to. The following would work just as well:

db.Execute "UPDATE MasterData SET MasterData.CompanyOpen = 1 " & _
"WHERE MasterData.Name=" & Chr$(34) & CompanyName & Chr$(34)

Also, consider using the dbFailOnError parameter in conjunction with the
Execute method: a trappable error message will be raised if there's a
problem, and that might help you pinpoint the problem:

db.Execute "UPDATE MasterData SET MasterData.CompanyOpen = 1 " & _
"WHERE MasterData.Name=" & Chr$(34) & CompanyName & Chr$(34),
dbFailOnError

 

[Ken Snell \(MVP\)]

Try this:

Sub UpdateMasterData2() <-----This sub does NOT update the table
correctly
Dim db As Dao.Database
Dim CompanyName As String
CompanyName = "Sample Company 12-31-06"
Set db = CurrentDb
db.Execute "UPDATE MasterData SET MasterData.CompanyOpen = 1 WHERE
(((MasterData.Name)='" & CompanyName & "'));"
End Sub


Change involves using the ' character to delimit the text value that is
being concatentated into the query string.

 

[BobV]

Thank you Doug for your prompt response. I appreciate it very much!

BobV

 

[BobV]

Thank you Ken for your prompt response. I appreciate it very much!

BobV