First day of week?????

[Derek Brown]

Hi all

When I create a query to list all appointments booked for any particular
WeekNumber I use Year WeekNumber and DayName in the query. The results are
not what you would expect. The weekday name Saturday for example comes up as
Sunday. I know it's ridiculous but please someone try the following

 

[Duane Hookom]

I'm having a little trouble seeing your data and your SQL View. Could you
share more about your application?

--
Duane Hookom
MS Access MVP

Hi all

When I create a query to list all appointments booked for any particular
WeekNumber I use Year WeekNumber and DayName in the query. The results are
not what you would expect. The weekday name Saturday for example comes up
as Sunday. I know it's ridiculous but please someone try the following

Open a query (not linked to any table) in design view. In the
field heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])

Set your system date to Jan 1st 2005 and switch to datasheet veiw. Yes,
my
system calender shows Saturday as the 1st of jan but what does your query
show?

I do hope that this is fixable. I am told that there is a feature to
change the firstday of the week in a query. How do I find it. I'll do the
work if someone will point me in the right direction.

 

[John Vinson]

Hi all

When I create a query to list all appointments booked for any particular
WeekNumber I use Year WeekNumber and DayName in the query. The results are
not what you would expect. The weekday name Saturday for example comes up as
Sunday. I know it's ridiculous but please someone try the following

Open a query (not linked to any table) in design view. In the
field heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])

Just use

DayName: Format([Dates], "dddd")

to get the name of the date. You're going all around the barn to do
something that can be done much easier.

John W. Vinson[MVP]

 

[Derek Brown]

Thanks John.

The problem is I need to format a report (A diary) for any particular week.
I don't want the user to have the hastle of remembering the date of last
Sunday and the date of next Saturday to be able to print 1 weeks diary. The
user should be able to just enter the current week number or press a default
button that selects the current week. So all date information has to start
with the week number and current year.

Hi all

When I create a query to list all appointments booked for any particular
WeekNumber I use Year WeekNumber and DayName in the query. The results are
not what you would expect. The weekday name Saturday for example comes up
as
Sunday. I know it's ridiculous but please someone try the following

Open a query (not linked to any table) in design view. In the
field heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])

Just use

DayName: Format([Dates], "dddd")

to get the name of the date. You're going all around the barn to do
something that can be done much easier.

John W. Vinson[MVP]

 

[Derek Brown]

I hope in my reply to John Vinson that it becomes clearer.

I have just picked up something from net about datediff, not what i need but
here it is:
DateDiff(interval,Date1,Date2,[, firstdayofweek[, firstweekofyear]])
Although the bracketing looks wierd! I have also established that
If Firstday of week is set to 1 then Sunday becomes the first day of the
week
If Firstweekof year is set to 3 That means the fuirst full week of the year
and not the first days becomes week 1.

If you could look at my SQL and tell me how to use this info in the field
area of my query I might get a nights sleep for a change.

SELECT Appointments.AppointmentDate, DatePart("yyyy",[AppointmentDate]) AS
[Year], WeekdayName(DatePart("w",[AppointmentDate],1)) AS DayOfWeek,
DatePart("ww",[AppointmentDate],1,3) AS [Week Number]
FROM Schools INNER JOIN Appointments ON Schools.[Account Code] =
Appointments.[Account Code]
WHERE
(((DatePart("yyyy",[AppointmentDate]))=[Forms]![DiaryWeekForm]![YearNow])
AND
((DatePart("ww",[AppointmentDate],1,3))=[Forms]![DiaryWeekForm]![WeekNumber]))
ORDER BY Appointments.AppointmentDate;

I'm having a little trouble seeing your data and your SQL View. Could you
share more about your application?

--
Duane Hookom
MS Access MVP

Hi all

When I create a query to list all appointments booked for any particular
WeekNumber I use Year WeekNumber and DayName in the query. The results
are not what you would expect. The weekday name Saturday for example
comes up as Sunday. I know it's ridiculous but please someone try the
following

