sum values ​​with condition

[g.adamo@email.it]

I'm not sure I understand



what and how I have to do?

[g.adamo@email.it]

orderSQL = "desc"
greatless = "<"
mysql = "SELECT TOP 1 updateallresults.values" _
& " FROM updateallresults" _
& " WHERE (((updateallresults.checkpoint)=" & checkptsql & ") AND ((updateallresults.system)=" & Chr(34) & sysSQL & Chr(34) & ") AND ((updateallresults.tilt)" & greatless & rs.Fields("tilt") & ") and ((IsNull([values]))=False) AND ((updateallresults.values)<>0))" _
& " ORDER BY updateallresults.tilt " & orderSQL & ";"
' Debug.Print mysql & "here"

Set rst = CurrentDb.OpenRecordset(mysql)
If rst.EOF Then
completed = False
Else: updateval = rst.Fields(0)
completed = True
End If





End If














on debug:




sqlstring = "UPDATE tbltilt_full SET tbltilt_full.[values] =" & updateval & " WHERE (([tbltilt_full].[code]=" & Chr(34) & rs.Fields("code") & Chr(34) & "));"


Debug.Print i & " " & sqlstring


CurrentDb.Execute (sqlstring)
rs.MoveNext
i = i + 1
Loop

[g.adamo@email.it]

Option Explicit
Option Compare Database

Private Sub Command0_Click()
Dim rst As dao.Recordset
Dim db As Database
Dim rs As dao.Recordset
Set db = CurrentDb
Dim i As Integer
i = 0
Dim checkptsql As Integer
Dim sysSQL As String
Dim orderSQL As String
Dim greatless As String
Dim mysql As String
Dim updateval As Variant
updateval = 0
Dim completed As Boolean


Set rs = db.OpenRecordset("updateallresults")
Dim sqlstring As String


Do While Not rs.EOF
completed = False
Select Case IsNull(rs.Fields("values")) Or rs.Fields("values") = 0
Case Is = False
updateval = rs.Fields("values")
completed = True

Case Is = True

checkptsql = rs.Fields("checkpoint")
sysSQL = rs.Fields("system")
orderSQL = "Asc"
greatless = ">"
mysql = "SELECT TOP 1 updateallresults.values" _
& " FROm updateallresults" _
& " WHERE (((updateallresults.checkpoint)=" & checkptsql & ") AND ((updateallresults.system)=" & Chr(34) & sysSQL & Chr(34) & ") AND ((updateallresults.tilt)" & greatless & rs.Fields("tilt") & ") and ((IsNull([values]))=False) AND ((updateallresults.values)<>0))" _
& " ORDER BY updateallresults.tilT " & orderSQL & ";"
'Debug.Print mysql

Set rst = CurrentDb.OpenRecordset(mysql)

If rst.EOF Then
completed = False



Else: updateval = rst.Fields(0)
completed = True

End If

End Select






If completed = False Then



orderSQL = "desc"
greatless = "<"
mysql = "SELECT TOP 1 updateallresults.values" _
& " FROM updateallresults" _
& " WHERE (((updateallresults.checkpoint)=" & checkptsql & ") AND ((updateallresults.system)=" & Chr(34) & sysSQL & Chr(34) & ") AND ((updateallresults.tilt)" & greatless & rs.Fields("tilt") & ") and ((IsNull([values]))=False) AND ((updateallresults.values)<>0))" _
& " ORDER BY updateallresults.tilt " & orderSQL & ";"
' Debug.Print mysql & "here"

Set rst = CurrentDb.OpenRecordset(mysql)
If rst.EOF Then
completed = False
Else: updateval = rst.Fields(0)
completed = True
End If





End If



















sqlstring = "UPDATE tbltilt_full SET tbltilt_full.[values] =" & updateval & " WHERE (([tbltilt_full].[code]=" & Chr(34) & rs.Fields("code") & Chr(34) & "));"


Debug.Print i & " " & sqlstring


CurrentDb.Execute (sqlstring)
rs.MoveNext
i = i + 1
Loop






















rs.Close

Set rs = Nothing
rst.Close


Set rst = Nothing











End Sub



Private Sub Command6_Click()
Dim checks As Integer
Dim tilts As Integer
Dim check As Integer
Dim i, j As Integer
Dim systemlist As String
Dim systems As Integer
Dim syst() As String
Dim numbers As Variant
Dim db As Database
Set db = CurrentDb
Dim mysql As String
Dim rs As Recordset
Set rs = db.OpenRecordset("qrydistincttilts")
systems = rs.RecordCount
Do Until rs.EOF
systemlist = systemlist & rs.Fields("system") & ","
rs.MoveNext
Loop
Debug.Print systemlist


DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tbltilt_full"
DoCmd.SetWarnings True


checks = DMax("[checkpoint]", "tbltilt")
tilts = DMax("[tilt]", "tbltilt")

syst = Split(systemlist, ",")
For check = 1 To checks
For i = 1 To systems



For j = 0 To tilts
mysql = "insert into tbltilt_full (checkpoint,system,tilt)" _
& " values (" & check & "," & Chr(34) & syst(i - 1) & Chr(34) & "," & j & ");"
'Debug.Print mysql



db.Execute (mysql)
Next
Next
Next



End Sub

[g.adamo@email.it]

Cartel1.zip
I attached a file input that contains max values (checkpoint 1-10; system 1-10; tilt 0-10. exploding sum with condition this file have to produce more than one 1000 rows, the result may be 10*10*11)

[andy49]

valueswithconditionv5.zip

If I import your data into tbltilt using the ExternalData (excel) wizard this works perfectly

[g.adamo@email.it]

ummumum
if I import excell (wizard) when I push the second button the same error appears:
on local window
4OF04871 CALTAGIRONE OVEST 1° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 10° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 2° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 3° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 4° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 5° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 6° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 7° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 8° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 9° GSMVODAFONE,
0 UPDATE tbltilt_full SET tbltilt_full.[varvalues] =0,157 WHERE (([tbltilt_full].[varcode]="64OF04871 CALTAGIRONE OVEST 5° GSMVODAFONE10"));

[andy49]

Where is that comma coming from? 0,157??


Sent from my iPhone using Tapatalk

[g.adamo@email.it]

on the new tbltilt the record 0,157 fisrt time appears on the row 91

I imported excell on tbltilt then I press first botton .
ok
then I press second botton and the same error appear "error run time 3144...." Iclose the error window press alt+11 then ctrl+g amd on local window appears:
4OF04871 CALTAGIRONE OVEST 1° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 10° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 2° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 3° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 4° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 5° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 6° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 7° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 8° GSMVODAFONE,4OF04871 CALTAGIRONE OVEST 9° GSMVODAFONE,
0 UPDATE tbltilt_full SET tbltilt_full.[varvalues] =0,157 WHERE (([tbltilt_full].[varcode]="64OF04871 CALTAGIRONE OVEST 5° GSMVODAFONE10"));

[andy49]

valueswithconditionv51.zip


Well I think this should do the job.

I replicated your error on my PC by changing my local settings to show a comma in a decimal number. (SQL hates 0,04 I presume!!)


The error no longer happens as I've replaced the comma with a "." in the SQL update statement

Fingers crossed!!