Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    13

    Post Unanswered: Access Rounding problem

    Hi,

    Firstly thanks in advance for looking at this. I am quite new to VBA...

    I have VBA code which looks up a price based on a customer and part field of a table...

    The result always rounds up or down to the nearest whole number, I need to show 2 dec places for currency...

    Any help, thanks

    The code is.......

    Private Sub PartNo_AfterUpdate()

    Dim sSQL As String
    Dim Rec As Recordset
    Dim db As Database

    ' Query to bring out the price, using variables in the form.
    sSQL = "SELECT DISTINCT price FROM tblPrices "
    sSQL = sSQL & "WHERE partno = '" & PartNo & "' AND CustID = '" & CustID & "';"


    Set db = CurrentDb
    Set Rec = db.OpenRecordset(sSQL)

    ' Check something is returned and set the price field to returned value.
    If Rec.RecordCount > 0 Then
    price = Rec("price")
    Else
    price = "0"
    End If
    Me.Requery

    End Sub

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb tblPrices

    In the field defendition of tblPrices, what type of field is price?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Access Help

    Type

    fieldsize

    (Oneword) in Access help for information on different ranges stored by INTEGER, DOUBLE, LONG etc.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Jan 2004
    Posts
    13
    Hi thanks for teh responce,

    the format is currency...

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Data Type

    I am not talking about the format on your form or report, the underlying defenition of the field in the table.

    If you Open tblPrices in design view,
    DataType for price is shown as currency - Is that correct?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Jan 2004
    Posts
    13
    Sorry, yes its set to Currency

    thanks

    I have also tried putting 0.00 in the format area of the field in the form an also added

    price = Format(price, "0.00")

    in the code below..

    Private Sub PartNo_AfterUpdate()

    'price = Format(price, "0.00")

    Dim sSQL As String
    Dim Rec As Recordset
    Dim db As Database

    ' Query to bring out the price, using variables in the form.
    sSQL = "SELECT DISTINCT price FROM tblPrices "
    sSQL = sSQL & "WHERE partno = '" & PartNo & "' AND CustID = '" & CustID & "';"



    Set db = CurrentDb
    Set Rec = db.OpenRecordset(sSQL)

    ' Check something is returned and set the price field to returned value.
    If Rec.RecordCount > 0 Then
    price = Rec("price")
    Else
    price = "0"
    End If
    price = Format(price, "0.00")
    Me.Requery

    End Sub

    thanks

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Form

    Check the format in the form itself.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    2
    Try this:
    ...
    Dim ShowPrice as Currency
    ....
    If Rec.RecordCount > 0 Then
    ShowPrice = Rec.Fields("price")
    MsgBox ShowPrice
    End If

    If the price is shown correct, the error is on the form, else it's in the database.

  9. #9
    Join Date
    Jan 2004
    Posts
    13
    Thanks

    The error is on the form, the price showd decemals..

    Where do I go from here?

    thanks

  10. #10
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Form

    Open the form in design view.

    Click the field Price > Then right-click properties

    Ensure that formatting is set to "Currency"
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  11. #11
    Join Date
    Jan 2004
    Posts
    13
    Thanks, I think I see the problem....

    I will let you know..

    regards

    Allen

  12. #12
    Join Date
    Jan 2004
    Posts
    13

    Thumbs up Thanks

    Thanks for all your help,

    problem is fixed, data was being added to a third table of wrong format.

    regards
    Allen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •