RunSQL w multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JT - 20 Sep 2005 20:36 GMT

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

Signature

Reply to this Message

Douglas J Steele - 20 Sep 2005 21:06 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 11 lines]
>
> Thanks for the help...........

Reply to this Message

JT - 20 Sep 2005 21:25 GMT

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

Signature

> DoCmd.RunSQL "UPDATE[Table1] set[Comments] = Null, " & _
> "[Field1] = 'Something Else', [Field2] = 3 " & _
[quoted text clipped - 33 lines]
> >
> > Thanks for the help...........

Reply to this Message

David C. Holley - 20 Sep 2005 23:27 GMT

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

Reply to this Message