Weekday from Date problem

[AdrianGawrys]

Can someone tell me why this query works:

SELECT WeekdayName(Weekday(#17/02/2007#)-1) AS [Day]

and this one doesnt

SELECT WeekdayName(Weekday(#18/02/2007#)-1) AS [Day]

what I am trying to achieve is to get weekday from date obviously. a
Appreciate any help

[Jim Doherty]

Can someone tell me why this query works:

SELECT WeekdayName(Weekday(#17/02/2007#)-1) AS [Day]

and this one doesnt

SELECT WeekdayName(Weekday(#18/02/2007#)-1) AS [Day]

what I am trying to achieve is to get weekday from date obviously. a
Appreciate any help

you are missing out the second argument of the WEEKDAY function try this and closely compare yours against these. The second argument determines the starting point for the week ie: 1 equals Sunday 2 equals Monday and so on

Your fault is that you at some point are telling it use a value of 0-1 which is of course a negative falling outside of the range 0 to 7

WeekdayName(Weekday([Date],0))
or this
WeekdayName(Weekday([Date]-1,0))

where [Date] equals the name of your field being tested when used in a query


Jim :)

[missinglinq]

what I am trying to achieve is to get weekday from date

First off, your code is going to retrieve the week day name of the day before your date, not the week day name of the actual date. Is this what you want?

FirstDayOfWeek is optional and defaults to 1, which means Sunday; that's not the issue here.

The reason this identical syntax fails (I think) is this:

WeekdayName(Weekday(#17/02/2007#)-1 )

is retrieving the WeekDayName of 17/02/2007 (Saturday) and then, with the -1, is yielding the previous WeekDayName, which is Friday. And this works fine!

The problem when you change the date to 18/02/2007 is that 18/02/2007 is a Sunday. Then, with the -1, you're once again trying to get the WeekDayName that comes before Sunday, and in Access' mind, when the FirstDayOfWeek is set to Sunday, there is no day of the week before Sunday! And so the code fails if the day entered is a Sunday.

The way around this is to subtract your 1 from the date first, then evaluate the WeekDayName from this date. You do this by including a second set of parentheses .

Instead of

WeekdayName(Weekday(#18/02/2007#)-1)

use

WeekdayName(Weekday( (#18/02/2007#)-1) )

Linq ;0)>

[AdrianGawrys]

Thanks guys!! You're awesome - both resolutions sound fine. As you said - my fault of trying to change begining of the week by (-1). Thanks again!

[missinglinq]

So, you really were only trying to get the WeekDayName for the given date? Then plain old

WeekdayName(Weekday(#18/02/2007#))

will work unless you need the firstdayof week to be something other than Sunday.

Glad we could help!

Linq ;0)>