SQL - Update

  • Thread starter Thread starter AHopper
  • Start date Start date
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
 
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.
 
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 & ");"
 
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
 
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...
 
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...
 
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...
 
Back
Top