UK Date Format Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I'm not sure is this is the right category for this question so I would
appreciate it if someone could direct me to the correct one.

I am working on a database for use in the US and UK. During testing using
UK Regional Settings I ran into some problems with dates that are used in SQL
statements in my code. Here is one that I'm trying to resolve now.

I have the following formula in a SQL statement used for a rowsouce in a
list box.

WeekdayName(WeekDay([SalesDate],True))

With US Regional Settings the weekday name displays correctly in my listbox
along with the date and other columns. When I switch to UK Regional Settings
the weekday is one day off. For example Dec 15, 2006 (15/12/2006) shows up
as Sat, when it should be Fri.

Any ideas or better ways to handle dates in VBA would be appreciated.

Thanks
 
ScottS said:
Hello:

I'm not sure is this is the right category for this question so I would
appreciate it if someone could direct me to the correct one.

I am working on a database for use in the US and UK. During testing using
UK Regional Settings I ran into some problems with dates that are used in SQL
statements in my code. Here is one that I'm trying to resolve now.

I have the following formula in a SQL statement used for a rowsouce in a
list box.

WeekdayName(WeekDay([SalesDate],True))

With US Regional Settings the weekday name displays correctly in my listbox
along with the date and other columns. When I switch to UK Regional Settings
the weekday is one day off. For example Dec 15, 2006 (15/12/2006) shows up
as Sat, when it should be Fri.
Click to expand...


The True value as the FirstDayOfWeek argument should be
invalid. Most likely, you have the right parenthesis in the
wrong place. I think(?) you should specify it as 1 or 2
according to your definition of the start of the week:
WeekdayName(WeekDay([SalesDate],1), True)
See VBA Help for details.

You might find it easier to use the Format function:
Format(SalesDate, "ddd", 1)
 
Thanks for your help.

The right parethesis error was a typo on my part when I wrote the post--I
should be more careful when I post. I agree that the definition for the
first day of the week is the culprit. I'll have to acommodate Sunday and
Monday as first day of week options depending on who's using the system.
I'll also check out your Format suggestion.




--
Scott S


Marshall Barton said:
ScottS said:
Hello:

I'm not sure is this is the right category for this question so I would
appreciate it if someone could direct me to the correct one.

I am working on a database for use in the US and UK. During testing using
UK Regional Settings I ran into some problems with dates that are used in SQL
statements in my code. Here is one that I'm trying to resolve now.

I have the following formula in a SQL statement used for a rowsouce in a
list box.

WeekdayName(WeekDay([SalesDate],True))

With US Regional Settings the weekday name displays correctly in my listbox
along with the date and other columns. When I switch to UK Regional Settings
the weekday is one day off. For example Dec 15, 2006 (15/12/2006) shows up
as Sat, when it should be Fri.
Click to expand...


The True value as the FirstDayOfWeek argument should be
invalid. Most likely, you have the right parenthesis in the
wrong place. I think(?) you should specify it as 1 or 2
according to your definition of the start of the week:
WeekdayName(WeekDay([SalesDate],1), True)
See VBA Help for details.

You might find it easier to use the Format function:
Format(SalesDate, "ddd", 1)
Click to expand...
 
Back
Top