RunSQL w multiple criteria

[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...........

 

[Douglas J Steele]

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.

 

[Guest]

Thanks for the help. This is going to save me a lot of time...Thanks again...
--
JT

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!)

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...........

 

[David C. Holley]

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