Why does access fill my decimal places with 0's when I know there are real numbers? (1 Viewer)

[andy_dyer]

Hi,

I have an exchange rate field which is calculated to ten decimal places.

This is looked up from a date field and a currency field via a select query which when I test that shows to 10 correct full decimal places.

When I try and show this calculated exchange rate in my form it gives me 10 decimal places but after the first four it just gives 0's when i know there are actuals there...

I've tried various formats including General Number, Fixed etc and currently have it set as 0.0000000000 and my decimal places setting as 10.

I've even currently got my control source as

=FormatNumber(ExchangeRate([Start Date],[CurrencyID]),10)

But yet it still doesn't show properly...

What am I doing wrong??

Any suggestions would save me alot of time... I think I'v tried just about every setting combo I can think of or find suggested on here in other posts...

 

[Galaxiom]

Is the return value from the ExchangeRate function designated as a Double datatype?

 

[andy_dyer]

Thanks it was originally Currency but is now changed to Double and still displays as before...

 

[boblarson]

Do you have any format or input mask set on that text box? If so, get rid of the input mask as you don't need it and get rid of one of the formatting. Either use the formatting in the control source or in the text box's properties, but not both.

 

[Pat Hartman]

How is the column defined in the table? Long integer is the default numeric data type.

Unless I know I need more than four decimal digits of precision, I use the currency data type for all non-integer numeric fields to avoid floating point errors.

See "when Access math doesn't add up" at www.fmsinc.com

 

[andy_dyer]

When I went back to look I looked and noticed that when i changed from currency to double I missed one currency reference... when I changed this it now appears to work so thought I share my working code...

Thanks for your help everyone

My exchange rate function;

Public Function ExchangeRate(DateValue As Date, CurrencyID As Integer) As Double

'Returns Exchange Rate for values supplied
'-1 returned if not found

Dim dblExchangeRate As Double
Dim qd As QueryDef
Dim rst As Recordset

On Error Resume Next ' We have to go through with this come what may!

Set qd = CurrentDb.QueryDefs("qselExchangeRateForDateAndCurrency")

With qd

.Parameters(0).Value = CurrencyID
.Parameters(1).Value = DateValue
Set rst = .OpenRecordset

With rst

If .BOF And .EOF Then
dblExchangeRate = -1
Else
dblExchangeRate = .Fields(0).Value
End If

End With

End With

ExchangeRate = dblExchangeRate

End Function