Setting Date format irrespective of Regional Settings

JohnPapa

Registered User.
Local time
Today, 18:39
Joined
Aug 15, 2010
Messages
1,088
I have an application where the user should be able to select to use the Date format he prefers throughout the application ex dd-mm-yy, mm-dd-yy etc. In other words I would like the application to be irrespective of the Regional settings on the PC.

Is this advisable or am I asking for trouble?

There is a further complication with the decimal point, in the US it is a Period in Europe it is a Comma ex 143.23 Vs 143,23.

Any ideas on how to approach this problem?
John
 
To display a date the Format function is normally used.
In each case the Format function uses a format string as in: -

Dim strDateFormat As String

MsgBox Format(Date, "dd\/mm\/yyyy")
MsgBox Format(Date, "mm\/dd\/yyyy")
MsgBox Format(Date, "yyyy\-mm\-dd")

strDateFormat = "yyyy\-mm\-dd"
MsgBox Format(Date, strDateFormat)

MsgBox Format(Date, GetDateFormat1())
MsgBox Format(Date, GetDateFormat2())


Code:
Function GetDateFormat1() As String

    GetDateFormat1 = "yyyy\-mm\-dd"

End Function


Function GetDateFormat2() As String

    GetDateFormat2 = DLookup("DateFormat", _
                             "tblConfiguration", _
                             "UserName = " & Chr(34) & CurrentUser & Chr(34))

End Function

So you will need to have a Global method of getting the format string the user selects.
You should also have a method for the user to select, or create, a format string and save it.

The decimal separator can be handled by wrapping the floating point number in the Str() function.
This should only be required when building a string which will be executed as an SQL string.

Chris.
 
I have an application where the user should be able to select to use the Date format he prefers throughout the application ex dd-mm-yy, mm-dd-yy etc. In other words I would like the application to be irrespective of the Regional settings on the PC.
I find this kinda strange. You are saying that a user who has a US regional setting, will use your database and want to enter dates in a UK format. Really?

Is this advisable or am I asking for trouble?

It is certainly going to be a lot of coding. You are going to have to basically be the interpreter in between your User Interface (UI) and the database (DB) you are saving things in.

eg.

UI - date input > You format the date for the DB > Commit date to DB
Request from DB > Format it for User > put it in the UI

You will have to do this for all reports, forms and any out put to Word docs. excel etc.

Plus all inbuilt functions in Access / VBA cannot be use without you specially formating the date as the person would like. Also you could not use any inbuilt ISDATE for example functions as this will cause errors if a UK regional setting system has this date 05/25/2011 - because the user wants to enter it as a US date.

If you still want to do it we can explore how in another post. My approach is going to be that you use normal text fields to get input and then format it based on Users Access data format.

The Point/Comma issue is not an issue for me. If there is a number in a database - then on a DE machine it show commas and on a UK machine it is a point.

You know that in the Regional Settings you can customize this. Thus a UK reginal settings might have a comma instead of a point.

There is a further complication with the decimal point, in the US it is a Period in Europe it is a Comma ex 143.23 Vs 143,23.

Any ideas on how to approach this problem?
John
Like the dates what is the problem you have here. Access does it all for you from what I can see.

Some backgound. I have a data base which deals with at least 4 formats
UK-DE / US / JP / CN date formats. People enter based on Regional Settings and the back end database understand that it is a date and saves it in the database correctly.
 
ChrisO thanks for your input.

Darbid, the application will be used in the US, Europe and Far East so it is not uncommon for a US-based user (US Regional Settings, mm-dd-yy) to want to generate a report in European format (dd-mm-yy) or some other pre-defined format.

What you are saying and I would tend to agree, is that requiring the user to properly set his Regional Settings would be a small price to pay for all the work that Access will do for you in the background.

If the Regional Settings are set to US, then would it be advisable to use ChrisO’s suggestion of Formatting the display of the dates in a required format? Could I display on a US set machine date 04-06-11 ( 6th April 11) as 06-04-11 if the fields are formatted per ChrisO?

