error, can trace, how to fix it?

J

Janjang

Registered User.
Local time
Today, 10:15
Joined
Oct 17, 2005
Messages
80
error, cant trace, how to fix it?

Code: 
Private Sub cmd_calculate_Click()

Dim daoRS As DAO.Recordset
Dim strRS As String
   
   strRS = _
      "SELECT COUNT(*) AS [Counter], " & _
      " AVG(NZ([A1-1],0)) AS [Aa11], AVG(NZ([A1-2],0)) AS [Aa12], AVG(NZ([A1-3],0)) AS [Aa13], AVG(NZ([A1-4],0)) AS [Aa14], " & _
      " AVG(NZ([A2-1],0)) AS [Aa21], AVG(NZ([A2-2],0)) AS [Aa22], AVG(NZ([A2-3],0)) AS [Aa23], " & _
      " AVG(NZ([A3-1],0)) AS [Aa31], AVG(NZ([A3-2],0)) AS [Aa32], " & _
      " AVG(NZ([B1-1],0)) AS [Bb11], AVG(NZ([B1-2],0)) AS [Bb12], AVG(NZ([B1-3],0)) AS [Bb13], AVG(NZ([B1-4],0)) AS [Bb14]" & _
      " AVG(NZ([C1-1],0)) AS [Cc11], AVG(NZ([C1-2],0)) AS [Cc12], AVG(NZ([C1-3],0)) AS [Bb13], AVG(NZ([C1-4],0)) AS [Cc14]" & _
      " AVG(NZ([C2-1],0)) AS [Cc21], AVG(NZ([C2-2],0)) AS [Cc22], AVG(NZ([C2-3],0)) AS [Cc23], " & _
      " AVG(NZ([C3-1],0)) AS [Cc31], AVG(NZ([C3-2],0)) AS [Cc32], AVG(NZ([C3-3],0)) AS [Cc33], AVG(NZ([C3-4],0)) AS [CC34], AVG(NZ([C3-5],0)) AS [CC35]" & _
      " AVG(NZ([D1-1],0)) AS [Dd11], AVG(NZ([D1-2],0)) AS [Dd12], AVG(NZ([D1-3],0)) AS [Dd13]" & _
      " AVG(NZ([E1-1],0)) AS [Ee11], AVG(NZ([E1-2],0)) AS [Ee12], AVG(NZ([E1-3],0)) AS [Ee13], AVG(NZ([E1-4],0)) AS [Ee14] " & _
      " AVG(NZ([E2-1],0)) AS [Ee21] " & _
      " AVG(NZ([F1-1],0)) AS [Ff11], AVG(NZ([F1-2],0)) AS [Ff12], AVG(NZ([F1-3],0)) AS [Ff13], AVG(NZ([F1-4],0)) AS [Ff14], AVG(NZ([F3-5],0)) AS [Ff15]" & _
      " AVG(NZ([G1-1],0)) AS [Gg11], AVG(NZ([G1-2],0)) AS [Gg12], AVG(NZ([G1-3],0)) AS [Gg13], AVG(NZ([G1-4],0)) AS [Gg14] " & _
      " AVG(NZ([H1-1],0)) AS [Hh11], AVG(NZ([H1-2],0)) AS [Hh12], AVG(NZ([H1-3],0)) AS [Hh13] " & _
      " AVG(NZ([H2-1],0)) AS [Hh11], AVG(NZ([H2-2],0)) AS [Hh22], AVG(NZ([H2-3],0)) AS [Hh23] " & _
      " AVG(NZ([J1-1],0)) AS [Jj11], AVG(NZ([J1-2],0)) AS [Jj12], AVG(NZ([J1-3],0)) AS [Jj13], AVG(NZ([J1-4],0)) AS [Jj14], AVG(NZ([J3-5],0)) AS [Jj15]" & _
      " AVG(NZ([I1-1],0)) AS [II11], AVG(NZ([I2-1],0)) AS [Ii21], AVG(NZ([I2-2],0)) AS [Ii22], AVG(NZ([I2-3],0)) AS [Ii23], AVG(NZ([I2-4],0)) AS [Ii24]" & _
      " AVG(NZ([I3-1],0)) AS [Ii31], AVG(NZ([I3-2],0)) AS [Ii32], AVG(NZ([I3-3],0)) AS [Ii33], AVG(NZ([I3-4],0)) AS [Ii34], AVG(NZ([I3-5],0)) AS [Ii35]" & _
      " AVG(NZ([K1-1],0)) AS [Kk11], AVG(NZ([K1-2],0)) AS [Kk12], AVG(NZ([K1-3],0)) AS [Kk13], AVG(NZ([K1-4],0)) AS [Kk14] " & _
      " AVG(NZ([K2-1],0)) AS [Kk21], AVG(NZ([K2-2],0)) AS [Kk22] " & _
      " FROM [Crew_Query] " & _
      " WHERE [CrewName] ='" & Me.CrewName2 & "' " & _
      " AND [Month]= '" & Me.Month2 & "' " & _
      " AND [Year] = " & Me.year2 & " "
