SQL - Update

A

AHopper

I want to update three
fields "Closed"(Yes/No), "DateClosed" (Date/Time),
and "EmployeeClosed" (Number) in the same
table "BatchNumber". Presently I am using three SQL
statements to do this. They work but can they be combined
into one SQL?

DoCmd.SetWarnings True

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = -1 "
& "WHERE ((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob
& "And (BatchNumbers.BatchNumber)=" &
Me.OperatorBatchNumber & ");"

DoCmd.RunSQL SQL
SQL = "UPDATE BatchNumbers SET BatchNumbers.DateClosed =
Now " & "WHERE ((BatchNumbers.UsedWithJob)=" &
Me.UsedWithJob & "And (BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

SQL = "UPDATE BatchNumbers SET BatchNumbers.EmployeeClosed
= [Forms]![OneTwoOperatorForm]![EmployeeNo] " & "WHERE
((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob & "And
(BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

DoCmd.SetWarnings True

Still learning how to use SQL so any help would be
appreciated.

Thanks
Allan
 
D

Dan McClelland

Just separate the SET statements with commas.

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = -1,
BatchNumbers.DateClosed = Now"

Also, you can build your query in the QBE and look at the
SQL view of it to see how it translates into SQL syntax.
You can even build it in QBE and copy/paste the SQL
statement into code.
 
D

Dirk Goldgar

AHopper said:
I want to update three
fields "Closed"(Yes/No), "DateClosed" (Date/Time),
and "EmployeeClosed" (Number) in the same
table "BatchNumber". Presently I am using three SQL
statements to do this. They work but can they be combined
into one SQL?

DoCmd.SetWarnings True

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = -1 "
& "WHERE ((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob
& "And (BatchNumbers.BatchNumber)=" &
Me.OperatorBatchNumber & ");"

DoCmd.RunSQL SQL
SQL = "UPDATE BatchNumbers SET BatchNumbers.DateClosed =
Now " & "WHERE ((BatchNumbers.UsedWithJob)=" &
Me.UsedWithJob & "And (BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

SQL = "UPDATE BatchNumbers SET BatchNumbers.EmployeeClosed
= [Forms]![OneTwoOperatorForm]![EmployeeNo] " & "WHERE
((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob & "And
(BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

DoCmd.SetWarnings True

Still learning how to use SQL so any help would be
appreciated.
Click to expand...

There's a difference between the WHERE clauses of your first SQL
statement and the last two. Is that a mistake, or intentional? As it
stands, it looks like the last two statements can be combined into one,
but the first statement can't because it applies to a different set of
records.

Here's how you might combine the last two statements into one:

SQL = _
"UPDATE BatchNumbers SET" & _
" BatchNumbers.DateClosed = Now" & _
", BatchNumbers.EmployeeClosed = " & _
"[Forms]![OneTwoOperatorForm]![EmployeeNo]" & _
" WHERE ((BatchNumbers.UsedWithJob)=" & _
Me.UsedWithJob & _
" And (BatchNumbers.CoilerBatchNumber)=" & _
Me.OperatorBatchNumber & ");"
 
M

Marshall Barton

AHopper said:
I want to update three
fields "Closed"(Yes/No), "DateClosed" (Date/Time),
and "EmployeeClosed" (Number) in the same
table "BatchNumber". Presently I am using three SQL
statements to do this. They work but can they be combined
into one SQL?

DoCmd.SetWarnings True

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = -1 "
& "WHERE ((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob
& "And (BatchNumbers.BatchNumber)=" &
Me.OperatorBatchNumber & ");"

DoCmd.RunSQL SQL
SQL = "UPDATE BatchNumbers SET BatchNumbers.DateClosed =
Now " & "WHERE ((BatchNumbers.UsedWithJob)=" &
Me.UsedWithJob & "And (BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

SQL = "UPDATE BatchNumbers SET BatchNumbers.EmployeeClosed
= [Forms]![OneTwoOperatorForm]![EmployeeNo] " & "WHERE
((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob & "And
(BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

DoCmd.SetWarnings True
Click to expand...


Yes, you can combine them.

SQL = "UPDATE BatchNumbers " _
& "SET Closed = True, " _
& "DateClosed = Now(), " _
& "EmployeeClosed = " _
& Forms!OneTwoOperatorForm!EmployeeNo _
& " WHERE BatchNumbers.UsedWithJob = " _
& Me.UsedWithJob _
& " And (BatchNumbers.CoilerBatchNumber) = " _
& Me.OperatorBatchNumber
 
A

AHopper

Dan, thank you for your help.

Allan
-----Original Message-----
Just separate the SET statements with commas.

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = -1,
BatchNumbers.DateClosed = Now"

Also, you can build your query in the QBE and look at the
SQL view of it to see how it translates into SQL syntax.
You can even build it in QBE and copy/paste the SQL
statement into code.
-----Original Message-----
I want to update three
fields "Closed"(Yes/No), "DateClosed" (Date/Time),
and "EmployeeClosed" (Number) in the same
table "BatchNumber". Presently I am using three SQL
statements to do this. They work but can they be combined
into one SQL?

DoCmd.SetWarnings True

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = - 1 "
& "WHERE ((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob
& "And (BatchNumbers.BatchNumber)=" &
Me.OperatorBatchNumber & ");"

DoCmd.RunSQL SQL
SQL = "UPDATE BatchNumbers SET BatchNumbers.DateClosed =
Now " & "WHERE ((BatchNumbers.UsedWithJob)=" &
Me.UsedWithJob & "And (BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

SQL = "UPDATE BatchNumbers SET BatchNumbers.EmployeeClosed
= [Forms]![OneTwoOperatorForm]![EmployeeNo] " & "WHERE
((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob & "And
(BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

DoCmd.SetWarnings True

Still learning how to use SQL so any help would be
appreciated.

Thanks
Allan
.
Click to expand...
.
Click to expand...
 
A

AHopper

Marsh, thank you for your help

Allan
-----Original Message-----
AHopper said:
I want to update three
fields "Closed"(Yes/No), "DateClosed" (Date/Time),
and "EmployeeClosed" (Number) in the same
table "BatchNumber". Presently I am using three SQL
statements to do this. They work but can they be combined
into one SQL?

DoCmd.SetWarnings True

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = - 1 "
& "WHERE ((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob
& "And (BatchNumbers.BatchNumber)=" &
Me.OperatorBatchNumber & ");"

DoCmd.RunSQL SQL
SQL = "UPDATE BatchNumbers SET BatchNumbers.DateClosed =
Now " & "WHERE ((BatchNumbers.UsedWithJob)=" &
Me.UsedWithJob & "And (BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

SQL = "UPDATE BatchNumbers SET BatchNumbers.EmployeeClosed
= [Forms]![OneTwoOperatorForm]![EmployeeNo] " & "WHERE
((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob & "And
(BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

DoCmd.SetWarnings True
Click to expand...


Yes, you can combine them.

SQL = "UPDATE BatchNumbers " _
& "SET Closed = True, " _
& "DateClosed = Now(), " _
& "EmployeeClosed = " _
& Forms!OneTwoOperatorForm!EmployeeNo _
& " WHERE BatchNumbers.UsedWithJob = " _
& Me.UsedWithJob _
& " And (BatchNumbers.CoilerBatchNumber) = " _
& Me.OperatorBatchNumber
Click to expand...
 
A

AHopper

Dirk, I did make a mistake, all three should be the same.
Thank you for your help.
Allan
-----Original Message-----
I want to update three
fields "Closed"(Yes/No), "DateClosed" (Date/Time),
and "EmployeeClosed" (Number) in the same
table "BatchNumber". Presently I am using three SQL
statements to do this. They work but can they be combined
into one SQL?

DoCmd.SetWarnings True

SQL = "UPDATE BatchNumbers SET BatchNumbers.Closed = - 1 "
& "WHERE ((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob
& "And (BatchNumbers.BatchNumber)=" &
Me.OperatorBatchNumber & ");"

DoCmd.RunSQL SQL
SQL = "UPDATE BatchNumbers SET BatchNumbers.DateClosed =
Now " & "WHERE ((BatchNumbers.UsedWithJob)=" &
Me.UsedWithJob & "And (BatchNumbers.CoilerBatchNumber) =" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

SQL = "UPDATE BatchNumbers SET BatchNumbers.EmployeeClosed
= [Forms]![OneTwoOperatorForm]![EmployeeNo] " & "WHERE
((BatchNumbers.UsedWithJob)=" & Me.UsedWithJob & "And
(BatchNumbers.CoilerBatchNumber)=" &
Me.OperatorBatchNumber & ");"
DoCmd.RunSQL SQL

DoCmd.SetWarnings True

Still learning how to use SQL so any help would be
appreciated.
Click to expand...

There's a difference between the WHERE clauses of your first SQL
statement and the last two. Is that a mistake, or intentional? As it
stands, it looks like the last two statements can be combined into one,
but the first statement can't because it applies to a different set of
records.

Here's how you might combine the last two statements into one:

SQL = _
"UPDATE BatchNumbers SET" & _
" BatchNumbers.DateClosed = Now" & _
", BatchNumbers.EmployeeClosed = " & _
"[Forms]![OneTwoOperatorForm]! [EmployeeNo]" & _
" WHERE ((BatchNumbers.UsedWithJob)=" & _
Me.UsedWithJob & _
" And (BatchNumbers.CoilerBatchNumber)=" & _
Me.OperatorBatchNumber & ");"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax Error WHY? 16
Update, AddNew 3
SQL Date/Time Update 2
Function or Variable Expected 6
SQL Not Updating 5
Update table script 6
Two SQL statements in form -- UPDATE works; INSERT doesn't 2
Syntax error in Update statement 3

Top