ACCESS FEED: [MS_AccessPros] RE: dots and commas when importing a spreadsheet

Senin, 18 November 2013

[MS_AccessPros] RE: dots and commas when importing a spreadsheet

Onno,

which decimal seperator is "right" can start a (flame) war ;-) However if I might rephrase your question to: how can one find out which decimal (or thousands) separator is set then (I'll bet this gets messed up):

    Const LOCALE_SDECIMALSEP     As Long = &H16
    Const LOCALE_STHOUSANDSEP    As Long = &H17

    Dim LCID    As Long

    LCID = GetSystemDefaultLCID()

    ' DecimalSeparator = GetUserLocaleInfo(LCID, LOCALE_SDECIMALSEP)
    ' ThousandsSeparator = GetUserLocaleInfo(LCID, LOCALE_STHOUSANDSEP)

----------------------Module_Code-------------------------
Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long

Private Declare Function GetLocaleInfo Lib "kernel32" Alias _
        "GetLocaleInfoA" (ByVal Locale As Long, _
        ByVal LCType As Long, _
        ByVal lpLCData As String, _
        ByVal cchData As Long) As Long

Function GetUserLocaleInfo(ByVal dwLocaleID As Long, ByVal dwLCType As Long) As String

    Dim sReturn As String
    Dim Result As Long

    Result = GetLocaleInfo(dwLocaleID, dwLCType, sReturn, Len(sReturn)) 'returns the length of space needed for the answer, had we asked for the answer

    If Result Then
        sReturn = Space$(Result)
        Result = GetLocaleInfo(dwLocaleID, dwLCType, sReturn, Len(sReturn)) 'now returns the answer
        If Result Then
            GetUserLocaleInfo = Left$(sReturn, Result - 1) 'The last character is a Chr$(0) which signals end of string in C
        End If
    End If

End Function

---In ms_access_professionals@yahoogroups.com, <onno.knol@...> wrote:

Thanks John,

this seems to be a good approach, I will start with that.

But, just one comment. I think there may be a problem when the incoming number contains both a dot and a comma. I guess your code just replaces the dots for commas and vice versa. But the result should not contain a comma at all. I think the approach should be : 1) check whether both a comma and a dot are present.2) if yes, delete all but the last one, and 3) replace the last one for a dot (if needed)

greetings and thanks, Onno

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (4)

Recent Activity: