Order of battle in a query 2

[jpl458]

Need to know if an SQL Query (See Below) executes each of the parts in the order that they are written in the query;

Example;

udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields("Count") - rsdbo.Fields("SumOfItems") & "," & _
" [AddTime] = #" & TimeValue(Now()) & _
"#, [AddDate] = #" & DateValue(Now()) & _
"# WHERE ID = " & CInt(Right(rsdbo.Fields("Bayno"), 2))


DoCmd.SetWarnings False
DoCmd.RunSQL udq
DoCmd.SetWarnings True
rsdbo.MoveNext

I need to add to this query, but the order of execution is critical. I suer that it does, but just want to make sure.

Thanks

jpl

 

[PHV]

each of the parts
Which parts ?
For me, the order of columns in an UPDATE statement is irrelevant.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

First the UPDATE to identify the table, then WHERE to filter the table and finally the SETs.

I assume (but don't know for sure) that the SET clauses are executed in the order presented. In this case it doesn't much matter since all the specified fields are updated when the query completes and, as far as I can see, there isn't any interaction between the field values you are setting.

What are you planning to add that makes the order-of-execution critical?

 

[jpl458]

This app run in a timer event and has to calculate the number balls used, so I have to calculate that from current number minus to previous number. It has to do with the application that was here hand how it keeps track of stuff. So at the end of the update query I have to move the current value in the table to the previous value in the table. The way I tried to get around it was to add another small update query after the main calculation that move the current to the old. Here is the code: (ACCESS vba)

udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields("Count") - rsdbo.Fields("SumOfBalls") - BayCounts.PreSumBalls & "," & _
" [AddTime] = #" & TimeValue(Now()) & _
"#, [AddDate] = #" & DateValue(Now()) & _
"# WHERE ID = " & CInt(Right(rsdbo.Fields("Bayno"), 2))


DoCmd.SetWarnings False
DoCmd.RunSQL udq
DoCmd.RunSQL "UPDATE BayCounts SET BayCounts.PreSumBalls = rsdbo.Fields("SumOfBalls")"
DoCmd.SetWarnings True
rsdbo.MoveNext

Its in a loop. But the problem now is the DoCmd.RunSQL "UPDATE BayCounts SET BayCounts.PreSumBalls = rsdbo.Fields("SumOfBalls")" gives me a missing end of error. rsdbo is still live, it hasen't been closed or set to nothing

Thanks for the quick responses

jpl

 

[PHV]

Why not simply this ?

udq = _
"UPDATE BayCounts " & _
"SET [Count]=" & rsdbo.Fields("Count") - rsdbo.Fields("SumOfBalls") & " - PreSumBalls," & _
"PreSumBalls=" & rsdbo.Fields("SumOfBalls") & "," & _
"AddTime=#" & TimeValue(Now) & _
"#,AddDate=#" & DateValue(Now) & _
"# WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

PHV sent the follwing dit of code that executes perfectly. The problem was/is my logic:
In the blue shaded line is the error - It's missing parentheses thus causing the subtraction to take place with both SumOfBalls fields when I should be subtracting the difference between the two fields. This query executes just fine.

udq = _
"UPDATE BayCounts " & _
"SET [Count]=" & [COLOR=]rsdbo.Fields("Count") - rsdbo.Fields("SumOfBalls") & " - PreSumBalls,"[/color] & _
"PreSumBalls=" & rsdbo.Fields("SumOfBalls") & "," & _
"AddTime=#" & TimeValue(Now) & _
"#,AddDate=#" & DateValue(Now) & _
"# WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))

I tried the follwing, which compiles but bombs on execution with a type mismatch, which I don't see why.
I added parentheses to the second line of the code to force the subtraction between (rsdbo.Fields("SumOfBalls") and PreSumBalls to take place first (Which are the same data type), then do the subtract from count, which will yield the correct answer

udq = _
"UPDATE BayCounts " & _
"SET [Count]=" & rsdbo.Fields("Count") - (rsdbo.Fields("SumOfBalls") & " - PreSumBalls,") & _
"PreSumBalls=" & rsdbo.Fields("SumOfBalls") & "," & _
"AddTime=#" & TimeValue(Now) & _
"#,AddDate=#" & DateValue(Now) & _
"# WHERE ID=" & CInt(Right(rsdbo.Fields("Bayno"), 2))

Once again, anyhelp would be appreciated.

Thanks

jpl

 

[PHV]

SET [Count]=" & rsdbo.Fields("Count") - rsdbo.Fields("SumOfBalls") & " [!]+[/!] PreSumBalls," & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

PHV it works. I was thrown by the addition in the subtraction, but I had to review my long ago 8th grad math skills. But could you explaing, briefly, why you did it that way, please.

Thanks so much, again.

jpl

 

[PHV]

A - (B - C) = A - B + C

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886