Regarding the decimal point/comma issue, I am guessing that this would be defined in the Regional Settings. If I need my Single values to display 5 decimal places I could not, for example, hardcode in the Format field of a Form that a field will look like 0.00000 because the decimal indicator might be a comma. Is this correct?

Can you elaborate a bit whether I can display on a US based machine (US Regional Settings) instead of the usual 1456.23 à 1456,23 (comma instead of a point) per ChrisO.

Many thanks,
John
 
Lets deal with dates first. INPUT and OUTPUT ON FORMS

I am not an expert and I do not make MDB for a living - but here is how I handle it.

First I do this to establish the users regional settings for the dates

Code:
Select Case Right(fLocaleInfo(LOCALE_ILANGUAGE), 3)

    Case 409  ' USA
        strDateType = "usdatehelper"
        strinputdatetype = "usinputdatetype"
    Case 407  ' DE
        strDateType = "dedatehelper"
        strinputdatetype = "deinputdatetype"
    Case 804 'CN
        strDateType = "cndatehelper"
        strinputdatetype = "cninputdatetype"
    Case 411 'JP
        strDateType = "jpdatehelper"
        strinputdatetype = "jpinputdatetype"
    Case Else 'everything else
        strDateType = "otherdatehelper"
        strinputdatetype = "otherinputdatetype"
End Select
Code:
Public Function fLocaleInfo(lngLCType As Long) As String
Dim lngLocale As Long
Dim strLCData As String, lngData As Long
Dim lngX As Long

    strLCData = String$(cMAXLEN, 0)
    lngData = cMAXLEN - 1
    lngX = apiGetLocaleInfo(LOCALE_USER_DEFAULT, lngLCType, _
                    strLCData, lngData)
    If lngX <> 0 Then
        fLocaleInfo = Left$(strLCData, lngX - 1)
    End If
End Function
I use a rather old idea now. I have an .ini file with keys like this. So if the user is using a US regional setting then the keys starting with "us...." will be taken from the below list. I realise this might be confusing to you but it is like this.

For an input mask I do not have to specifiy each one I use a global variable.

Likewise if I have to warn the user they have not entered a proper date I can use the _ _ datehelper key for them to say how they should enter the date.

usdatehelper=MM/DD/YYYY
dedatehelper=TT.MM.JJJJ
cndatehelper=YYYY-MM-DD
jpdatehelper=YYYY/MM/DD
otherdatehelper=DD/MM/YYYY
usinputdatetype=99/99/0000;0;_
deinputdatetype=99/90/0000;0;_
jpinputdatetype=0000/00/99;0;_
cninputdatetype=0000-00-99;0;_
otherinputdatetype=99/90/0000;0;_
Using the above I then make the input mask - eg for the us. globalvariable = usinputdatetype
Code:
Me.YOURTEXTBOX.InputMask = globalvariable
Code:
Format = ShortDate
Catch the error - if you make it Format = Shortdate and then not enter a date properly then you get an Access error. Which is stupid. So to intercept this You do the following.

Code:
Private Sub Form_Error(DataErr As Integer, response As Integer)
On Error GoTo Err_Form_Error


Dim strcurrentcontrol As Control
   
Set strcurrentcontrol = Screen.ActiveControl

   
If DataErr = 2279 Or DataErr = 2113 Then
    
    If strcurrentcontrol.Name = name_of_your_control Or name_of_your_control Or name_of_your_control Then
        MsgBox "Invalid Date"
    End If
    
    Err.Clear
    response = acDataErrContinue
    Exit Sub
End If

Exit_Form_Error:
    Exit Sub

Err_Form_Error:
    MsgBox "Form Error " & Err.Description & " - " & Err.Number
    Resume Exit_Form_Error
End Sub
 