Open a query (not linked to any table) in design view. In the
field heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])

Set your system date to Jan 1st 2005 and switch to datasheet veiw. Yes,
my
system calender shows Saturday as the 1st of jan but what does your
query
show?

I do hope that this is fixable. I am told that there is a feature to
change the firstday of the week in a query. How do I find it. I'll do
the work if someone will point me in the right direction.

 

[Derek Brown]

Hi all

I have used
DayOfWeek: Format([AppointmentDate],"dddd") from John V and
Week Number: (DatePart("ww",[AppointmentDate],1,3)) This is the correct
format to get the first day of the week as Sunday and week 1 as first full
week of year. Would you believe that having solved that (with a good deal of
help) when I select week 52 I get in the same week 01 01 05 and 31 12 05
because both are weeks 52 and both are in the current year. I was warned
about diaries.

Thanks John.

The problem is I need to format a report (A diary) for any particular
week. I don't want the user to have the hastle of remembering the date of
last Sunday and the date of next Saturday to be able to print 1 weeks
diary. The user should be able to just enter the current week number or
press a default button that selects the current week. So all date
information has to start with the week number and current year.

Hi all

When I create a query to list all appointments booked for any particular
WeekNumber I use Year WeekNumber and DayName in the query. The results
are
not what you would expect. The weekday name Saturday for example comes up
as
Sunday. I know it's ridiculous but please someone try the following

Open a query (not linked to any table) in design view. In the
field heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])

Just use

DayName: Format([Dates], "dddd")

to get the name of the date. You're going all around the barn to do
something that can be done much easier.

John W. Vinson[MVP]

 

[Gary Walter]

Hi Derek,

PMFBI

If I wanted to know the Sunday
and Saturday dates of *current week*,
I'd use:

DateValue(Date())-(Weekday(Date())-1) AS SundayOfWeek

DateValue(Date())-(Weekday(Date())-7) AS SaturdayOfWeek

If your field contains time values, your Where clause
might look like:

WHERE
[AppointmentDate] >= DateValue(Date())-(Weekday(Date())-1)
AND
[AppointmentDate]< DateValue(Date())-(Weekday(Date())-7) + 1

Apologies again for butting in.

good luck,

gary

I have used
DayOfWeek: Format([AppointmentDate],"dddd") from John V and
Week Number: (DatePart("ww",[AppointmentDate],1,3)) This is the correct
format to get the first day of the week as Sunday and week 1 as first full
week of year. Would you believe that having solved that (with a good deal
of help) when I select week 52 I get in the same week 01 01 05 and 31 12
05 because both are weeks 52 and both are in the current year. I was
warned about diaries.

Thanks John.

The problem is I need to format a report (A diary) for any particular
week. I don't want the user to have the hastle of remembering the date of
last Sunday and the date of next Saturday to be able to print 1 weeks
diary. The user should be able to just enter the current week number or
press a default button that selects the current week. So all date
information has to start with the week number and current year.

On Tue, 11 Oct 2005 22:35:12 +0100, "Derek Brown"

Hi all

When I create a query to list all appointments booked for any particular
WeekNumber I use Year WeekNumber and DayName in the query. The results
are
not what you would expect. The weekday name Saturday for example comes
up as
Sunday. I know it's ridiculous but please someone try the following

Open a query (not linked to any table) in design view. In the
field heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])

Just use

DayName: Format([Dates], "dddd")

to get the name of the date. You're going all around the barn to do
something that can be done much easier.

John W. Vinson[MVP]

 

[Derek Brown]

Thats great and has been saved.

The problem is that the user may want a weeks diary or report for a future
Week. The only information is the week number and the year. Which
unfortunately may be at the end or beginning of a year. so any solution must
take into account the year and week number. Is it possible to modify your
sugestion to account for this. If it helps at all I got around it by putting
two Text boxes on my form and used the following code that I
modified from a function created by TimK at Utter Access VIP

