decimal point problem 2

[Hargreavesk]

Can anyone help?

I have a currency field called "Amount". I would like to convert the decimal point in this field to a hyphen in a field called "Amount1". My data is this £136.00 but I would like it to look like this 136-00. I am using a replace function with the following code:

Function Replace()

Dim Amount As Variant
Dim AmountLeft As String
Dim AmountRight As String
Dim Amount1 As Variant

Amount = Forms![FRM ChequeDetails]!Amount


AmountLeft = Left(Amount, InStr(Amount, ".&quot - 1) & "-"
AmountRight = Mid(Amount, InStr(Amount, ".&quot + 1)

Amount = AmountLeft & AmountRight

Forms![FRM ChequeDetails]!Amount1 = Amount

End Function

This is working fine except for data that has 00 after the decimal point. I have tried everything and am completely baffled. Can anyone help?

Regards

Kate

 

[grnzbra]

Try using Right() instead of mid(). If original number always has 2 decimals, use Right(Amount,2). When you run this, put a break point at the line where you concatinate the two sides back together, and see what the values are for the right side.

 

[StarPassing]

Change: Amount = Forms![FRM ChequeDetails]!Amount

To:
Amount = format(Forms![FRM ChequeDetails]!Amount,"######.00&quot

Tom

 

[Hargreavesk]

grnzbra, StarPassing,

Thanks for your help. StarPassing, I used yours and it works brilliantly. Thanks to both of you for replying.

Regards

Kate

)