Problem with Euro formatting

[brucedodds]

The system I support exists to generate invoices to send to European
clients. Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: €123.456,78

We have been generating these by remoting in to a dedicated PC with
its Windows Regional setting set to German. For various reasons we
would much prefer not to have to do this.

I've been trying to devise a format string that will display a number
in the format shown above on a PC with Regional setting set to US.
I'm not having luck. Has anyone succeeded in doing this?

TIA,

Bruce
Access 2003, Win XP

[Tom van Stiphout]

On Thu, 16 Oct 2008 14:46:26 -0700 (PDT), br********@comc ast.net
wrote:

It may be best to write your own ConvertToEuro function, using regular
string manipulation functions. RegEx gurus may have other options.

-Tom.
Microsoft Access MVP

>The system I support exists to generate invoices to send to European
clients. Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: €123.456,78

We have been generating these by remoting in to a dedicated PC with
its Windows Regional setting set to German. For various reasons we
would much prefer not to have to do this.

I've been trying to devise a format string that will display a number
in the format shown above on a PC with Regional setting set to US.
I'm not having luck. Has anyone succeeded in doing this?

TIA,

Bruce
Access 2003, Win XP

[brucedodds]

On Oct 16, 11:24*pm, Tom van Stiphout <tom7744.no.s.. [e-mail]:

On Thu, 16 Oct 2008 14:46:26 -0700 (PDT), brucedo...@comc ast.net
wrote:

It may be best to write your own ConvertToEuro function, using regular
string manipulation functions. RegEx gurus may have other options.

-Tom.
Microsoft Access MVP

The system I support exists to generate invoices to send to European
clients. *Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: €123.456,78

We have been generating these by remoting in to a dedicated PC with
its Windows Regional setting set to German. *For various reasons we
would much prefer not to have to do this.

I've been trying to devise a format string that will display a number
in the format shown above on a PC with Regional setting set to US.
I'm not having luck. Has anyone succeeded in doing this?

TIA,

Bruce
Access 2003, Win XP

Thanks for the suggestion. Do you mean building a string for display,
rather than using Format() to format the number?

[Tom van Stiphout]

On Fri, 17 Oct 2008 03:40:43 -0700 (PDT), br********@comc ast.net
wrote:

That's right. I read the details of the Format function, and it cannot
switch the thousand separator or the decimal point. Therefore the
suggestion to just write the darn thing yourself.

-Tom.
Microsoft Access MVP

>On Oct 16, 11:24*pm, Tom van Stiphout <tom7744.no.s.. [e-mail]:

>On Thu, 16 Oct 2008 14:46:26 -0700 (PDT), brucedo...@comc ast.net
wrote:

It may be best to write your own ConvertToEuro function, using regular
string manipulation functions. RegEx gurus may have other options.

-Tom.
Microsoft Access MVP

>The system I support exists to generate invoices to send to European
clients. *Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: €123.456,78

>We have been generating these by remoting in to a dedicated PC with
its Windows Regional setting set to German. *For various reasons we
would much prefer not to have to do this.

>I've been trying to devise a format string that will display a number
in the format shown above on a PC with Regional setting set to US.
I'm not having luck. Has anyone succeeded in doing this?

>TIA,

>Bruce
Access 2003, Win XP

Thanks for the suggestion. Do you mean building a string for display,
rather than using Format() to format the number?

[brucedodds]

On Oct 17, 9:23*am, Tom van Stiphout <tom7744.no.s.. [e-mail]:

On Fri, 17 Oct 2008 03:40:43 -0700 (PDT), brucedo...@comc ast.net
wrote:

That's right. I read the details of the Format function, and it cannot
switch the thousand separator or the decimal point. Therefore the
suggestion to just write the darn thing yourself.

-Tom.
Microsoft Access MVP

On Oct 16, 11:24*pm, Tom van Stiphout <tom7744.no.s.. [e-mail]:

On Thu, 16 Oct 2008 14:46:26 -0700 (PDT), brucedo...@comc ast.net
wrote:

It may be best to write your own ConvertToEuro function, using regular
string manipulation functions. RegEx gurus may have other options.

-Tom.
Microsoft Access MVP

The system I support exists to generate invoices to send to European
clients. *Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: €123.456,78

We have been generating these by remoting in to a dedicated PC with
its Windows Regional setting set to German. *For various reasons we
would much prefer not to have to do this.

I've been trying to devise a format string that will display a number
in the format shown above on a PC with Regional setting set to US.
I'm not having luck. Has anyone succeeded in doing this?

TIA,

Bruce
Access 2003, Win XP

Thanks for the suggestion. *Do you mean building a string for display,
rather than using Format() to format the number?- Hide quoted text -

- Show quoted text -

Well, it's better to know. Thanks.

[Sky]

<br********@com cast.netwrote in message
news:0d******** *************** ***********@y79 g2000hsa.google groups.com...

The system I support exists to generate invoices to send to European
clients. Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: €123.456,78

How about something like this:

"€" &
Replace(Replace (Replace(Format (123456.78,"#,# ##.00"),".","_" ),",","."),"_", ",")

which displays as €123.456,78

- Steve

[Sky]

"Sky" <s.young @ stanley associates . comwrote in message
news:4B******** ********@nwrddc 01.gnilink.net. ..

<br********@com cast.netwrote in message
news:0d******** *************** ***********@y79 g2000hsa.google groups.com...

>The system I support exists to generate invoices to send to European
clients. Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: ?123.456,78

How about something like this:

"?" &
Replace(Replace (Replace(Format (123456.78,"#,# ##.00"),".","_" ),",","."),"_", ",")

which displays as ?123.456,78

- Steve

Oops, the Euro symbol got converted to a question mark, but you get the
idea.

- Steve

[Phil Stanton]

Try

Public Function CashToEuro(Amou nt As Currency) As String

Dim TxtAmount As String
Dim i As Integer, j As Integer
Dim OverThousand As Boolean

TxtAmount = CStr(Amount)
i = InStr(TxtAmount , ".")
If i 0 Then ' Decimal found
TxtAmount = Left(TxtAmount, i - 1) & "," & Right(TxtAmount ,
Len(TxtAmount) - i)
If Len(TxtAmount) 6 Then ' Over 1000
OverThousand = True
End If
End If

If i = 0 Then ' No Decimal
TxtAmount = TxtAmount & ",00"
If Len(TxtAmount) 3 Then ' Over 1000
OverThousand = True
End If
End If

If OverThousand = True Then
i = InStrRev(TxtAmo unt, ",") ' Find the decimal point
i = i - 4
TxtAmount = Left(TxtAmount, i) & "." & Right(TxtAmount ,
Len(TxtAmount) - i)
End If

CashToEuro = Chr(128) & TxtAmount

End Function
You will probably need to right align the output field

Phil
"Sky" <s.young @ stanley associates . comwrote in message
news:TC******** *********@nwrdd c01.gnilink.net ...

"Sky" <s.young @ stanley associates . comwrote in message
news:4B******** ********@nwrddc 01.gnilink.net. ..

><br********@co mcast.netwrote in message
news:0d******* *************** ************@y7 9g2000hsa.googl egroups.com...

>>The system I support exists to generate invoices to send to European
clients. Currency values in these reports must be represented as
Euros with European conventions (comma for decimal; dot for thousands
separator). Example: ?123.456,78

How about something like this:

"?" &
Replace(Replac e(Replace(Forma t(123456.78,"#, ###.00"),".","_ "),",","."),"_" ,",")

which displays as ?123.456,78

- Steve

Oops, the Euro symbol got converted to a question mark, but you get the
idea.

- Steve