First Day of Week Mystery

R

raskew

AWF VIP

Local time

Today, 08:43

Joined

Jun 2, 2001

Messages

2,733

• Sep 26, 2008

• #1

Hi -

Hope this won't be considered double-posting, however I've run into a weird situation in another post http://www.access-programmers.co.uk/forums/showthread.php?t=157053 which I'm afraid won't be noted.

When importing the OP's DB, then creating a query which lists weekday of particular dates, it appears that Saturday is treated as the First Day of Week (1). Can't find anything in the DB that saves this info, however, listing dates and format([AttendanceDate], "dddd") erroneously shows a date that's actually Wednesday as Tuesday.

Thought it might be in the DB itself, so imported the tables/query to a new DB. Same problem. Yet, if in the immediate window I enter:

? format(date(), "dddd mm/dd/yyyy")
Thursday 09/25/2008

...it returns the correct info, so it's not a computer problem.

I'm lost. Any thoughts?

Bob

 

Last edited: Sep 26, 2008

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 14:43

Joined

Sep 12, 2006

Messages

16,094

• Sep 26, 2008

• #2

look at weekday function

theres an optional firstdayofweek parameter but the default is sunday not saturday

 

R

raskew

AWF VIP

Local time

Today, 08:43

Joined

Jun 2, 2001

Messages

2,733

• Sep 26, 2008

• #3

Thanks for the response -

PHP:

look at weekday function

...first thing I did. Couldn't find where it might be stored. And, no indication that it's tied to tables, but the fact that I imported the tables into a new DB, with the same problem remaining, kind of points to the weekday being tied to the table. The query was of my own creation, so that's not the issue.

Yet, we have lots of posts where Monday is used as day (1), but I've never seen it cause a problem.

Still lost.

Bob

 

D

dkinley

Access Hack by Choice

Local time

Today, 08:43

Joined

Jul 29, 2008

Messages

2,003

• Sep 26, 2008

• #4

raskew,

I think it's the Weekday function argument "vbMonday" that is causing it. In the immediate window, you are using the Format function which is different than the Weekday function.

I did some searching and some of the explanation in this link ...

http://bytes.com/forum/thread442595.html

shows some information. I didn't attempt to duplicate but it seems the usage of vbMonday shifts the numbering of the days around. The last post shows the difference of using Format vs. Weekday. Not sure if that applies to the OP's function because it uses that argument.

Don't know if that shines a little light.
-dK

 

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 14:43

Joined

Sep 12, 2006

Messages

16,094

• Sep 26, 2008

• #5

sorry, i didnt read the posting properly - you were talking about format, rather than weekday

the help for format gives a firstday value of 0 (ie no value) as

vbUseSystem 0 Use NLS API setting.

not sure where this is stored - must be some windows setting. Instead you can coerce a speciifc day by setting firstday to whatever you require

 

R

raskew

AWF VIP

Local time

Today, 08:43

Joined

Jun 2, 2001

Messages

2,733

• Sep 26, 2008

• #6

The mystery continues. I thank all for their responses.

Added this to the query:
Expr3: Weekday([AttendanceDate],1)

It correctly shows 4 for 9/3/08 (a Wednesday) and displays the correct day of week for all dates.

The question is: since the format function doesn't include a First Day of Week option, how to display the correct day of week, e.g. Wednesday, re the date 9/3/08, and not Tuesday.

Best Wishes - Bob

 

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 14:43

Joined

Sep 12, 2006

Messages

16,094

• Sep 26, 2008

• #7

format DOES include the option, as well as a first week of year option

 

R

raskew

AWF VIP

Local time

Today, 08:43

Joined

Jun 2, 2001

Messages

2,733

• Sep 27, 2008

• #8

You're absolutely right, I stand corrected!

Expr4: Format([attendancedate],"dddd", 1) does the job.
Obviously, the Format() function is not my 'strong suit'.

Thanks so much!

It would still be interesting to understand where in this particular DB something exists that makes Saturday as the default first day of week.

Best Wishes, Bob

 

Last edited: Sep 27, 2008

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 14:43

Joined

Sep 12, 2006

Messages

16,094

• Sep 27, 2008

• #9

i posted this above

the help says that if no value is set then, the default is

vbUseSystem 0 Use NLS API setting.

so there must be a windows setting somewhere for it

 

W

WayneRyan

AWF VIP

Local time

Today, 14:43

Joined

Nov 19, 2002

Messages

7,120

• Sep 27, 2008

• #10

Bob,

Search the registry for:

iFirstDayOfWeek

There is an API call to set this, but maybe just a simple RegEdit call ...

Wayne

 

R

raskew

AWF VIP

Local time

Today, 08:43

Joined

Jun 2, 2001

Messages

2,733

• Sep 27, 2008

• #11

Thanks Wayne for that.

I'm back in touch with the OP so maybe we can get this sorted out.

Guess my underlying question is:

What did the OP do when developing this DB that caused the dates to be handled in the manner they are? And, why can't I undo this but instead
must use a workaround to get the dates to do what I need them to do?

If you or I were to develop these relatively simple tables from scratch, they would most certainly treat Sunday as day1, by default. Not so in this case and it's just perplexing that short of starting from scratch and reinputting the data by hand, I've been unable to turn-around a very strange situation. Copying the data to a new DB seems to 'bring along' the strange behavior.

Could be wrong but have the perception that the OP's perceived skill level probably rules-out any tricky 'slight of hand', e.g. RegEdit stuff. Once again, I could be wrong. It was just perplexing to get hung-up on something that should be a non-issue.

We'll see.

Thanks again for your response.

Best Wishes -- Bob