'
   Set daoRS = CurrentDb.OpenRecordset(strRS)
'
   With daoRS
      If .EOF Then
         MsgBox "No Record Found"
         Me.A11 = ""
         Me.A12 = ""
         Me.A13 = ""
         Me.A14 = ""
         Me.A21 = ""
         Me.A22 = ""
         Me.A23 = ""
         Me.A31 = ""
         Me.A32 = ""
         Me.B11 = ""
         Me.B12 = ""
         Me.B13 = ""
         Me.B14 = ""
         Me.C11 = ""
         Me.C12 = ""
         Me.C13 = ""
         Me.C14 = ""
         Me.C21 = ""
         Me.C22 = ""
         Me.C23 = ""
         Me.C31 = ""
         Me.C32 = ""
         Me.C33 = ""
         Me.C34 = ""
         Me.C35 = ""
         Me.D11 = ""
         Me.D12 = ""
         Me.D13 = ""
         Me.E11 = ""
         Me.E12 = ""
         Me.E13 = ""
         Me.E14 = ""
         Me.E21 = ""
         Me.F11 = ""
         Me.F12 = ""
         Me.F13 = ""
         Me.F14 = ""
         Me.F15 = ""
         Me.G11 = ""
         Me.G12 = ""
         Me.G13 = ""
         Me.G14 = ""
         Me.H11 = ""
         Me.H12 = ""
         Me.H13 = ""
         Me.H21 = ""
         Me.H22 = ""
         Me.H23 = ""
         Me.J11 = ""
         Me.J12 = ""
         Me.J13 = ""
         Me.J14 = ""
         Me.J15 = ""
         Me.I11 = ""
         Me.I21 = ""
         Me.I22 = ""
         Me.I23 = ""
         Me.I24 = ""
         Me.I31 = ""
         Me.I32 = ""
         Me.I33 = ""
         Me.I34 = ""
         Me.I35 = ""
         Me.K11 = ""
         Me.K12 = ""
         Me.K13 = ""
         Me.K14 = ""
         Me.K21 = ""
         Me.K22 = ""
         
      Else
         Me.Counttext = !counter
         Me.A11 = !Aa11 * 100 '*100 to make percentage
         Me.A12 = !Aa12 * 100
         Me.A13 = !Aa13 * 100
         Me.A14 = !Aa14 * 100
         Me.A21 = !Aa21 * 100
         Me.A22 = !Aa22 * 100
         Me.A23 = !Aa23 * 100
         Me.A31 = !Aa31 * 100
         Me.A32 = !Aa32 * 100
         Me.B11 = !Bb11 * 100
         Me.B12 = !Bb21 * 100
         Me.B13 = !Bb13 * 100
         Me.B14 = !Bb14 * 100
         Me.C11 = !Cc11 * 100
         Me.C12 = !Cc12 * 100
         Me.C13 = !Cc13 * 100
         Me.C14 = !Cc14 * 100
         Me.C21 = !Cc21 * 100
         Me.C22 = !Cc22 * 100
         Me.C23 = !Cc23 * 100
         Me.C31 = !Cc31 * 100
         Me.C32 = !Cc32 * 100
         Me.C33 = !Cc33 * 100
         Me.C34 = !Cc34 * 100
         Me.C35 = !Cc35 * 100
         Me.D11 = !Dd11 * 100
         Me.D12 = !Dd12 * 100
         Me.D13 = !Dd13 * 100
         Me.E11 = !Ee11 * 100
         Me.E12 = !Ee12 * 100
         Me.E13 = !Ee13 * 100
         Me.E14 = !Ee14 * 100
         Me.E21 = !Ee21 * 100
         Me.F11 = !Ff11 * 100
         Me.F12 = !Ff12 * 100
         Me.F13 = !Ff13 * 100
         Me.F14 = !Ff14 * 100
         Me.F15 = !Ff15 * 100
         Me.G11 = !Gg11 * 100
         Me.G12 = !Gg12 * 100
         Me.G13 = !Gg13 * 100
         Me.G14 = !Gg14 * 100
         Me.H11 = !Hh11 * 100
         Me.H12 = !Hh12 * 100
         Me.H13 = !Hh13 * 100
         Me.H21 = !Hh21 * 100
         Me.H22 = !Hh22 * 100
         Me.H23 = !Hh23 * 100
         Me.J11 = !Jj11 * 100
         Me.J12 = !Jj12 * 100
         Me.J13 = !Jj13 * 100
         Me.J14 = !Jj14 * 100
         Me.J15 = !Jj15 * 100
         Me.I11 = !Ii11 * 100
         Me.I21 = !Ii21 * 100
         Me.I22 = !Ii22 * 100
         Me.I23 = !Ii23 * 100
         Me.I24 = !ii24 * 100
         Me.I31 = !Ii31 * 100
         Me.I32 = !Ii32 * 100
         Me.I33 = !Ii33 * 100
         Me.I34 = !Ii34 * 100
         Me.I35 = !Ii35 * 100
         Me.K11 = !Kk11 * 100
         Me.K12 = !Kk12 * 100
         Me.K13 = !Kk13 * 100
         Me.K14 = !Kk14 * 100
         Me.K21 = !Kk21 * 100
         Me.K22 = !Kk22 * 100
         
      End If
   End With