Darbid, the application will be used in the US, Europe and Far East so it is not uncommon for a US-based user (US Regional Settings, mm-dd-yy) to want to generate a report in European format (dd-mm-yy) or some other pre-defined format.

Now this I understand. And yes this is a problem. It gets even worse if you have a German user who wants to print an english report in word and use the actual name of a month. Access will print it in German. I have a solution for that below.

Here you are going to have to have some kind of setting in your database for the users to choose either language and/or date format. Otherwise you cannot guess.

I made a decision that all reports are in English so I then could just deal with for example the German language date problems

For example I have this as a function. Thus if a German uses this

Code:
Format(dt_date, "mmm")
it will be Mrz

Thus I call this

Code:
GetEnglishMonth(Format(dt_date, "mmm"), True)
To get an english translation.

Code:
Public Function GetEnglishMonth(str_DE_Month As String, Optional bln_short As Boolean) As String
On Error GoTo Err_GetEnglishMonth


If bln_short = True Then

    Select Case str_DE_Month
    
    Case "Jan"
        GetEnglishMonth = "Jan"
    
    Case "Feb"
        GetEnglishMonth = "Feb"
    
    Case "Mrz"
        GetEnglishMonth = "Mar"
    
    Case "Apr"
        GetEnglishMonth = "Apr"
    
    Case "Mai"
        GetEnglishMonth = "May"
    
    Case "Jun"
        GetEnglishMonth = "Jun"
    
    Case "Jul"
        GetEnglishMonth = "Jul"
    
    Case "Aug"
        GetEnglishMonth = "Aug"
    
    Case "Sep"
        GetEnglishMonth = "Sep"
    
    Case "Okt"
        GetEnglishMonth = "Oct"
    
    Case "Nov"
        GetEnglishMonth = "Nov"
    
    Case "Dez"
        GetEnglishMonth = "Dec"
        
    Case Else
        GetEnglishMonth = str_DE_Month
    End Select

Else
    Select Case str_DE_Month
    
        Case "Januar"
            GetEnglishMonth = "January"
        Case "Februar"
            GetEnglishMonth = "February"
        Case "März"
            GetEnglishMonth = "March"
        Case "April"
            GetEnglishMonth = "April"
        Case "Mai"
            GetEnglishMonth = "May"
        Case "Juni"
            GetEnglishMonth = "June"
        Case "Juli"
            GetEnglishMonth = "July"
        Case "August"
            GetEnglishMonth = "August"
        Case "September"
            GetEnglishMonth = "September"
        Case "Oktober"
            GetEnglishMonth = "October"
        Case "November"
            GetEnglishMonth = "November"
        Case "Dezember"
            GetEnglishMonth = "December"
            
        Case Else
            GetEnglishMonth = str_DE_Month
    End Select
  End If
 
Regarding the decimal point/comma issue, I am guessing that this would be defined in the Regional Settings. If I need my Single values to display 5 decimal places I could not, for example, hardcode in the Format field of a Form that a field will look like 0.00000 because the decimal indicator might be a comma. Is this correct?

Can you elaborate a bit whether I can display on a US based machine (US Regional Settings) instead of the usual 1456.23 à 1456,23 (comma instead of a point) per ChrisO.

Many thanks,
John

I do not have anything set up for numbers. But for an input mask you follow the same idea that I have put in for dates. On a machine with a regional settings of POINT it will show points - on a machine with Regional settings of Comma it will show comma's. This goes too for formating in excel and word. I do not think you can or need to change this as I think you will get into problems if you do. Unlike the date problem which is in the end only text. If you try to put 1.000.000 (which is in Germany One Million) into an excel spreedsheet under the default UK regional settings, this is not going to think of it as a million.

I think you just leave this alone. I have googled whether the point / comma thing is a language thing or a preference and it is a preference. Thus there are many english speaking people that also use 1.000.000 to represent One Million. Thus people should be able to tell the difference.

Do some tests. In my experience if a German creates an excel spreedsheet with 1.000.000,56 - and then I open this with my regional settings I would see 1,000,000.56. Thus excel handles it.
 
