Problem changing Query 2

[jpl458]

have the following code:
Dim SumBalls As Variant

SumBalls = "SELECT BayCounts.ID, BayCounts.Bayno, BayCounts.Count, dbo_Transaction_Table.UseEndTime, Sum(dbo_Transaction_Table.Balls) AS SumOfBalls "
SumBalls = SumBalls + "FROM BayCounts INNER JOIN dbo_Transaction_Table ON BayCounts.Bayno = dbo_Transaction_Table.Location_Id "
SumBalls = SumBalls + "WHERE (((DateValue([dbo_Transaction_Table]![Date_of_Transaction]) + TimeValue([dbo_Transaction_Table]![Time_of_Transaction])) > DateValue([BayCounts]![AddDate]) + TimeValue([dbo_Transaction_Table].[UseEndTime]))) "
SumBalls = SumBalls + "GROUP BY BayCounts.ID, BayCounts.Bayno, BayCounts.Count, dbo_Transaction_Table.UseEndTime"


'Set up select query
Dim cnndbo As ADODB.Connection
Set cnndbo = CurrentProject.Connection
Dim rsdbo As New ADODB.Recordset
rsdbo.ActiveConnection = cnndbo
rsdbo.CursorType = adOpenDynamic
'-------------Send query to SQL Server----------------------------------------------------------------'
rsdbpen SumBalls
rsdbo.MoveFirst
'-------------------------------------------------------------------------------------------------------
'Update BayCounts.Counts from RecordSet rsdbo, Counts minus SumofBalls
'-------------------------------------------------------------------------------------------------------
Dim udq As Variant
Do While Not rsdbo.EOF
udq = "UPDATE BayCounts SET [Count]=" & rsdbo.Fields(2).Value - rsdbo.Fields(4).Value & " BayCounts [AddTime]=" & rsdbo.Fields(3) & "WHERE ID=" & Right(rsdbo.Fields(1).Value, 2)
DoCmd.SetWarnings False
DoCmd.RunSQL udq
DoCmd.SetWarnings True
rsdbo.MoveNext
Loop
The problem is in:

udq = "UPDATE BayCounts SET [Count]=" & rsdbo.Fields(2).Value - rsdbo.Fields(4).Value & " BayCounts [AddTime]=" & rsdbo.Fields(3) & "WHERE ID=" & Right(rsdbo.Fields(1).Value, 2)

I need to add BayCounts [AddTime] = Rsdbo.Fields(3).value to the update query. (The above is my latest attempt) I got help on the original update query, but can't get this to work. I have referenced two books and the web but all they show are simple examples that don't help much. It's the use of "s I don't understand in the above.

Thanks for your help.

jpl

 

[Golom]

Try

udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields(2).Value-rsdbo.Fields(4).Value & ",[COLOR=#FCE94F][/color]" & _
"    [AddTime] = " & rsdbo.Fields(3) & _
"WHERE ID = " & Right(rsdbo.Fields(1).Value, 2)

 

[Andrzejek]

[tt]
udq = "UPDATE BayCounts SET " _
& " [Count] = " & rsdbo.Fields("Bayno").Value - rsdbo.Fields("UseEndTime").Value & [blue]", "[/blue] _
& " [AddTime] = " & rsdbo.Fields("Count") _
& " WHERE ID = " & Right(rsdbo.Fields("ID").Value, 2)
[/tt]

Have fun.

---- Andy

 

[Andrzejek]