'
End Sub
 

Attachments

Last edited:
RE:error, cant trace, how to fix it?

I converted the select code to run from a query (as Below) it now works you had some fields specified incorrectly
(Bb ,Ff, Hh, Jj) once they were corrected it runs ok

Hope this points you in the right direction

SELECT COUNT(*) AS [Counter],
AVG(NZ([A1-1],0)) AS Aa11, AVG(NZ([A1-2],0)) AS Aa12, AVG(NZ([A1-3],0)) AS Aa13, AVG(NZ([A1-4],0)) AS Aa14,
AVG(NZ([A2-1],0)) AS Aa21, AVG(NZ([A2-2],0)) AS Aa22, AVG(NZ([A2-3],0)) AS Aa23,
AVG(NZ([A3-1],0)) AS Aa31, AVG(NZ([A3-2],0)) AS Aa32,
AVG(NZ([B1-1],0)) AS Bb11, AVG(NZ([B1-2],0)) AS Bb12, AVG(NZ([B1-3],0)) AS Bb13, AVG(NZ([B1-4],0)) AS Bb14,
AVG(NZ([C1-1],0)) AS Cc11, AVG(NZ([C1-2],0)) AS Cc12, AVG(NZ([C1-3],0)) AS Cc13, AVG(NZ([C1-4],0)) AS Cc14,
AVG(NZ([C2-1],0)) AS Cc21, AVG(NZ([C2-2],0)) AS Cc22, AVG(NZ([C2-3],0)) AS Cc23,
AVG(NZ([C3-1],0)) AS Cc31, AVG(NZ([C3-2],0)) AS Cc32, AVG(NZ([C3-3],0)) AS Cc33, AVG(NZ([C3-4],0)) AS CC34, AVG(NZ([C3-5],0)) AS CC35,
AVG(NZ([D1-1],0)) AS Dd11, AVG(NZ([D1-2],0)) AS Dd12, AVG(NZ([D1-3],0)) AS Dd13,
AVG(NZ([E1-1],0)) AS Ee11, AVG(NZ([E1-2],0)) AS Ee12, AVG(NZ([E1-3],0)) AS Ee13, AVG(NZ([E1-4],0)) AS Ee14,
AVG(NZ([E2-1],0)) AS Ee21,
AVG(NZ([F1-1],0)) AS Ff11, AVG(NZ([F1-2],0)) AS Ff12, AVG(NZ([F1-3],0)) AS Ff13, AVG(NZ([F1-4],0)) AS Ff14, AVG(NZ([F1-5],0)) AS Ff15,
AVG(NZ([G1-1],0)) AS Gg11, AVG(NZ([G1-2],0)) AS Gg12, AVG(NZ([G1-3],0)) AS Gg13, AVG(NZ([G1-4],0)) AS Gg14,
AVG(NZ([H1-1],0)) AS Hh11, AVG(NZ([H1-2],0)) AS Hh12, AVG(NZ([H1-3],0)) AS Hh13,
AVG(NZ([H2-1],0)) AS Hh21, AVG(NZ([H2-2],0)) AS Hh22, AVG(NZ([H2-3],0)) AS Hh23,
AVG(NZ([J1-1],0)) AS Jj11, AVG(NZ([J1-2],0)) AS Jj12, AVG(NZ([J1-3],0)) AS Jj13, AVG(NZ([J1-4],0)) AS Jj14, AVG(NZ([J1-5],0)) AS Jj15,
AVG(NZ([I1-1],0)) AS II11, AVG(NZ([I2-1],0)) AS Ii21, AVG(NZ([I2-2],0)) AS Ii22, AVG(NZ([I2-3],0)) AS Ii23, AVG(NZ([I2-4],0)) AS Ii24,
AVG(NZ([I3-1],0)) AS Ii31, AVG(NZ([I3-2],0)) AS Ii32, AVG(NZ([I3-3],0)) AS Ii33, AVG(NZ([I3-4],0)) AS Ii34, AVG(NZ([I3-5],0)) AS Ii35,
AVG(NZ([K1-1],0)) AS Kk11, AVG(NZ([K1-2],0)) AS Kk12, AVG(NZ([K1-3],0)) AS Kk13, AVG(NZ([K1-4],0)) AS Kk14,
AVG(NZ([K2-1],0)) AS Kk21, AVG(NZ([K2-2],0)) AS Kk22
FROM [Crew_Query]
WHERE [CrewName] = forms![f_result]![CrewName2]
AND [Month]= forms![f_result]![Month2]
AND [Year] = forms![f_result]![year2]
 
You must log in or register to reply here.

Similar threads

N
Solved How to update bulk selling prices at one go
Replies
5
Views
357
nector
N
N
Solved How to grab fields from Json string to be part of the invoice
Replies
14
Views
1,694
Edgar_
E
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
2,697
The_Doc_Man
The_Doc_Man
N
Chart Error: Expression is typed incorrectly, or is too complex to be evaluated
Replies
7
Views
1,812
The_Doc_Man
The_Doc_Man
D
Solved How to sort values (the highest first)? If duplicates values found, the sorting is based on the saved table for those values
Replies
10
Views
1,444
jdraw
jdraw

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom