Access Rounding problem

[allenEd]

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&quot
Else
price = "0"
End If
Me.Requery

End Sub

 

[jrbarnett]

I presume the "Price" variable is a field on the form. If so, I would just set its format to 0.00 which will give you the extra zeros on the end.
If it is not, and it is a variable that is not declared, you can do this yourself by putting

price = format (price, "0.00&quot

after the "End If" line and before Me.Requery.

John

 

[carlp69]

What Data-Type have you set for price in the database?

 

[allenEd]

Thanks for the responce...

The price data type is currency, CustID and PartNo are text.

I tried putting 0.00 in the format "price" field of the form which shows the results, and also adding price = format (price, "0.00&quot

but I get "type mismatch" on line
sSQL = "SELECT DISTINCT price FROM tblPrices "

thanks

 

[carlp69]

What is the price variable declared as?

 

[allenEd]

I dont think Its declared
(sorry to sound dumd - not my area of expertise)

 

[jrbarnett]

carlp69 and allenEd - from what I gather, "price" is a field on the form.

John