As Darbid says, there are no problems with the decimal separator, other than in code of SQL statements, where Str can take care of that automatically. I have developed applications on UK-settings, with "." as decimal separator, and they come out just fine in locale settings with "," as separator.

As to the format "000000.00" : the format string is interpreted according to the locale setting, so no problem there either. In other words, "000.00" gives 123.45 in a locale with "." as decimal separator, and "123,45" in a locale where "," is used as decimal separator.
 
Darbid, many thanks for your input. You may be interested in the following code for retrieving, setting locale info. I found it on the Internet and I modified it slightly:

Code:
Option Explicit
Public AlCol As Boolean
Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long
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
Declare Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA" (ByVal _
locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Boolean
Declare Function GetUserDefaultLCID% Lib "kernel32" ()
Public Const LOCALE_ICENTURY = &H24
Public Const LOCALE_ICOUNTRY = &H5
Public Const LOCALE_ICURRDIGITS = &H19
Public Const LOCALE_ICURRENCY = &H1B
Public Const LOCALE_IDATE = &H21
Public Const LOCALE_IDAYLZERO = &H26
Public Const LOCALE_IDEFAULTCODEPAGE = &HB
Public Const LOCALE_IDEFAULTCOUNTRY = &HA
Public Const LOCALE_IDEFAULTLANGUAGE = &H9
Public Const LOCALE_IDIGITS = &H11
Public Const LOCALE_IINTLCURRDIGITS = &H1A
Public Const LOCALE_ILANGUAGE = &H1
Public Const LOCALE_ILDATE = &H22
Public Const LOCALE_ILZERO = &H12
Public Const LOCALE_IMEASURE = &HD
Public Const LOCALE_IMONLZERO = &H27
Public Const LOCALE_INEGCURR = &H1C
Public Const LOCALE_INEGSEPBYSPACE = &H57
Public Const LOCALE_INEGSIGNPOSN = &H53
Public Const LOCALE_INEGSYMPRECEDES = &H56
Public Const LOCALE_IPOSSEPBYSPACE = &H55
Public Const LOCALE_IPOSSIGNPOSN = &H52
Public Const LOCALE_IPOSSYMPRECEDES = &H54
Public Const LOCALE_ITIME = &H23
Public Const LOCALE_ITLZERO = &H25
Public Const LOCALE_NOUSEROVERRIDE = &H80000000
Public Const LOCALE_S1159 = &H28
Public Const LOCALE_S2359 = &H29
Public Const LOCALE_SABBREVCTRYNAME = &H7
Public Const LOCALE_SABBREVDAYNAME1 = &H31
Public Const LOCALE_SABBREVDAYNAME2 = &H32
Public Const LOCALE_SABBREVDAYNAME3 = &H33
Public Const LOCALE_SABBREVDAYNAME4 = &H34
Public Const LOCALE_SABBREVDAYNAME5 = &H35
Public Const LOCALE_SABBREVDAYNAME6 = &H36
Public Const LOCALE_SABBREVDAYNAME7 = &H37
Public Const LOCALE_SABBREVLANGNAME = &H3
Public Const LOCALE_SABBREVMONTHNAME1 = &H44
Public Const LOCALE_SCOUNTRY = &H6
Public Const LOCALE_SCURRENCY = &H14
Public Const LOCALE_SDATE = &H1D
Public Const LOCALE_SDAYNAME1 = &H2A
Public Const LOCALE_SDAYNAME2 = &H2B
Public Const LOCALE_SDAYNAME3 = &H2C
Public Const LOCALE_SDAYNAME4 = &H2D
Public Const LOCALE_SDAYNAME5 = &H2E
Public Const LOCALE_SDAYNAME6 = &H2F
Public Const LOCALE_SDAYNAME7 = &H30
Public Const LOCALE_SDECIMAL = &HE
Public Const LOCALE_SENGCOUNTRY = &H1002
Public Const LOCALE_SENGLANGUAGE = &H1001
Public Const LOCALE_SGROUPING = &H10
Public Const LOCALE_SINTLSYMBOL = &H15
Public Const LOCALE_SLANGUAGE = &H2
Public Const LOCALE_SLIST = &HC
Public Const LOCALE_SLONGDATE = &H20
Public Const LOCALE_SMONDECIMALSEP = &H16
Public Const LOCALE_SMONGROUPING = &H18
Public Const LOCALE_SMONTHNAME1 = &H38
Public Const LOCALE_SMONTHNAME10 = &H41
Public Const LOCALE_SMONTHNAME11 = &H42
Public Const LOCALE_SMONTHNAME12 = &H43
Public Const LOCALE_SMONTHNAME2 = &H39
Public Const LOCALE_SMONTHNAME3 = &H3A
Public Const LOCALE_SMONTHNAME4 = &H3B
Public Const LOCALE_SMONTHNAME5 = &H3C
Public Const LOCALE_SMONTHNAME6 = &H3D
Public Const LOCALE_SMONTHNAME7 = &H3E
Public Const LOCALE_SMONTHNAME8 = &H3F
Public Const LOCALE_SMONTHNAME9 = &H40
Public Const LOCALE_SMONTHOUSANDSEP = &H17
Public Const LOCALE_SNATIVECTRYNAME = &H8
Public Const LOCALE_SNATIVEDIGITS = &H13
Public Const LOCALE_SNATIVELANGNAME = &H4
Public Const LOCALE_SNEGATIVESIGN = &H51
Public Const LOCALE_SPOSITIVESIGN = &H50
Public Const LOCALE_SSHORTDATE = &H1F
Public Const LOCALE_STHOUSAND = &HF
Public Const LOCALE_STIME = &H1E
Public Const LOCALE_STIMEFORMAT = &H1003
 
