Formatting of Euro Currency

[dgulliver]

Hi,

I have a MS Access 2010 application which works perfectly when using the currency format ###,###,###.00, however on a PC with German Locale settings where the Euro Currecy format is ###.###.###,00 I'm having a problem converting . The problem occurs when I try and execute this query:

rst2.Open "SELECT Col1 FROM SomeTable WHERE ID = 1 AND Col2 >= " & ME.txtPurchasePrice & " ORDER BY Col2 ", CurrentProject.Connection, adOpenStatic, adLockReadOnly

ME.txtPurchasePrice is bound to a fields


I've tried converting using the following:

    Function E2UK(x As String) As Double
    
    Dim i As Integer
    Dim a As String
    
    For i = 1 To Len(x)
        If Mid(x, i, 1) = "," Then
            a = a & "."
        Else
            If Mid(x, i, 1) = "." Then
                a = a & ","
            Else
                a = a & Mid(x, i, 1)
            End If
        End If
    Next i
    E2UK = CDbl(a)
   
   End Function

But this this drops the decimal so if I pass in 999,99 "a" does get correctly formated as 999.99 but this line E2UK = CDbl(a) drops the decimal so it returns as 99999.

I have also tried this but x still formatted as 999,99

    x = Format(CcyToConvert, "###,###,###.00")

Cheers
Darren

 

[ByteMyzer]

You might try this code instead:

[COLOR="Navy"]Function[/COLOR] E2UK([COLOR="Navy"]ByVal[/COLOR] x [COLOR="Navy"]As String[/COLOR]) [COLOR="Navy"]As Double

    Dim[/COLOR] d [COLOR="DarkGreen"]' Decimal Placeholder[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] t [COLOR="DarkGreen"]' Thousand Placeholder[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] c [COLOR="DarkGreen"]' Currency Test String[/COLOR]

    [COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As Long[/COLOR] [COLOR="DarkGreen"]' String Position

    ' Generate test Currency string to extrapolate the
    ' User's Locale Decimal and Thousand placeholders[/COLOR]
    c = Format(123456 / 100, "Currency")
    d = Mid(c, 7, 1)
    t = Mid(c, 3, 1)

    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] Len(x)
        [COLOR="Navy"]Select Case[/COLOR] Mid(x, i, 1)
            [COLOR="Navy"]Case[/COLOR] "," [COLOR="DarkGreen"]' Euro Decimal[/COLOR]
                [COLOR="Navy"]Mid[/COLOR](x, i, 1) = d
            [COLOR="Navy"]Case[/COLOR] "." [COLOR="DarkGreen"]' Euro Thousand[/COLOR]
                [COLOR="Navy"]Mid[/COLOR](x, i, 1) = t
        [COLOR="Navy"]End Select
    Next[/COLOR] i

    E2UK = [COLOR="Navy"]CDbl[/COLOR](x)

[COLOR="Navy"]End Function[/COLOR]