Lost by a minute.... :-(

Also try:[tt][blue]
Debug.Print udq[/blue][/tt]
and see what you are getting.

Have fun.

---- Andy

 

[dhookom]

I don't care much for

& " WHERE ID = " & Right(rsdbo.Fields("ID").Value, 2)

It appears the ID field is numeric but IMO the function Right() should be for strings/text. It does work for numbers but I would probably use:

& " WHERE ID = " &  rsdbo.Fields("ID") mod 100

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Ran the query and had a problem.
Code
udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields(2).Value - rsdbo.Fields(4).Value & "," & _
" [AddTime] = " & rsdbo.Fields(3) & _
"WHERE ID = " & Right(rsdbo.Fields(1).Value, 2)

Debug.Print udq
Debug.Print rsdbo.Fields(0).Value
Debug.Print rsdbo.Fields(1).Value
Debug.Print rsdbo.Fields(2).Value
Debug.Print rsdbo.Fields(3).Value
Debug.Print rsdbo.Fields(4).Value
Debug.Print rsdbo.Fields(2).Value - rsdbo.Fields(4).Value
DoCmd.SetWarnings False
DoCmd.RunSQL udq
DoCmd.SetWarnings True
rsdbo.MoveNext

Immediate Window
UPDATE BayCounts SET [Count] = 658, [AddTime] = 10/25/2012 11:32:35 AMWHERE ID = 02
2
DW02
691
10/25/2012 11:32:35 AM
33
658

Error = Syntax Error (Missing operator) in query expression '11:32:35 AMWHERE ID = 02'

Think it's concatonating the time and the ID

Thanks for the quick response

jpl

jpl

 

[jpl458]

When I execute this query I get a syntax error and the Debug.Print udq =

UPDATE BayCounts SET [Count] = 1177, [AddTime] = 12:51:30 PMWHERE ID = 01

It look like AddTime and Id are concatonated.
How do I fix that?


udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields(2).Value - rsdbo.Fields(4).Value & "," & _
" [AddTime] = " & TimeValue(rsdbo.Fields(3)) & _
"WHERE ID = " & Right(rsdbo.Fields(1).Value, 2)


Thanks

jpl

 

[dhookom]

You need to understand the difference between strings and numbers and dates. These are all handled differently in code and SQL. You were missing delimiters around the date value and possibly quotes around the text value.

[green]' assuming ID is text and AddTime is date/time[/green]
udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields(2) - rsdbo.Fields(4) & "," & _
" [AddTime] = #" & rsdbo.Fields(3) & _
"# WHERE ID = """ & Right(rsdbo.Fields(1), 2) & """ "

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Duane, I checked and you were right. ID is a number (Long Integer), and the recordset data right(rsdbo.fields(1),2) is text. I now get a type mismatch error. Tried the following but it didn't work. Still got type mismatch.

udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields(2) - rsdbo.Fields(4) & "," & _
" [AddTime] = #" & rsdbo.Fields(3) & _
"# WHERE ID = """ & CInt(Right(rsdbo.Fields(1), 2)) & """ "

Any suggestions?

Thanks

jpl

 

[jpl458]

Duane, The ID field is Long Integer and the recordset data rsdbo.fields(1).value is a text fields. I tried this change to the code, but it did not work. still get type mismatch. CInt(Right(rsdbo.Fields(1), 2))

udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields(2) - rsdbo.Fields(4) & "," & _
" [AddTime] = #" & rsdbo.Fields(3) & _
"# WHERE ID = """ & CInt(Right(rsdbo.Fields(1), 2)) & """ "

Thanks again

jpl

 

[dhookom]

It would help if you provided the debug.print results. However try remove the extra quotes since they resulted in creating a string which was compared with ID which is numeric. This causes an error.

udq = _
"UPDATE BayCounts " & _
"SET [Count] = " & rsdbo.Fields(2) - rsdbo.Fields(4) & "," & _
" [AddTime] = #" & rsdbo.Fields(3) & _
"# WHERE ID = " & CInt(Right(rsdbo.Fields(1), 2))

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Duane, that did the trick. I need to bone up on expressions, I have difficulty with () "" etc.

Thanks again for the help

jpl

 

[Andrzejek]

I need to bone up on expressions" - you may want to read this little piece on Punctuation Problem There is a simple sample of Select sql at the bottom with the explanation .

Have fun.

---- Andy

 

[dhookom]

Andy,
Very nice examples of using delimiters and building SQL statements.

Duane
Hook'D on Access
MS Access MVP