Public Const SDecCurr = "."
Public Const SMieCurr = ","
Public Const DateFormat = "MM/dd/yyyy"
Public Const SeparatorData = "/"
Public Const Sdecimal = "."
Public Const SMie = ","
'Public Db As database
'Public Ws As Workspace
Dim locale As Variant
Dim SeparatorData_Vechi As Variant
Dim DateFormat_Vechi  As Variant
Dim SDecimal_Vechi As Variant
Dim Smie_Vechi As Variant
Dim SMieCurr_Vechi As Variant
Dim SDecCurr_Vechi As Variant
Public Function Get_locale()  ' Retrieve the regional setting
 
      Dim Symbol As String
      Dim iRet1 As Long
      Dim iRet2 As Long
      Dim lpLCDataVar As String
      Dim Pos As Integer
 
      locale = GetUserDefaultLCID()
 
 
'*****************************************************************
 
     iRet1 = GetLocaleInfo(locale, LOCALE_SDATE, lpLCDataVar, 0)
    Symbol = String$(iRet1, 0)
    iRet2 = GetLocaleInfo(locale, LOCALE_SDATE, Symbol, iRet1)
    Pos = InStr(Symbol, Chr$(0))
        If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           'MsgBox "Regional Setting = " + Symbol
      End If
    SeparatorData_Vechi = Symbol
'*******************************************************************
      iRet1 = GetLocaleInfo(locale, LOCALE_SSHORTDATE, lpLCDataVar, 0)
      Symbol = String$(iRet1, 0)
      iRet2 = GetLocaleInfo(locale, LOCALE_SSHORTDATE, Symbol, iRet1)
      Pos = InStr(Symbol, Chr$(0))
      If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           'MsgBox "Regional Setting = " + Symbol
      End If
       DateFormat_Vechi = Symbol
 
