CurrentDb.Execute - multiple where criteria

[branston]

Hi,

Im trying to have more than where criteria in a CurrentDb.Execute statement. I was wondering if you could tell me if i needed a 'where' for every 'and' (eg where x and where y). This is what i have at the moment:

CurrentDb.Execute "Update TblPvnJustSTORE set TblPvnJustSTORE.Justification = '" & _
strAtt & "' Where TblPvnJustSTORE.ProjectNo = " & StrProj & _
" And TblPvnJustSTORE.PvnNo = " & StrPvn & _
" And TblPvnJustSTORE.Rev = " & strRev

Thank you

 

[HiTechCoach]

You only use one Where clause. You can mage multiple criteria, but you will only have one "WHERE".

You SQL looks correct assuming all the variable are numeric data int he "Where" section.

**** While testing, be sure to make a backup of the database with the tables before you run your SQL. ***

 

[branston]

great, thank you... was kind of hoping that was what was wrong though!!

 

[DCrake]

CurrentDb.Execute "Update TblPvnJustSTORE Set Justification = '" & _
strAtt & "' Where ProjectNo = " & StrProj & _
" And PvnNo = " & StrPvn & _
" And Rev = " & strRev & " " & dbFailOnError

When updating one table you do not really need to refer to the table for each field. Also you can employ the dbFailOnError to roll back any updates should an error occur.

David

 

[namliam]

Try and make sql readable by doing it like so:

mySQL = ""
mySQL = mySQL & " Update TblPvnJustSTORE  "
mySQL = mySQL & " set TblPvnJustSTORE.Justification = '" & 
strAtt & "' "
mySQL = mySQL & " Where TblPvnJustSTORE.ProjectNo = " & StrProj 
mySQL = mySQL & "   And TblPvnJustSTORE.PvnNo = " & StrPvn 
mySQL = mySQL & "   And TblPvnJustSTORE.Rev = " & strRev

Assuming pvnno rev and projectno are numbers this sql is correct

Edit: Lots of xposts I am so slow

 

[branston]

Thanks DCrake, thats a very handy hint!!

 

[branston]

namliam, Thanks for the suggestion - I keep meaning to set out my SQL like this, but i find it easier to read just keeping it neat and all in 1. Personal preference i guess.
Ill give your way a go for the next few times though and see if it works for me!