MS Access project with SQL Server - International Access version problem

Author

International Access version problem

bobby

2005-04-06, 8:06 pm

I have developed Access applications for some time but this is one of the
weirdest problems ever.

On a form I have 2 independent subforms both showing similar data and
showing the same problem. On each form I display some weekly data based on a
stored procedure. The stored procedure looks like

SELECT

CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 1 THEN Hours
ELSE 0 END)) AS Sunday,

CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 2 THEN Hours
ELSE 0 END)) AS Monday,

CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 3 THEN Hours
ELSE 0 END)) AS Tuesday,

CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 4 THEN Hours
ELSE 0 END)) AS Wednesday,

CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 5 THEN Hours
ELSE 0 END)) AS Thursday,

CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 6 THEN Hours
ELSE 0 END)) AS Friday,

CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 7 THEN Hours
ELSE 0 END)) AS Saturday,

CONVERT(Numeric(9, 2), SUM(Hours )) AS TotalWeek

FROM dbo.tblMyTable

WHERE (MyDate BETWEEN @StartDate AND @EndDate)

To put it in another way; I produce a recordset with 7 fields, one for each
day of the week. The Hours field does a sum for each date.

This works absolutely fine nearly always. But I have installed this on a
machine abroad (Denmark) where a Danish version of Access is installed and
this is where things go wrong. On this client machine data gets bound to the
wrong field (control source). On the form I have fields named Monday through
Sunday. The following happens:

Should be MONDAY is bound to SUNDAY.

Should be TUESDAY is bound to MONDAY.

Should be WEDNESDAY is bound to TUESDAY.

Should be THURSDAY is bound to WEDNESDAY.

Should be FRIDAY is bound to THURSDAY.

Should be SATURDAY is bound to FRIDAY.

Should be SUNDAY is bound to SATURDAY.

I figure this has something to do with the international first day of week.
In the US Sunday is the first day of the week, whereas Monday is the first
day of the week in Denmark. But I can't see how it influences in this case
because the stored procedure is executed on the server. Is SQL server so
clever that when it sees the call comes from a Danish client machine, it
will overrule the server setting of "first day of week" thus creating a
different recordset?

On clients where International runtime is installed things work fine.

I would appreciate any comments and suggestions.

Norman Yuan

2005-04-06, 8:06 pm

It has nothing to do with Access itself. After all, the problem is with SP,
which runs inside SQL Server.

Yes, your guess is right: in US, weekday starts on Sun by default, while in
other region it could start on Mon. (or anyday in the week). You can set
@@DateFirst to let SQL Server know which day you want it to be the first
day of a week. So,

SET DATEFIRST 1
SELECT...FROM...

Oddly enough, though, in "SET DATEFIRST" 1 means Mon. 2 means Tue....

"bobby" <bobby@bobbys.com> wrote in message
news:ePJQyP9IFHA.3376@TK2MSFTNGP14.phx.gbl...
> I have developed Access applications for some time but this is one of the
> weirdest problems ever.
>
> On a form I have 2 independent subforms both showing similar data and
> showing the same problem. On each form I display some weekly data based on
a
> stored procedure. The stored procedure looks like
>
> SELECT
>
> CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 1 THEN
Hours

> ELSE 0 END)) AS Sunday,
>
> CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 2 THEN
Hours

> ELSE 0 END)) AS Monday,
>
> CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 3 THEN
Hours

> ELSE 0 END)) AS Tuesday,
>
> CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 4 THEN
Hours

> ELSE 0 END)) AS Wednesday,
>
> CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 5 THEN
Hours

> ELSE 0 END)) AS Thursday,
>
> CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 6 THEN
Hours

> ELSE 0 END)) AS Friday,
>
> CONVERT(Numeric(9, 2), SUM(CASE DATEPART(weekday, MyDate) WHEN 7 THEN
Hours

> ELSE 0 END)) AS Saturday,
>
> CONVERT(Numeric(9, 2), SUM(Hours )) AS TotalWeek
>
> FROM dbo.tblMyTable
>
> WHERE (MyDate BETWEEN @StartDate AND @EndDate)
>
> To put it in another way; I produce a recordset with 7 fields, one for
each
> day of the week. The Hours field does a sum for each date.
>
> This works absolutely fine nearly always. But I have installed this on a
> machine abroad (Denmark) where a Danish version of Access is installed and
> this is where things go wrong. On this client machine data gets bound to
the
> wrong field (control source). On the form I have fields named Monday
through
> Sunday. The following happens:
>
> Should be MONDAY is bound to SUNDAY.
>
> Should be TUESDAY is bound to MONDAY.
>
> Should be WEDNESDAY is bound to TUESDAY.
>
> Should be THURSDAY is bound to WEDNESDAY.
>
> Should be FRIDAY is bound to THURSDAY.
>
> Should be SATURDAY is bound to FRIDAY.
>
> Should be SUNDAY is bound to SATURDAY.
>
> I figure this has something to do with the international first day of
week.
> In the US Sunday is the first day of the week, whereas Monday is the first
> day of the week in Denmark. But I can't see how it influences in this case
> because the stored procedure is executed on the server. Is SQL server so
> clever that when it sees the call comes from a Danish client machine, it
> will overrule the server setting of "first day of week" thus creating a
> different recordset?
>
> On clients where International runtime is installed things work fine.
>
> I would appreciate any comments and suggestions.
>
>