'********************************************************************
 
    iRet1 = GetLocaleInfo(locale, LOCALE_SDECIMAL, lpLCDataVar, 0)
    Symbol = String$(iRet1, 0)
    iRet2 = GetLocaleInfo(locale, LOCALE_SDECIMAL, Symbol, iRet1)
    Pos = InStr(Symbol, Chr$(0))
      If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           'MsgBox "Regional Setting = " + Symbol
      End If
    SDecimal_Vechi = Symbol
'******************************************************************
    iRet1 = GetLocaleInfo(locale, LOCALE_STHOUSAND, lpLCDataVar, 0)
    Symbol = String$(iRet1, 0)
    iRet2 = GetLocaleInfo(locale, LOCALE_STHOUSAND, Symbol, iRet1)
    Pos = InStr(Symbol, Chr$(0))
          If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           'MsgBox "Regional Setting = " + Symbol
      End If
    Smie_Vechi = Symbol
'*****************************************************************
'currency
iRet1 = GetLocaleInfo(locale, LOCALE_SMONTHOUSANDSEP, lpLCDataVar, 0)
    Symbol = String$(iRet1, 0)
    iRet2 = GetLocaleInfo(locale, LOCALE_SMONTHOUSANDSEP, Symbol, iRet1)
    Pos = InStr(Symbol, Chr$(0))
          If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           'MsgBox "Regional Setting = " + Symbol
      End If
    SMieCurr_Vechi = Symbol
 
iRet1 = GetLocaleInfo(locale, LOCALE_SMONDECIMALSEP, lpLCDataVar, 0)
    Symbol = String$(iRet1, 0)
    iRet2 = GetLocaleInfo(locale, LOCALE_SMONDECIMALSEP, Symbol, iRet1)
    Pos = InStr(Symbol, Chr$(0))
          If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           'MsgBox "Regional Setting = " + Symbol
      End If
    SDecCurr_Vechi = Symbol
 
End Function
Public Function Set_locale()  'Change the regional setting
Dim iRet As Long
iRet = SetLocaleInfo(locale, LOCALE_SDATE, SeparatorData)
iRet = SetLocaleInfo(locale, LOCALE_SSHORTDATE, DateFormat)
iRet = SetLocaleInfo(locale, LOCALE_SDECIMAL, Sdecimal)
iRet = SetLocaleInfo(locale, LOCALE_STHOUSAND, SMie)
iRet = SetLocaleInfo(locale, LOCALE_SMONTHOUSANDSEP, SMieCurr)
iRet = SetLocaleInfo(locale, LOCALE_SMONDECIMALSEP, SDecCurr)
End Function
 
Public Function Restore_Locale()
Dim iRet As Long
 
iRet = SetLocaleInfo(locale, LOCALE_SDATE, SeparatorData_Vechi)
iRet = SetLocaleInfo(locale, LOCALE_SSHORTDATE, DateFormat_Vechi)
iRet = SetLocaleInfo(locale, LOCALE_SDECIMAL, SDecimal_Vechi)
iRet = SetLocaleInfo(locale, LOCALE_STHOUSAND, Smie_Vechi)
     iRet = SetLocaleInfo(locale, LOCALE_SMONTHOUSANDSEP, SMieCurr_Vechi)
iRet = SetLocaleInfo(locale, LOCALE_SMONDECIMALSEP, SDecCurr_Vechi)
End Function

There is an additional twist to the story. The input into the software may have a user-defined date and number format. The software compares international price lists so a US-based price list may be mm-dd-yy and 0.00000 while a European price list dd-mm-yy and 0,00000. In the case where the Regional Settings are mm-dd-yy and the input is in dd-mm-yy, I believe the only way to handle this would be to define the date format and if different from the Regional Settings to convert to the correct format. Correct?

John
 
[/CODE]There is an additional twist to the story. The input into the software may have a user-defined date and number format. The software compares international price lists so a US-based price list may be mm-dd-yy and 0.00000 while a European price list dd-mm-yy and 0,00000. In the case where the Regional Settings are mm-dd-yy and the input is in dd-mm-yy, I believe the only way to handle this would be to define the date format and if different from the Regional Settings to convert to the correct format. Correct?