Public Sub GetDate()
Dim intWeekNumber As Integer, intFullYear As Integer
Dim DayOne As Date, x As Integer
Dim i As Integer
intFullYear = YearNow ' YearNow is a calculated field on my form that
only
shows year number

For i = 1 To 7
If DatePart("ww", DateSerial(intFullYear, 1, i), , vbFirstFullWeek) = 1
Then
x = i
Exit For
End If
Next i

DayOne = DateAdd("d", (WeekNumber - 1) * 7, DateSerial(intFullYear, 1, 1)) -
x
WeekStartDate = DayOne + 1 '+ 1 changes first day of week from Sunday to
Monday
WeekEndDate = DayOne + 7
End Sub

WeekStartDate and WeehEndDate are two new Text boxes on my form. The query
now draws its criteria from the form as "Between WeekStartDate and
WeekEndDate"

Any help?

Hi Derek,

PMFBI

If I wanted to know the Sunday
and Saturday dates of *current week*,
I'd use:

DateValue(Date())-(Weekday(Date())-1) AS SundayOfWeek

DateValue(Date())-(Weekday(Date())-7) AS SaturdayOfWeek

If your field contains time values, your Where clause
might look like:

WHERE
[AppointmentDate] >= DateValue(Date())-(Weekday(Date())-1)
AND
[AppointmentDate]< DateValue(Date())-(Weekday(Date())-7) + 1

Apologies again for butting in.

good luck,

gary

I have used
DayOfWeek: Format([AppointmentDate],"dddd") from John V and
Week Number: (DatePart("ww",[AppointmentDate],1,3)) This is the correct
format to get the first day of the week as Sunday and week 1 as first
full week of year. Would you believe that having solved that (with a good
deal of help) when I select week 52 I get in the same week 01 01 05 and
31 12 05 because both are weeks 52 and both are in the current year. I
was warned about diaries.

Thanks John.

The problem is I need to format a report (A diary) for any particular
week. I don't want the user to have the hastle of remembering the date
of last Sunday and the date of next Saturday to be able to print 1 weeks
diary. The user should be able to just enter the current week number or
press a default button that selects the current week. So all date
information has to start with the week number and current year.

On Tue, 11 Oct 2005 22:35:12 +0100, "Derek Brown"

Hi all

When I create a query to list all appointments booked for any
particular
WeekNumber I use Year WeekNumber and DayName in the query. The results
are
not what you would expect. The weekday name Saturday for example comes
up as
Sunday. I know it's ridiculous but please someone try the following

Open a query (not linked to any table) in design view. In the
field heading of the first 4 columns type each of the following:
Dates: Now()
Year: DatePart("yyyy",[Dates])
DayNumber: (DatePart("w",[Dates]))
DayName: WeekdayName([DayNumber])

Just use

DayName: Format([Dates], "dddd")

to get the name of the date. You're going all around the barn to do
something that can be done much easier.

John W. Vinson[MVP]

 

[Gary Walter]

Hi Derek,

First let me correct my last post:

If I wanted to know the Sunday
and Saturday dates of *current week*,
I'd use:

Date()-(Weekday(Date())-1) AS SundayOfWeek

Date()-(Weekday(Date())-7) AS SaturdayOfWeek

If your field "AppointmentDate" contained time values, your Where clause
might look like:

WHERE
[AppointmentDate] >= Date()-(Weekday(Date())-1)
AND
[AppointmentDate]< Date()-(Weekday(Date())-7) + 1

Sorry, I meant to go back and correct that but forgot....

Next, let me ask, if you don't mind, if "week numbers"
are the only way your users can relate to requesting a
weekly report on your form?

And, will they only be requesting for the current year
or future years, i.e., never for past years?

This might be one way:

Create a "tblNum" with one field "Num"
of type Long and make it the primary key.

