Help with Update query 1

[waubain]

I am having trouble with the syntax for an update query. I am getting a compile error: Syntax error. I have looked at examples but many are very complex and hard to follow.

I have a combobox (cboFullName) with a list of interview candidates with the row source

SELECT tblCandidates.FullName, tblCandidates.Interview FROM tblCandidates WHERE (((tblCandidates.Interview)=0)) ORDER BY tblCandidates.FullName;

After choosing a name and filling out the evaluation form I want clicking a cmd button to save and close the form I want to remove them from the list by running an update query and changing (tblCandidates.Interview) to -1 or 'true'

Here is my current update query. I have trouble understanding the quotes and & signs. I have Access VBA for Dummies, but does not explain the why's well.

CurrentDb.Execute ("UPDATE tblCandidates SET tblCandidates.Interview = -1 WHERE [tblCandidates.FullName] =' & Me!FullName'")

Probably not the best naming but Me!FullName is the control source to tblEvaluations.FullName on save.


You don't know what you don't know...

 

[MajP]

If writing sql code, I always save the string to a variable so I can check my work first and see if it looks correct

   dim strSql as string 
  'text in sql statements needs single quotes  ex. where fullname = 'John Smith'
   strSql = "UPDATE tblCandidates SET tblCandidates.Interview = -1 WHERE [tblCandidates.FullName] = '" & me.fullName & "'"
   debug.print strsql
   'It should read:  UPDATE tblCandidates SET tblCandidates.Interview = -1 WHERE [tblCandidates.FullName] = 'John Smith'
   CurrentDb.Execute (strSql)

 

[waubain]

MajP,

Thank you for the quick response and the programming advice.

Your solution worked perfect.

Waubain

You don't know what you don't know...

 

[PHV]

Just in case you have irish names (eg O'Neil):

strSql = "UPDATE tblCandidates SET Interview=-1 WHERE FullName='" & Replace(Me!fullName, "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886