RunSQL w multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use the following RunSQL command to update a field in a table based on a
single criteria. I would like to modifiy this command for multiple criteria
but I'm having trouble with the syntax.

DoCmd.RunSQL "UPDATE[Table1] set[Comments] = Null WHERE [Stock Number] = '"
& Me.Units_Field & "'"

I want to modify the comments where (1) [Stock Number = me.units_field, (2)
"In Use" = True, and (3) "Out of Service" = True.

I would also like to update 2 other fields based on the same criteria. do I
have to run separate Run SQL commands or can the same command be modified to
"set" 3 separate fields based on the same multiple criteria?

Thanks for the help...........
 
DoCmd.RunSQL "UPDATE[Table1] set[Comments] = Null, " & _
"[Field1] = 'Something Else', [Field2] = 3 " & _
" WHERE [Stock Number] = '" & _
Me.Units_Field & "' And " & _
"[In Use] = True And [Out Of Service] = True"

Incidentally, if you use

CurrentDb.Execute "UPDATE[Table1] set[Comments] = Null, " & _
"[Field1] = 'Something Else', [Field2] = 3 " & _
" WHERE [Stock Number] = '" & _
Me.Units_Field & "' And " & _
"[In Use] = True And [Out Of Service] = True", dbFailOnError

instead, you won't get prompted for "Do you want to update...", plus you'll
be able to trap any errors that might arise.
 
Thanks for the help. This is going to save me a lot of time...Thanks again...
--
JT


Douglas J Steele said:
DoCmd.RunSQL "UPDATE[Table1] set[Comments] = Null, " & _
"[Field1] = 'Something Else', [Field2] = 3 " & _
" WHERE [Stock Number] = '" & _
Me.Units_Field & "' And " & _
"[In Use] = True And [Out Of Service] = True"

Incidentally, if you use

CurrentDb.Execute "UPDATE[Table1] set[Comments] = Null, " & _
"[Field1] = 'Something Else', [Field2] = 3 " & _
" WHERE [Stock Number] = '" & _
Me.Units_Field & "' And " & _
"[In Use] = True And [Out Of Service] = True", dbFailOnError

instead, you won't get prompted for "Do you want to update...", plus you'll
be able to trap any errors that might arise.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JT said:
I use the following RunSQL command to update a field in a table based on a
single criteria. I would like to modifiy this command for multiple criteria
but I'm having trouble with the syntax.

DoCmd.RunSQL "UPDATE[Table1] set[Comments] = Null WHERE [Stock Number] = '"
& Me.Units_Field & "'"

I want to modify the comments where (1) [Stock Number = me.units_field, (2)
"In Use" = True, and (3) "Out of Service" = True.

I would also like to update 2 other fields based on the same criteria. do I
have to run separate Run SQL commands or can the same command be modified to
"set" 3 separate fields based on the same multiple criteria?

Thanks for the help...........
Click to expand...
Click to expand...
 
I always break up my SQL statements to make reading/changing them easier
as in.

strSQL = ""
strSQL = strSQL & "UPDATE [Table1] SET [Comments] = Null "
strSQL = strSQL & "WHERE [StockNumber] = '" & Me.Units_Field & "'"

Then to add an additional criteria simply add another line

strSQL = strSQL & "AND [OrderNumber] = '" & Me.OrderNumber & "'"

The DoCmd.RunSQL becomes DoCmd.RunSQL strSQL
 
Back
Top