Enter 53 records, 0 through 52 in "Num."


Create 2 combo boxes and 2 text boxes on your form.

1) cmboYear:

rowsource =
SELECT Year(Date())+[tblNum].[Num] AS RptYear
FROM tblNum
ORDER BY Year(Date())+[tblNum].[Num];

2) cmboWeek:

"initial" rowsource:
SELECT [Num]+1 AS YearWeek,
(#1/1/2005#-(Weekday(#1/1/2005#)-1))+[Num]*7 AS WeekStart,
[WeekStart]+6 AS WeekEnd
FROM tblNum
ORDER BY [Num]+1;

Column Count: 3
Column Heads: Yes
Column Widths: 0.7";0.8";0.8"
Bound Column: 1
List Rows: 35
List Width: 2.5"

3) txtStartDate

unbound but locked

4) txtEndDate

unbound but locked

'**** code for form ****
Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim strSQL As String
Dim lngYear As Long

lngYear = Year(Date)
Me!cmboYear = lngYear
strSQL = "SELECT [Num]+1 AS YearWeek, " _
& "(#1/1/" & lngYear & "#-(Weekday(#1/1/" _
& lngYear & "#)-1))+[Num]*7 AS WeekStart, " _
& "[WeekStart]+6 AS WeekEnd " _
& "FROM tblNum " _
& "ORDER BY [Num]+1;"
Me!cmboWeek.RowSource = strSQL
Me!cmboWeek.Requery
Me!cmboWeek = 1
cmboWeek_AfterUpdate

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub
Private Sub cmboWeek_AfterUpdate()
On Error GoTo Err_cmboWeek_AfterUpdate
Me!txtWeekStart = Me!cmboWeek.Column(1)
Me!txtWeekEnd = Me!cmboWeek.Column(2)

Exit_cmboWeek_AfterUpdate:
Exit Sub

Err_cmboWeek_AfterUpdate:
MsgBox Err.Description
Resume Exit_cmboWeek_AfterUpdate
End Sub

Private Sub cmboYear_AfterUpdate()
On Error GoTo Err_cmboYear_AfterUpdate
Dim strSQL As String
Dim lngYear As Long

lngYear = Me!cmboYear
strSQL = "SELECT [Num]+1 AS YearWeek, " _
& "(#1/1/" & lngYear & "#-(Weekday(#1/1/" _
& lngYear & "#)-1))+[Num]*7 AS WeekStart, " _
& "[WeekStart]+6 AS WeekEnd " _
& "FROM tblNum " _
& "ORDER BY [Num]+1;"
Me!cmboWeek.RowSource = strSQL
Me!cmboWeek.Requery
Me!cmboWeek = 1
cmboWeek_AfterUpdate

Exit_cmboYear_AfterUpdate:
Exit Sub

Err_cmboYear_AfterUpdate:
MsgBox Err.Description
Resume Exit_cmboYear_AfterUpdate
End Sub
'**** end of code *****

In this manner you don't care how Access
can confuse a week number. You are defining
a start and end date for your report, and *your
week numbers* will show every week for the
year your user selects in cmboYear.

That might be one way.....

good luck,

gary

{I just downloaded "Time Has Come Today"
by Chambers Brothers thru iTunes, and above
was generated while listening to it over and
over.....so hopefully no flashbacks interferred
with logic. }

 

[Derek Brown]

Hi Gary and thank you.

Yes the user will have to select only by week number and year number. It
cannot be right for the user to have to figure out start and end date of a
week just to print a diary. If it was just current week then that would be a
breeze compared to the problems I have invited on myself. Thanks again for
your solution.

Hi Derek,

First let me correct my last post:

If I wanted to know the Sunday
and Saturday dates of *current week*,
I'd use:

Date()-(Weekday(Date())-1) AS SundayOfWeek

Date()-(Weekday(Date())-7) AS SaturdayOfWeek