Huey

2005-04-06, 8:06 pm

Thanks for your reply.

The thing is I have already "SET DATEFIRST 1" so all clients work well when
they have the international run-time installed that I distributed the app
with. For 2 clients, however, they have a Danish Access installed and that
is where the problem is.

Does the @@datefirst persist? What I mean is, how do we get SQL server to
think Monday is first day of the week always, on all databases, on any
connection, at any time on any client?

"Norman Yuan" <NotReal@NotReal.not> wrote in message
news:uYnDNi%23IFHA.2700@TK2MSFTNGP09.phx.gbl...
> It has nothing to do with Access itself. After all, the problem is with
> SP,
> which runs inside SQL Server.
>
> Yes, your guess is right: in US, weekday starts on Sun by default, while
> in
> other region it could start on Mon. (or anyday in the week). You can set
> @@DateFirst to let SQL Server know which day you want it to be the first
> day of a week. So,
>
> SET DATEFIRST 1
> SELECT...FROM...
>
> Oddly enough, though, in "SET DATEFIRST" 1 means Mon. 2 means Tue....
>
>
> "bobby" <bobby@bobbys.com> wrote in message
> news:ePJQyP9IFHA.3376@TK2MSFTNGP14.phx.gbl...
> a
> Hours
> Hours
> Hours
> Hours
> Hours
> Hours
> Hours
> each
> the
> through
> week.
>
>

Norman Yuan

2005-04-06, 8:06 pm

OK, since data retreived from backend (Sql Sever) can be shown in whatever
format, you have to find out from where it goes wrong.

If you have used SET DateFirst 1 right before your "SELECT...FROM..."
statement, the SP should return you correct result set. You need to verify
it by just running the SP in the ADP project to see whether the returned
data set is correct or not (it should be!). After confirming the SP returns
correct data set, you then are sure the problem is caused by Danish version
of Access. In this case, you need to examine your data bindig code if there
is any or control formatting settings. I'd bet some sort of date/time
formatting is used at form level that causes the problem. You should be able
to correct them there.

"Huey" <Huey@NoThanks.com> wrote in message
news:uGnT36#IFHA.1280@TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
>
> The thing is I have already "SET DATEFIRST 1" so all clients work well
when
> they have the international run-time installed that I distributed the app
> with. For 2 clients, however, they have a Danish Access installed and that
> is where the problem is.
>
> Does the @@datefirst persist? What I mean is, how do we get SQL server to
> think Monday is first day of the week always, on all databases, on any
> connection, at any time on any client?
>
> "Norman Yuan" <NotReal@NotReal.not> wrote in message
> news:uYnDNi%23IFHA.2700@TK2MSFTNGP09.phx.gbl...
first[color=darkred]

the[color=darkred]
a[color=darkred]
to[color=darkred]
so[color=darkred]
it[color=darkred]
>
>

Jess Martyn

2005-04-13, 3:25 am

Dear Friends,

Recently, I have been through a good forum, hence I feel you to have a look
at it, and if you like, be a member, and discuss about Computer, Love,
Romance, Hacking, Cracking, Poems etc.

Site: http://www.devworld.tk

- Regards,
Jess.
"Norman Yuan" <NotReal@NotReal.not> wrote in message
news:OKJ2ShBJFHA.2704@tk2msftngp13.phx.gbl...
> OK, since data retreived from backend (Sql Sever) can be shown in whatever
> format, you have to find out from where it goes wrong.
>
> If you have used SET DateFirst 1 right before your "SELECT...FROM..."
> statement, the SP should return you correct result set. You need to verify
> it by just running the SP in the ADP project to see whether the returned
> data set is correct or not (it should be!). After confirming the SP
returns
> correct data set, you then are sure the problem is caused by Danish
version
> of Access. In this case, you need to examine your data bindig code if
there

> is any or control formatting settings. I'd bet some sort of date/time
> formatting is used at form level that causes the problem. You should be
able
> to correct them there.
>
>
> "Huey" <Huey@NoThanks.com> wrote in message
> news:uGnT36#IFHA.1280@TK2MSFTNGP09.phx.gbl...
> when
app[color=darkred]
that[color=darkred]
to[color=darkred]
with[color=darkred]
while[color=darkred]

set[color=darkred]
> first
> the
based[color=darkred]

for[color=darkred]
on[color=darkred]
> a
installed[color=dark
red]
> to
> so
> it
a[color=darkred]
>
>

Sponsored Links