By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Database (DB) is rounding incorrectly

P: 2
I am new with access database and our database having rounding issues can any one help me here is the Query.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComputeInvoice(ciArg As String)
  2.     If IsNull(Me.[CustomerID]) Then
  3.       MsgBox ("Customer ID required!")
  4.       Exit Sub
  5.     End If
  6.     Dim SUBTOTAL As Double
  7.     Dim MATERIALCOST As Double
  8.     Dim HOURSTOTAL As Double
  9.     Dim TAX As Double
  10.     Dim MyDB As Database, MyRS As Recordset
  11.     Set MyDB = DBEngine.Workspaces(0).Databases(0)
  12.     Set MyRS = MyDB.OpenRecordset("tblcustomers", dbOpenTable)
  13.     MyRS.Index = "CustomerID"
  14.     MyRS.Seek "=", Me![CustomerID]
  15.     Me![CustomerID] = MyRS![CustomerID]
  16.     Me!LABORRATE = MyRS![LABORRATE]
  17.     MyRS.Close
  18.  
  19.     SUBTOTAL = 0
  20.     MATERIALCOST = 0
  21.     HOURSTOTAL = 0
  22.     'MsgBox Val(materialcost)
  23.     If Not IsNull(Me![MAT1]) Then MATERIALCOST = MATERIALCOST + Me!MAT1
  24.     If Not IsNull(Me![MAT2]) Then MATERIALCOST = MATERIALCOST + Me!MAT2
  25.     If Not IsNull(Me![MAT3]) Then MATERIALCOST = MATERIALCOST + Me!MAT3
  26.     If Not IsNull(Me![MAT4]) Then MATERIALCOST = MATERIALCOST + Me!MAT4
  27.     If Not IsNull(Me![MAT5]) Then MATERIALCOST = MATERIALCOST + Me!MAT5
  28.     If Not IsNull(Me![MAT6]) Then MATERIALCOST = MATERIALCOST + Me!MAT6
  29.     If Not IsNull(Me![MAT7]) Then MATERIALCOST = MATERIALCOST + Me!MAT7
  30.     If Not IsNull(Me![MAT8]) Then MATERIALCOST = MATERIALCOST + Me!MAT8
  31.     If Not IsNull(Me![MAT9]) Then MATERIALCOST = MATERIALCOST + Me!MAT9
  32.     If Not IsNull(Me![MAT10]) Then MATERIALCOST = MATERIALCOST + Me!MAT10
  33.     If Not IsNull(Me![MAT11]) Then MATERIALCOST = MATERIALCOST + Me!MAT11
  34.     If Not IsNull(Me![MAT12]) Then MATERIALCOST = MATERIALCOST + Me!MAT12
  35.     If Not IsNull(Me![MAT13]) Then MATERIALCOST = MATERIALCOST + Me!MAT13
  36.     If Not IsNull(Me![MAT14]) Then MATERIALCOST = MATERIALCOST + Me!MAT14
  37.     If Not IsNull(Me![MAT15]) Then MATERIALCOST = MATERIALCOST + Me!MAT15
  38.     If Not IsNull(Me![HOUR1]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR1
  39.     If Not IsNull(Me![HOUR2]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR2
  40.     If Not IsNull(Me![HOUR3]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR3
  41.     If Not IsNull(Me![HOUR4]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR4
  42.     If Not IsNull(Me![HOUR5]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR5
  43.     If Not IsNull(Me![HOUR6]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR6
  44.     If Not IsNull(Me![HOUR7]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR7
  45.     If Not IsNull(Me![HOUR8]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR8
  46.     If Not IsNull(Me![HOUR9]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR9
  47.     If Not IsNull(Me![HOUR10]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR10
  48.     If Not IsNull(Me![HOUR11]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR11
  49.     If Not IsNull(Me![HOUR12]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR12
  50.     If Not IsNull(Me![HOUR13]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR13
  51.     If Not IsNull(Me![HOUR14]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR14
  52.     If Not IsNull(Me![HOUR15]) Then HOURSTOTAL = HOURSTOTAL + Me!HOUR15
  53.     Me!totalmaterial = Round(MATERIALCOST, 2)
  54.     Me!totalhours = Round(HOURSTOTAL, 2)
  55.  
  56.     Me!totalcost = Round(HOURSTOTAL * LABORRATE, 2)
  57.     SUBTOTAL = Round((HOURSTOTAL * LABORRATE), 2) + MATERIALCOST
  58.     If Me!TYPE = "LR" Then Me!totalhours = Round((HOURSTOTAL * 1.5), 2)
  59.     If Me!TYPE = "LR" Then Me!totalcost = Round((HOURSTOTAL * 73), 2)
  60.  
  61.     If Me!TYPE = "LR" Then SUBTOTAL = Round((HOURSTOTAL * 73), 2) + MATERIALCOST
  62.  
  63.  
  64.  
  65.     TAX = 0
  66.     Me!invoicetotal = SUBTOTAL + TAX
  67.  
  68. If ciArg = "C" Then
  69.   Exit Sub
Can any one tell me when i add to total why my total is wrong like one digit up or down.
Dec 28 '07 #1
Share this Question
Share on Google+
4 Replies


Motoma
Expert 2.5K+
P: 3,235
Hi portsamerica,
Perhaps you could tell us what language you are writing this in, as well as the database you are using. Also, give us a general sense of where the error is occurring. List any debugging methods you have tried, as well as your thoughts on their results.
Dec 28 '07 #2

P: 2
this is MS ACCESS 2003 database and i never work on it.my boss want me to look in to that and fix the rounding issue.
Dec 28 '07 #3

NeoPa
Expert Mod 15k+
P: 31,419
Please tell us which item is rounding incorrectly.
What type of field is used for the item?
Jan 2 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Take a look at the thread Round function - how it REALLY works

Regards,
Fish
Jan 2 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.