VB, Regional Settings, and Access

[Greg Rothlander]

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.

[Arthur Wood]

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.

[Greg Rothlander]

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.
>

[Douglas J. Steele]

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.
> >
>

[anuradha]

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.
>