International Access version problem

[bobby]

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]

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

 

[Huey]

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]

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.

 

[Jess Martyn]

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.