VB, Regional Settings, and Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: VB, Regional Settings, and Access

Hybrid View

  1. #1
    Greg Rothlander Guest

    VB, Regional Settings, and Access



    The format of Date/Time settings in Access are dependant on the regional
    settings in Windows. The same goes for the NOW command in VB. Try using
    NOW and then change your regional settings then run NOW again... NOW is now
    different.

    When using VB how can I ask Access, Windows, or VB what format is being used?
    I could handle this by telling Access not to use the regional date/time
    settings. I don't know how to do either of these or if they are even possible.


    I'm looking for a simple API call or something in VB such as now.format?


    Thanks,
    Greg

    Here is a little background in order to understand why I can not force the
    users to a given regional and/or application setting.

    I am having problems with users in other countries making changes to their
    regional settings and then the date/time format in the Access datebase changes.
    VB is not able to correctly determine date formats such as 01/02/01. Is
    it Jan 02, 2001 or Feburary 1st, 2001. In some countries they really get
    odd and use (yy.mm.dd)... So then what is 01.02.02?

    When my application applies a users date/time format they can set within
    the application... there is no way to determine format Access is using without
    knowing the regional date/time setting used in Windows.

    I do have the option of removing the date/time setting in Access and just
    use a string that I can control in my VB code. This would require a little
    more coding that I'd like, but I can go this route if necessary.

  2. #2
    Arthur Wood Guest

    Re: VB, Regional Settings, and Access


    Greg,

    If it is realy important to display dates in a standard form, regardless
    of the Regional setting, you can use Format$(Now,"<set the format you want>")
    and this will automatically adjust to whatever is set in regional setting.,
    and display with a consistant format.

    Arthur Wood

    "Greg Rothlander" <grothlander@anteon.com> wrote:
    >
    >
    >The format of Date/Time settings in Access are dependant on the regional
    >settings in Windows. The same goes for the NOW command in VB. Try using
    >NOW and then change your regional settings then run NOW again... NOW is

    now
    >different.
    >
    >When using VB how can I ask Access, Windows, or VB what format is being

    used?
    > I could handle this by telling Access not to use the regional date/time
    >settings. I don't know how to do either of these or if they are even possible.
    >
    >
    >I'm looking for a simple API call or something in VB such as now.format?
    >
    >
    >Thanks,
    >Greg
    >
    >Here is a little background in order to understand why I can not force the
    >users to a given regional and/or application setting.
    >
    >I am having problems with users in other countries making changes to their
    >regional settings and then the date/time format in the Access datebase changes.
    > VB is not able to correctly determine date formats such as 01/02/01. Is
    >it Jan 02, 2001 or Feburary 1st, 2001. In some countries they really get
    >odd and use (yy.mm.dd)... So then what is 01.02.02?
    >
    >When my application applies a users date/time format they can set within
    >the application... there is no way to determine format Access is using without
    >knowing the regional date/time setting used in Windows.
    >
    >I do have the option of removing the date/time setting in Access and just
    >use a string that I can control in my VB code. This would require a little
    >more coding that I'd like, but I can go this route if necessary.



  3. #3
    Greg Rothlander Guest

    Re: VB, Regional Settings, and Access


    I'm not sure that I am following you. Are you saying that I can format NOW
    or are you saying that I can format what is returned from now? Such as:

    txtDisplayDate = format(now, "M/D/YYYY HH:MM AM/PM")

    If I can format NOW, then that may work. If you are saying that I can format
    what is returned from NOW then that will not give me what I need.

    Yes that would work in most situations but what I'm doing is a little more
    of a pain. The solution maybe as simple as this but I don't think I can
    do it this way because I pulling an unknown format from Access and applying
    a user defined date/time format.

    Do you think that I'm missing something here? I may be looking right pass
    something very obvious.


    Here is why I think that will not work for me...

    The trick is that my application has a date/time setting similar to the regional
    settings. This is so Mexican government can send files to American government
    in American goverment format, but the Spanish installation of Windows98 doesn't
    have a setting for MM/DD/YYYY. The main problem is that VB doesn't have
    any idea what DD/MMM/AAAA is. This is the Spanish Windows date format. There
    are numerous other reasons why the application needs a date/time setting.

    The problem I'm having is that I read the date from Access and then format
    it using the setting selected by the user (this is the applications date/time
    setting and not the regional setting), but I do not know what format the
    date was saved in. I do know it was saved in the regional settings format
    but I do not know what the format is so my global format may misread the
    date and swap months and days. What I need is a way to read the regional
    settings and then format the date to the regional setting. Then format the
    date to the user setting. This way I can always insure that I am display
    the date correctly and in the format the user used to write the date into
    the Access database.

    The only thing that I can think of would be to stop using the Access Date/Time
    settings and just us strings. This would require a great deal of reworking
    the code so I'm trying to look at other options first.

    I would like to find a simple way to get the format of the regional settings
    or now if possible.


    Thanks for your help,
    Greg Rothlander


    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Greg,
    >
    > If it is realy important to display dates in a standard form, regardless
    >of the Regional setting, you can use Format$(Now,"<set the format you want>")
    >and this will automatically adjust to whatever is set in regional setting.,
    >and display with a consistant format.
    >
    >Arthur Wood
    >
    >"Greg Rothlander" <grothlander@anteon.com> wrote:
    >>
    >>
    >>The format of Date/Time settings in Access are dependant on the regional
    >>settings in Windows. The same goes for the NOW command in VB. Try using
    >>NOW and then change your regional settings then run NOW again... NOW is

    >now
    >>different.
    >>
    >>When using VB how can I ask Access, Windows, or VB what format is being

    >used?
    >> I could handle this by telling Access not to use the regional date/time
    >>settings. I don't know how to do either of these or if they are even possible.
    >>
    >>
    >>I'm looking for a simple API call or something in VB such as now.format?
    >>
    >>
    >>Thanks,
    >>Greg
    >>
    >>Here is a little background in order to understand why I can not force

    the
    >>users to a given regional and/or application setting.
    >>
    >>I am having problems with users in other countries making changes to their
    >>regional settings and then the date/time format in the Access datebase

    changes.
    >> VB is not able to correctly determine date formats such as 01/02/01.

    Is
    >>it Jan 02, 2001 or Feburary 1st, 2001. In some countries they really get
    >>odd and use (yy.mm.dd)... So then what is 01.02.02?
    >>
    >>When my application applies a users date/time format they can set within
    >>the application... there is no way to determine format Access is using

    without
    >>knowing the regional date/time setting used in Windows.
    >>
    >>I do have the option of removing the date/time setting in Access and just
    >>use a string that I can control in my VB code. This would require a little
    >>more coding that I'd like, but I can go this route if necessary.

    >



  4. #4
    Douglas J. Steele Guest

    Re: VB, Regional Settings, and Access

    Dates aren't stored with a format: they're stored as 8 byte floating
    numbers, where the integral part of the number represents the number of days
    relative to Dec 30, 1899, and the decimal part represents time as a fraction
    of a day (i.e.: 6:00 AM is .25, Noon is .5, 6:00 PM is .75, and so on)

    The only place the format matters is when you're using it in SQL statements,
    and there it must be in mm/dd/yyyy format, regardless of what the user's
    regional settings have the short date format set to. (There are some
    exceptions to this: as long as it's an unambiguous format, Access won't have
    a problem, but if your regional settings are dd/mm/yyyy, and you give it
    09/12/2000 today, it's going to interpret that as Sept. 12, not as Dec. 09)

    Basically, if you don't fiddle with the formats, it should always display in
    whatever the user's regional settings have for short date. As long as you
    manipulate the format in your SQL statements to explicitly make it
    mm/dd/yyyy, you should be okay.

    If you really want to retrieve the regional settings, Dev Ashish has sample
    code at http://www.mvps.org/access/api/api0017.htm on "The Access Web".
    While you're there, check out
    http://www.mvps.org/access/datetime/date005.htm for a relatively nifty way
    of formatting your dates in SQL strings, courtesy of Joe Foster.

    Whatever you do, DON'T try changing the user's settings, or forcing them to
    use a particular format. I once worked (strictly as a user) with a Lotus
    Notes app that refused to accept my default yyyy/mm/dd style, and it drove
    me crazy!

    HTH

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "Greg Rothlander" <grothlander@anteon.com> wrote in message
    news:3a315e66$1@news.devx.com...
    >
    > I'm not sure that I am following you. Are you saying that I can format

    NOW
    > or are you saying that I can format what is returned from now? Such as:
    >
    > txtDisplayDate = format(now, "M/D/YYYY HH:MM AM/PM")
    >
    > If I can format NOW, then that may work. If you are saying that I can

    format
    > what is returned from NOW then that will not give me what I need.
    >
    > Yes that would work in most situations but what I'm doing is a little more
    > of a pain. The solution maybe as simple as this but I don't think I can
    > do it this way because I pulling an unknown format from Access and

    applying
    > a user defined date/time format.
    >
    > Do you think that I'm missing something here? I may be looking right pass
    > something very obvious.
    >
    >
    > Here is why I think that will not work for me...
    >
    > The trick is that my application has a date/time setting similar to the

    regional
    > settings. This is so Mexican government can send files to American

    government
    > in American goverment format, but the Spanish installation of Windows98

    doesn't
    > have a setting for MM/DD/YYYY. The main problem is that VB doesn't have
    > any idea what DD/MMM/AAAA is. This is the Spanish Windows date format.

    There
    > are numerous other reasons why the application needs a date/time setting.
    >
    > The problem I'm having is that I read the date from Access and then format
    > it using the setting selected by the user (this is the applications

    date/time
    > setting and not the regional setting), but I do not know what format the
    > date was saved in. I do know it was saved in the regional settings format
    > but I do not know what the format is so my global format may misread the
    > date and swap months and days. What I need is a way to read the regional
    > settings and then format the date to the regional setting. Then format

    the
    > date to the user setting. This way I can always insure that I am display
    > the date correctly and in the format the user used to write the date into
    > the Access database.
    >
    > The only thing that I can think of would be to stop using the Access

    Date/Time
    > settings and just us strings. This would require a great deal of

    reworking
    > the code so I'm trying to look at other options first.
    >
    > I would like to find a simple way to get the format of the regional

    settings
    > or now if possible.
    >
    >
    > Thanks for your help,
    > Greg Rothlander
    >
    >
    > "Arthur Wood" <wooda@saic-trsc.com> wrote:
    > >
    > >Greg,
    > >
    > > If it is realy important to display dates in a standard form,

    regardless
    > >of the Regional setting, you can use Format$(Now,"<set the format you

    want>")
    > >and this will automatically adjust to whatever is set in regional

    setting.,
    > >and display with a consistant format.
    > >
    > >Arthur Wood
    > >
    > >"Greg Rothlander" <grothlander@anteon.com> wrote:
    > >>
    > >>
    > >>The format of Date/Time settings in Access are dependant on the regional
    > >>settings in Windows. The same goes for the NOW command in VB. Try

    using
    > >>NOW and then change your regional settings then run NOW again... NOW is

    > >now
    > >>different.
    > >>
    > >>When using VB how can I ask Access, Windows, or VB what format is being

    > >used?
    > >> I could handle this by telling Access not to use the regional date/time
    > >>settings. I don't know how to do either of these or if they are even

    possible.
    > >>
    > >>
    > >>I'm looking for a simple API call or something in VB such as now.format?
    > >>
    > >>
    > >>Thanks,
    > >>Greg
    > >>
    > >>Here is a little background in order to understand why I can not force

    > the
    > >>users to a given regional and/or application setting.
    > >>
    > >>I am having problems with users in other countries making changes to

    their
    > >>regional settings and then the date/time format in the Access datebase

    > changes.
    > >> VB is not able to correctly determine date formats such as 01/02/01.

    > Is
    > >>it Jan 02, 2001 or Feburary 1st, 2001. In some countries they really

    get
    > >>odd and use (yy.mm.dd)... So then what is 01.02.02?
    > >>
    > >>When my application applies a users date/time format they can set within
    > >>the application... there is no way to determine format Access is using

    > without
    > >>knowing the regional date/time setting used in Windows.
    > >>
    > >>I do have the option of removing the date/time setting in Access and

    just
    > >>use a string that I can control in my VB code. This would require a

    little
    > >>more coding that I'd like, but I can go this route if necessary.

    > >

    >




  5. #5
    anuradha Guest

    Re: VB, Regional Settings, and Access


    Hi

    Insert the following code in the gobal declarations.

    Private Const LOCALE_SSHORTDATE = &H1F
    Private Const LOCALE_SDATE = &H1D

    Private Declare Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA"
    (ByVal LOCALE As Long, ByVal LCType As Long, ByVal lpLCData As String) As
    Boolean
    Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long

    Insert the following code in the startup form

    Dim dwLCID As Long
    dwLCID = GetSystemDefaultLCID()
    If SetLocaleInfo(dwLCID, LOCALE_SSHORTDATE, "dd/MM/yyyy") = False Then
    MsgBox "DATE SETTING FAILED. "
    Exit Sub
    End If
    If SetLocaleInfo(dwLCID, LOCALE_SDATE, "/") = False Then
    MsgBox "DATE SEPARATOR FAILED."
    Exit Sub
    End If

    THis will allow u to set the date and separator in the regional settings
    of the control panel from VB.

    Anuradha




    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Greg,
    >
    > If it is realy important to display dates in a standard form, regardless
    >of the Regional setting, you can use Format$(Now,"<set the format you want>")
    >and this will automatically adjust to whatever is set in regional setting.,
    >and display with a consistant format.
    >
    >Arthur Wood
    >
    >"Greg Rothlander" <grothlander@anteon.com> wrote:
    >>
    >>
    >>The format of Date/Time settings in Access are dependant on the regional
    >>settings in Windows. The same goes for the NOW command in VB. Try using
    >>NOW and then change your regional settings then run NOW again... NOW is

    >now
    >>different.
    >>
    >>When using VB how can I ask Access, Windows, or VB what format is being

    >used?
    >> I could handle this by telling Access not to use the regional date/time
    >>settings. I don't know how to do either of these or if they are even possible.
    >>
    >>
    >>I'm looking for a simple API call or something in VB such as now.format?
    >>
    >>
    >>Thanks,
    >>Greg
    >>
    >>Here is a little background in order to understand why I can not force

    the
    >>users to a given regional and/or application setting.
    >>
    >>I am having problems with users in other countries making changes to their
    >>regional settings and then the date/time format in the Access datebase

    changes.
    >> VB is not able to correctly determine date formats such as 01/02/01.

    Is
    >>it Jan 02, 2001 or Feburary 1st, 2001. In some countries they really get
    >>odd and use (yy.mm.dd)... So then what is 01.02.02?
    >>
    >>When my application applies a users date/time format they can set within
    >>the application... there is no way to determine format Access is using

    without
    >>knowing the regional date/time setting used in Windows.
    >>
    >>I do have the option of removing the date/time setting in Access and just
    >>use a string that I can control in my VB code. This would require a little
    >>more coding that I'd like, but I can go this route if necessary.

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center