Formatting Currency

[ledmark]

Hello - I have been looking but cannot find how to format the currency so that no matter what you type in the field, the decimal is automatically put in. For example - if I type in 255 in a field it registers as $2.55, or 10167 as $101.67. Is this a hard thing to do?

Would appreciate any help or at least direction in where to look.

Thank you
Laura Edmark

 

[HiTechCoach]

Off the top of my head:

In the after update event use:

If Instr(Me[MyControlName],".") = 0 then 
   Me[MyControlName] = Me[MyControlName] / 100
end if

In the on enter event, you may need to reverse it with:

   Me[MyControlName] = Me[MyControlName] * 100

 

[wazz]

if it's a currency data type in the table, you can set the decimal places property to 2.

 

[ledmark]

Thank you both for your help. Much appreciated.

Laura

 

[ledmark]

OK - I've tried both solutions and neither one has worked. I set the decimal point to 2 in the table but it's not doing what I'm wanting.

When I write the code for this do I put it in the forms after update event or the controls? And by [MyControlName] are you referring to the name of the field in the table? After writing this and then trying it I entered 400, which should have given me $4.00, and it gave me $0.04.

This is the code I wrote:

Private Sub Form_AfterUpdate()
If InStr(Me.Cost, ".") = 0 Then
Me.Cost = Me.Cost / 100
End If

End Sub

Then you say to enter Me.Cost = Me.Cost * 100 in the On Enter event but there is no such event??

Any suggestions?

Thanks
Laura

 

[Scooterbug]

I'm guessing what you are tying to do is to do data entry, but in order to speed it up you just want to type in the numbers and let the database put the decimal point in.

On the After Update event of the field that you are inputing the data, just put in:

me.cost = me.cost / 100

 

[gemma-the-husky]

in any display field, set the formatting bits to fixed (possibly currency) and deciamls to 2

then it should show as 12.50 or £12.50 with a currency symbol

is that what you mean?

 

[HiTechCoach]

OK - I've tried both solutions and neither one has worked. I set the decimal point to 2 in the table but it's not doing what I'm wanting.

When I write the code for this do I put it in the forms after update event or the controls? And by [MyControlName] are you referring to the name of the field in the table? After writing this and then trying it I entered 400, which should have given me $4.00, and it gave me $0.04.

This is the code I wrote:

Private Sub Form_AfterUpdate()
If InStr(Me.Cost, ".") = 0 Then
Me.Cost = Me.Cost / 100
End If

End Sub

Then you say to enter Me.Cost = Me.Cost * 100 in the On Enter event but there is no such event??

Any suggestions?

Thanks
Laura

Laura,

... by [MyControlName] are you referring to the name of the field in the table?

In Access, Control are on forms, field are in tables. So MyControlName is the name of a control in the form that can be bound to a field in a table. By default, Access will name a control the name of the bound field in some instances. I always make sure the the control name does NOT match the field name. I find this keeps things clear and less confusing.


Also, The code I suggest is for the events of the control, not the form.

 

[ledmark]

Thank you for all the responses - I'll keep working on it and let you know what happens.

Laura

 

[wazz]

hi, i've tried setting the decimal places to 2 and also left it on Auto and both add the decimal places. maybe the control isn't reading the data type correctly (assuming it's currency). try replacing the control, might get lucky. or maybe an input mask is getting in the way?

 

[gemma-the-husky]

i see what the OP wants now

i think if you want to INPUT 255, but have it show as 2.55, you have to manage it yourself in some way

A number IS A NUMBER, and the number you are entering is 255 (with an implied decimal point at the end 255.0) - irrespective of formats etc - this isnt like a calculator that can do automatic dps.

you could set an input mask, with a period IN IT, so you enter the fraction AFTER the period - but then you are technically entering the number as 2.55, although access is helping you do this.

----------
its the same with percentage fields - if you format as percentage, and enter 1, you get 100% - you have to enter 1% (ie with the symbol) to do it correctly.