Quicker ways of doing the following

[bloody_football]

3 Questions

A) Is there a shorter way of doing this line?

DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'Hired' WHERE ((ContainerData.ContainerNumber) = [Container1]);")

B)Can I combine the 2 following lines into one statement -

DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'On Hire' WHERE ((ContainerData.ContainerNumber) = [Container1]);")
DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'On Hire' WHERE ((ContainerData.ContainerNumber) = [Container2]);")

C)Can I combine the 2 following lines into one statement

DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'On Hire' WHERE ((ContainerData.ContainerNumber) = [Container1]);")
DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.ReleaseDate = DateOfHire WHERE ((ContainerData.ContainerNumber) = [Container1]);")

 

[bloody_football]

Thanks Pat.

Oops quick edit - For the first question it is not the current record, it is in a differant table.

 

[Jon K]

B) Can I combine the 2 following lines into one statement -

DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'On Hire' WHERE ((ContainerData.ContainerNumber) = [Container1]);")

DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'On Hire' WHERE ((ContainerData.ContainerNumber) = [Container2]);")

B) Yes

DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'On Hire' WHERE ContainerData.ContainerNumber IN ([Container1], [Container2]);")
.

 

[bloody_football]

thanks Jon K

 

[24sharon]

I think so

B) yes
DoCmd.RunSQL ("UPDATE ContainerData SET ContainerData.Status = 'On Hire' WHERE ((ContainerData.ContainerNumber) in(" & [Container1] & "," & [Container2] & ");")

the result strSQL is
UPDATE ContainerData SET ContainerData.Status = 'On Hire'
WHERE (ContainerData.ContainerNumber) in(1, 3);

 

[WayneRyan]

Bloody,

B)

DoCmd.RunSQL ("UPDATE ContainerData " & _
              "SET    ContainerData.Status = 'On Hire' " & _
              "WHERE  ContainerData.ContainerNumber = [Container1] Or " & _
              "       ContainerData.ContainerNumber = [Container2];"

or maybe:

DoCmd.RunSQL ("UPDATE ContainerData " & _
              "SET    ContainerData.Status = 'On Hire' " & _
              "WHERE  ContainerData.ContainerNumber In ([Container1], [Container2]);"

Not sure on the second one.

Wayne

 

[bloody_football]

Thanks Wayne, Sharon & Pat