currency format

Can someone help me out with formatting a currency text field.

i found this code:

me.Text = Format(val(me.text), "\\£###,###,###0.00")

Which does format the value to the correct format. This is in the lostFocus event handler.
If i type 1000, it changes it to £1,000.00
But if i type £1,000.00, it changes the value to £0.00.

How can i check to see if the text is already in the correct format?

You can do something like this:

if Left(me.text, 1) <> "£" then
  me.Text = Format(val(me.text), "\\£###,###,###0.00")
end if

Try to dump everything except the numbers and the “.”, then make it a number first.
Also look at the mask property of the textfield. See if you can use that to limit what people can enter.

Thanks guys. Managed to sort it out now.
For future reference:

[code] dim i as String

if Left(me.text, 1) = “£” then
i = me.Text.right(me.Text.Len-1)
end if

i = i.ReplaceAll(",", “”)

me.Text = Format(val(i), “\£###,###,###0.00”)[/code]

When you do val(me.text) the value method hits the £ char., decides this is not a number, and returns a value of 0. For best results with whatever is entered in your field, use the mask property as Dirk suggested.

Your post came in while I was typing. But what if the left most character is a $ or a %? For best results do have a look at the mask property.

Thats a good point.
How do i get the mask working with a £ symbol?

tried £###,###,### but isnt working

Have a look at the LR:

“Mask escape character.
Treats the next character in the mask as a literal. The escape character enables you to use the ‘#’, ‘&’, ‘A’, ‘?’ (and so on) characters in the mask. The escaped character is treated as a literal (formatting) character. For example, the mask “\C\C-9999” accepts the entry “1234” and returns “CC-1234”.”

Here is a helper function to do that…

Function Unformat(s As String) As Double
  Dim i As Integer
  Dim ch As String
  Dim result() As String
  for i = 1 to Len(s)
    ch = Mid(s, i, 1)
    if IsNumeric(ch) or (ch = ".") then 
    end if
  next i 
  return Val(Join(result, ""))
End Function

Thanks Alwyn.
Roger - That doesnt work properly. Using an input mask of “\C\C-9999” requires you to type a capital C before you can type anything else.

How about filtering the input at keydown or keypress, keeping only numeric characters and decimal separators of your choice (or one defined by system defaults, and doing the formatting at lost focus?

You may also want to allow delete, backspace and other such keys depending on your specific needs.

I want to show profit / loss in a text field.

How do I handle negative values whilst using the currency format above ?


and the following posts after this one.