Trying to use VB to automate some queries. Running into what looks like a string problem

Question

Hi there

I'm using MS Access 2003 and I'm trying to execute a few queries at once using VB. When I write out the query in SQL it works fine, but when I try to do it in VB it asks me to "Enter Parameter Value" for DEPA, then DND (which are the first few letters of a two strings I have). Here's the code:

Option Compare Database

Public Sub RemoveDupelicateDepartments()

Dim oldID As String
Dim newID As String
Dim sqlStatement As String


oldID = "DND-01"
newID = "DEPA-04"

sqlStatement = "UPDATE [Clean student table] SET [HomeDepartment]=" & newID & " WHERE [HomeDepartment]=" & oldID & ";"

DoCmd.RunSQL sqlStatement & ""

End Sub

It looks to me as though it's taking in the string up to the - then nothing else. I dunno, that's why I'm asking lol. What should my code look like?

Answer 1

You probably want to insert quotes around the IDs.

Answer 2

Use (') character to set start and end of value

sqlStatement = "UPDATE [Clean student
table] SET [HomeDepartment]='" & newID
& "' WHERE [HomeDepartment]='" & oldID
& "';"