John

Looks like you got them all in that code. I probably started from there as well.

So these price lists - do humans see them? Does your database have to read and print them or only print them?
 
In a typical scenario, the price lists come in from telecom providers like Verizon, Fusion, Aircel in the form of an Excel spreadsheet. The spreadsheet contents are Price (Single), Effective Date (Date) etc. The spreadsheets are read by Access and imported into a Jet db for processing, comparision etc.

There is a potential problem when:
1) My Regional Settings have a date format = dd-mm-yy and the Excel has a date format of mm-dd-yy
2) My decimal separator = "," (comma) and in the Excel input the numbers have a decimal separator of "." (point)

Hope this helps,
John
 
Maybe with each Excel spreadsheet I should define the date format and the decimal separator. Based on these 2 params and the Regional Settings read the Excel in the proper manner.
 
Dont' mess with these settings. Formats do not affect content!

If the filed format in Excel is corresponding to the content, i.e. date or number, then the CONTENT of the cells is correct always, jusr the format (for display) is different. The important thing is to make sure that imported valeus are imported as the respective field types, ie. Date or Number. If they are imported as strings, then you do have a problem indeed.
 
So Spikepl, are you saying that the best way to deal with this is to make sure that the Regional Settings are set to whatever format the user wants to work with and if the user is going to import dates and numbers from an Excel spreadsheet, as long as these dates and numbers are imported into the Jet db as dates and numbers, there should be no problem?

And Formatting can be used for specific appearance.
 
There is a potential problem when:
1) My Regional Settings have a date format = dd-mm-yy and the Excel has a date format of mm-dd-yy
If the excel cells that contain dates are formated cells of type DATE then there is no problem - from my knowledge. Excel will change things for you. Thus if you have a cell with format date - and add 10/24/2011 on a US Regional settings. Then you open this excel in a UK regional settings it will show 24/10/2011.

IF the telco does not format the cells to a type such as DATE then you must know what format it was entered and deal with that. In any case to enter into Jet you always format it to an US date.

Of course the way to do away with all this is to write dates with YYYY-MM-DD which is universally understood by computers.

2) My decimal separator = "," (comma) and in the Excel input the numbers have a decimal separator of "." (point)
Same thing as I said above. Excel will handle this for you.

Excel will show you the number based on your regional settings not based on the regional settings of the machine where the excel was originally opened and data was entered. I would here set the excel cell value to a variable type decimal. Then add this variable to the JET SQL etc. This will then make sure it understand formats.
 
What if the Excel date field is formatted as Text? Then there is a problem in reading this value into a Date field in Jet.

I have a feeling that I would need to define the Date format of the Excel if it is not defined. One workaround would be to format the Excel column containing the dates to Date. Since the Price List has maybe 20000 entries I can figure out the sequence of Day, Month, Year.
 
Yes that is what I and Darbid are saying.

I think you got yourself utterly confused. A format is a feature for humans. For the machine, internally, the data are the way they are, and are unaffected by which locale they were created in or by formats. But the DISPLAy of them is of course affected.
 
An Excel-datefield is either an Excel Date field or it isn't. If dates are stored as text, there is a difference, because a text does not carry the information as to in which locale it was created - the human does. You should convert these to a date field. If the dates were written in another locale than the one in which you are operating, then do some fiddling with excel string and date functions.
 
And BTW: you cannot "format" a date field as text. You can format the date field to display the textual value of a date in various ways, but the data contained in the field is unaffected. Or you can store a date as text. Format affects only display of a value, not the value itself.
 
I had a look at some of the Price Lists and "anything goes".

Also the Effective Date of an Excel may be the same for all 20K entries and so you cannot be sure.

I could check whether the date column is formatted as Date, if not ask the user to enter the actual Date format. This would solve the problem, I believe.

Many thanks for all your help.
 

Users who are viewing this thread

Back
Top Bottom