SQL Update Field based on Two Fields

[jlfvlf]

On my form, I am trying to update one field in a table where results are based on two fields.

My code that is not working is:

DoCmd.RunSQL ("UPDATE tbl_1 SET [Field3]=(""" & Yellow & """) WHERE [Field1]=(""" & Brown & """) & [Field2]=(""" & Blue & """);")

I have only been able to update with the DoCmd.SQL statement based on one WHERE statement, not two.

 

[arnelgp]

DoCmd.RunSQL "UPDATE tbl_1 SET [Field3]='Yellow' WHERE [Field1]='Brown' AND [Field2]='Blue'"

 

[jlfvlf]

When I put the "& AND in the middle of my two WHERE statements, I receive a compile error, Expected: list or separator or )

When I put & AND in the middle of my two WHERE statements, I receive a Syntax error (missing operator) in query expression '[Field1]=("Brown") & AND [Field2]=("Blue")'.

 

[Minty]

To further Pat's suggestion and it would highlight where you are going wrong

Dim sSql as String

sSql = "UPDATE tbl_1 SET [Field3]=(""" & Yellow & """) WHERE [Field1]=(""" & Brown & """) [COLOR="Red"]& " AND[/COLOR] [Field2]=(""" & Blue & """);"

Debug.Print sSql
DoCmd.RunSQL sSql

Note the bit in red where there is a difference between yours and Pat's . Arne's version is much easier to read.
In the above it infers that Yellow , Brown and Blue are variables?


If they are I would do it like ;

    Dim sSql As String

    sSql = "UPDATE tbl_1 SET [Field3]= '" & Yellow & "' WHERE [Field1]='" & Brown & "' And [Field2] = '" & Blue & "'"

    Debug.Print sSql

    DoCmd.RunSQL sSql