Problem with Euro formatting

[brucedodds@comcast.net]

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]

Re: Problem with Euro formatting

On Thu, 16 Oct 2008 14:46:26 -0700 (PDT), brucedodds@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@comcast.net]

Re: Problem with Euro formatting

On Oct 16, 11:24 pm, Tom van Stiphout <tom7744.no.s.. .@cox.netwrote:

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]

Re: Problem with Euro formatting

On Fri, 17 Oct 2008 03:40:43 -0700 (PDT), brucedodds@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.. .@cox.netwrote:

>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@comcast.net]

Re: Problem with Euro formatting

On Oct 17, 9:23 am, Tom van Stiphout <tom7744.no.s.. .@cox.netwrote:

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.. .@cox.netwrote:

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]

Re: Problem with Euro formatting

<brucedodds@com cast.netwrote in message
news:0d6e4937-b7d9-4c73-81e2-97b415112f8f@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(Replace (Replace(Format (123456.78,"#,# ##.00"),".","_" ),",","."),"_", ",")

which displays as €123.456,78

- Steve

[Sky]

Re: Problem with Euro formatting

"Sky" <s.young @ stanley associates . comwrote in message
news:4B6Kk.1784 $Rx2.879@nwrddc 01.gnilink.net. ..

<brucedodds@com cast.netwrote in message
news:0d6e4937-b7d9-4c73-81e2-97b415112f8f@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(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]

Re: Problem with Euro formatting

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:TC6Kk.1786 $Rx2.1407@nwrdd c01.gnilink.net ...

"Sky" <s.young @ stanley associates . comwrote in message
news:4B6Kk.1784 $Rx2.879@nwrddc 01.gnilink.net. ..

><brucedodds@co mcast.netwrote in message
>news:0d6e493 7-b7d9-4c73-81e2-97b415112f8f@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
>
>