VBA can't add!

[alley]

Which is ridiculous, of course.<br><br>Adding a list of numbers in a recordset clone comes up with wrong answers for all three sums. The currency numbers are low, and the integer one is high. <br><br>Source and object data types match.&nbsp;&nbsp;There is no number in any of the lists that matches the error amount.&nbsp;&nbsp;Did I fall into another Access known pot-hole?<br><br>Any thoughts would be appreciated.<br><br>alley

 

[DougP]

Can't troubleshooot with out code.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[alley]

DougP, here is the code; You sent it to me a couple days ago or so;<br><br><br>'For each task, accumulate bill amount, pay amount, and head count.<br><br>Private Sub Form_Load()<br>Dim rst As Recordset, i As Integer, tmpbil As Currency, tmppay As Currency, tmphdc As Integer<br><br>Set rst = Me.RecordsetClone<br><br>'get record count<br>rst.MoveLast<br>rst.MoveFirst<br><br>'clean 'em out<br>tmpbil = 0<br>tmppay = 0<br>tmphdc = 0<br><br>'add'em up<br>For i = 1 To rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;tmpbil = tmpbil + jbill<br>&nbsp;&nbsp;&nbsp;&nbsp;tmppay = tmppay + jpay<br>&nbsp;&nbsp;&nbsp;&nbsp;tmphdc = tmphdc + headcnt<br>Next<br><br>' done with recordset clone<br>rst.Close<br><br>'put results on main form<br>[Forms]![jobsndetail]!tbill = tmpbil<br>[Forms]![jobsndetail]!tpay = tmppay<br>[Forms]![jobsndetail]!thdc = tmphdc<br>'really, really done<br>End Sub<br><br>

 

[alley]

And here is the data set;<br><br>Job No Lot No Yard Code&nbsp;&nbsp;Head Cnt&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Job Bill&nbsp;&nbsp;&nbsp;Job Pay<br>0001 1 BFY 11 220 $242.00 $24.20<br>0001 1 BFY 12 133 $159.60 $15.96<br>0001 1 BFY 13 215 $279.50 $27.95<br>0001 1 BFY 22 146 $321.20 $32.12<br>0001 10 BFY 10 331 $331.00 $33.10

 

[alley]

and here are the results VBA and manual calculator;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pay&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;headcnt<br>VBA:&nbsp;&nbsp;&nbsp;&nbsp;1210.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;121.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1100<br>Calc:&nbsp;&nbsp;&nbsp;&nbsp;1333.30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;123.30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1045<br><br>Variance:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-123.30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-12.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;+55<br><br>the rate table that&nbsp;&nbsp;is used to calculate the bill and pay has the same ratio: 10:1<br>Note that VB's wrong answers have the same ratio.&nbsp;&nbsp;Strange...

 

[Amiel]

<br>'add'em up<br>For i = 1 To rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;tmpbil = tmpbil + jbill<br>&nbsp;&nbsp;&nbsp;&nbsp;tmppay = tmppay + jpay<br>&nbsp;&nbsp;&nbsp;&nbsp;tmphdc = tmphdc + headcnt<br>Next<br><br>to fix this you'll need to add the statement, <br><b> rst.MoveNext </b>, to move through the recordset.<br>But wait, don't use the above loop statement. Instead use this:<br><br><b><br>Do while not rst.EOF<br>' calculations here<br><br>rst.MoveNext<br>Loop<br></b><br><br>Incidently using rst.RecordCount after calling rst.MoveFirst followed by rst.MoveLast (i assume this is done to get the correct record count value into the RecordCount member of the recordset) is far more cumbersome than creating a recordset by simply calling...<br><br>sub GetTotalsForCustomer()<br><br>Dim db as database<br>dim rs as recordset<br><br>set db = dbengine(0)(0)'gets a reference to the current database<br><br>set rs = db.Openrecordset(&quot;TheRecordsetTheFormUses&quot<br><br>rs.MoveFirst <br>do while not rs.EOF<br>'do something with each record.<br><br><br>rs.MoveNext<br>Loop<br>Done !<br>End Sub<br><br>The above will execute as fast or faster than using <br>'rst = Me.RecordsetClone' followed by<br>rst.Move.First then rst.MoveLast to get acurate record count; and then a loop through each record.<br><br><br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[Jonathan]

I've seen something like this before.&nbsp;&nbsp;I asked a SQL Server DBA and he said that the Single datatype is notoriously bad as far as arithmetic is concerned.&nbsp;&nbsp;Don't know why this would affect the integer though. <p> Jonathan<br><a href=mailto:j.w.george@virginnet.co.uk>j.w.george@virginnet.co.uk</a><br><a href= > </a><br>Working with: Visual Basic 6, Access 97, Visual Interdev 6, VBScript, Active Server Pages, SQL Server 6.5, Oracle 7

 

[Jonathan]

Sorry, I've just realised that wasn't helpful at all.&nbsp;&nbsp;You could try changing the datatypes to Doubles and see if that helps at all.<br><br> <p> Jonathan<br><a href=mailto:j.w.george@virginnet.co.uk>j.w.george@virginnet.co.uk</a><br><a href= > </a><br>Working with: Visual Basic 6, Access 97, Visual Interdev 6, VBScript, Active Server Pages, SQL Server 6.5, Oracle 7

 

[alley]

Thanks Amiel. The recordset I am using here is never less than 2, or more than 5 or 6,<br>so the speed is probably not an issue.&nbsp;&nbsp;MoveNext is basic, and I missed it.&nbsp;&nbsp;Ah, well, life is hard.<br><br>alley