If your field "AppointmentDate" contained time values, your Where clause
might look like:

WHERE
[AppointmentDate] >= Date()-(Weekday(Date())-1)
AND
[AppointmentDate]< Date()-(Weekday(Date())-7) + 1

Sorry, I meant to go back and correct that but forgot....

Next, let me ask, if you don't mind, if "week numbers"
are the only way your users can relate to requesting a
weekly report on your form?

And, will they only be requesting for the current year
or future years, i.e., never for past years?

This might be one way:

Create a "tblNum" with one field "Num"
of type Long and make it the primary key.

Enter 53 records, 0 through 52 in "Num."


Create 2 combo boxes and 2 text boxes on your form.

1) cmboYear:

rowsource =
SELECT Year(Date())+[tblNum].[Num] AS RptYear
FROM tblNum
ORDER BY Year(Date())+[tblNum].[Num];

2) cmboWeek:

"initial" rowsource:
SELECT [Num]+1 AS YearWeek,
(#1/1/2005#-(Weekday(#1/1/2005#)-1))+[Num]*7 AS WeekStart,
[WeekStart]+6 AS WeekEnd
FROM tblNum
ORDER BY [Num]+1;

Column Count: 3
Column Heads: Yes
Column Widths: 0.7";0.8";0.8"
Bound Column: 1
List Rows: 35
List Width: 2.5"

3) txtStartDate

unbound but locked

4) txtEndDate

unbound but locked

'**** code for form ****
Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim strSQL As String
Dim lngYear As Long

lngYear = Year(Date)
Me!cmboYear = lngYear
strSQL = "SELECT [Num]+1 AS YearWeek, " _
& "(#1/1/" & lngYear & "#-(Weekday(#1/1/" _
& lngYear & "#)-1))+[Num]*7 AS WeekStart, " _
& "[WeekStart]+6 AS WeekEnd " _
& "FROM tblNum " _
& "ORDER BY [Num]+1;"
Me!cmboWeek.RowSource = strSQL
Me!cmboWeek.Requery
Me!cmboWeek = 1
cmboWeek_AfterUpdate

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub
Private Sub cmboWeek_AfterUpdate()
On Error GoTo Err_cmboWeek_AfterUpdate
Me!txtWeekStart = Me!cmboWeek.Column(1)
Me!txtWeekEnd = Me!cmboWeek.Column(2)

Exit_cmboWeek_AfterUpdate:
Exit Sub

Err_cmboWeek_AfterUpdate:
MsgBox Err.Description
Resume Exit_cmboWeek_AfterUpdate
End Sub

Private Sub cmboYear_AfterUpdate()
On Error GoTo Err_cmboYear_AfterUpdate
Dim strSQL As String
Dim lngYear As Long

lngYear = Me!cmboYear
strSQL = "SELECT [Num]+1 AS YearWeek, " _
& "(#1/1/" & lngYear & "#-(Weekday(#1/1/" _
& lngYear & "#)-1))+[Num]*7 AS WeekStart, " _
& "[WeekStart]+6 AS WeekEnd " _
& "FROM tblNum " _
& "ORDER BY [Num]+1;"
Me!cmboWeek.RowSource = strSQL
Me!cmboWeek.Requery
Me!cmboWeek = 1
cmboWeek_AfterUpdate

Exit_cmboYear_AfterUpdate:
Exit Sub

Err_cmboYear_AfterUpdate:
MsgBox Err.Description
Resume Exit_cmboYear_AfterUpdate
End Sub
'**** end of code *****

In this manner you don't care how Access
can confuse a week number. You are defining
a start and end date for your report, and *your
week numbers* will show every week for the
year your user selects in cmboYear.

That might be one way.....

good luck,

gary

{I just downloaded "Time Has Come Today"
by Chambers Brothers thru iTunes, and above
was generated while listening to it over and
over.....so hopefully no flashbacks interferred
with logic. }