Correctly using multiple WHERE in UPDATE macro

[jonnyboy101]

Hi all,

I am trying to add multiple WHERE criteria into my UPDATE macro, but I cant seem to get the AND syntax to work.

Private Sub update_test_Click()
  
 Dim dbs As Database
    Dim qdf As QueryDef
  
   CurrentDb.Execute "UPDATE timedata " _
        & "SET timestampstop = now() " _
        & "WHERE startstop = -1" And _
         "WHERE BGSnum = " & Forms!Mainform!BGS & "" And _
         "WHERE processdone = " & Forms!Mainform!p11 & ""
        
    End Sub

startstop is a checkbox, BGSnum and processdone are numbers.

Hope someone can help. Thanks in advance, Jon

 

[CJ_London]

you don't need all your wheres and your quotes are wrong, try

CurrentDb.Execute "UPDATE timedata " _
        & "SET timestampstop = now() " _
        & "WHERE startstop = -1 And " _
         " BGSnum = " & Forms!Mainform!BGS & " And " _
         " processdone = " & Forms!Mainform!p11

 

[jonnyboy101]

Its still kicking out a syntax error, "expected end of phrase" and highlighting " BGSnum = "

Still a little lost!?!?!

you don't need all your wheres and your quotes are wrong, try

CurrentDb.Execute "UPDATE timedata " _
        & "SET timestampstop = now() " _
        & "WHERE startstop = -1 And " _
         " BGSnum = " & Forms!Mainform!BGS & " And " _
         " processdone = " & Forms!Mainform!p11

 

[David R]

You're missing the connecting &'s on your last two lines.

 

[jonnyboy101]

David, you're probably totally correct but would you mind telling me where and how to change it? Your help is much appreciated.

On a side note I really hate having to ask what are probably simple questions but I cant find any good sources on VBA syntax. :banghead:

You're missing the connecting &'s on your last two lines.

 

[David R]

When you're starting out it can be REALLY helpful to build your query syntax in Query Design View, and then transfer the SQL to your VBA.

So if you think about it or try this, your SQL will probably be something like

UPDATE timedata SET timestampstop = now() WHERE startstop = -1 And BGSnum = Forms!Mainform!BGS And processdone = Forms!Mainform!p11

With me so far? Now put that together with the fact that in VBA, if you want to break the lines for readability, you need to close the quote ("), add an & so it knows to reconnect the lines, and then put a _ because... well I don't know, actually. But that underscore is the syntax tells Access that this isn't a 'full' line stop, you're continuing code on the next line. So in between every line of your formatted code, you need & _ (or _ & apparently works too, I just learned something) - the " is not necessary if your line ends/begins in a variable or field reference, like several of yours do.

Now see if you can work out what's wrong with your syntax. If not, post what you tried and we'll answer, but I want you to start to get more comfortable with the VBA syntax.

 

[jonnyboy101]

Thanks, that's fantastic because it works!

Is this written down anywhere in a concise form, you know give the man a net not a fish type thing.

 

[David R]

http://msdn.microsoft.com/en-us/library/office/aa189299(v=office.10).aspx seems to be pretty 'solid', if you'll forgive the pun.

 

[jonnyboy101]

Textbook, thanks