How determine what day of the week a certain date is?

[PamelaDV]

I have two problems, actually. I am looking to see if there is a
function that will return the day of the week (Monday, Tuesday,
Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

Thanks!

Pamela

[Mike Storr]

Off the top of my head, I've never seen anything that will will return
Friday or Monday, but it wouldn't be to hard to make a function that does
this. The only problem will be if regional settings affect what day is
considered the first.

Function GetDayName(useD ate as Date) As String
Dim dayNum as Integer

If Not IsDate(useDate) Then Exit Function
dayNum = DatePart("w",Da te)
Select Case dayNum
Case 1
GetDayName = "Sunday"
Case 2
GetDayName = "Monday"
Case 3
GetDayName = "Tuesday"
etc...

End Select
End Function

As for the second question, you can get the week number (1-53 I beleive) by
using DatePart("ww",D ate). Perhaps this number could be used to sort or
group on in a query. Alternatively, you'd have to use a range as a string as
the sorting grouping value ie. "02/01/04 - 05/25/04.

Mike Storr
www.veraccess.com
"PamelaDV" <pd******@steel case.com> wrote in message
news:b6******** *************** ***@posting.goo gle.com...

I have two problems, actually. I am looking to see if there is a
function that will return the day of the week (Monday, Tuesday,
Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

Thanks!

Pamela

[paii, Ron]

For day of week try Format$(NOW()," dddd")

In your report, use SORTING AND GROUPING to sort on the date field, then set
GROUP ON to "Week".
If you also want to sort the days of the week, setup a 2nd sort on the same
date field and set GROUP ON to "Each value".

"PamelaDV" <pd******@steel case.com> wrote in message
news:b6******** *************** ***@posting.goo gle.com...

I have two problems, actually. I am looking to see if there is a
function that will return the day of the week (Monday, Tuesday,
Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

Thanks!

Pamela

[Jeremy Wallace]

Check the help files for WeekDay().

--
=============== ==
Jeremy Wallace
AlphaBet City Dataworks
ABCDataworks dot com
"PamelaDV" <pd******@steel case.com> wrote in message
news:b6******** *************** ***@posting.goo gle.com...

I have two problems, actually. I am looking to see if there is a
function that will return the day of the week (Monday, Tuesday,
Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

Thanks!

Pamela

[Mike Sherrill]

On 4 Mar 2004 12:20:16 -0800, pd******@steelc ase.com (PamelaDV) wrote:

I have two problems, actually. I am looking to see if there is a
function that will return the day of the week (Monday, Tuesday,
Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.
See help for the Format() function.
Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

Best Practice is to use a table.

--
Mike Sherrill
Information Management Systems

[Lyle Fairfield]

pd******@steelc ase.com (PamelaDV) wrote in news:b6783f18.0 403041220.39301 038
@posting.google .com:

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

An expression like

DatePart("ww", vDate, vbSunday, vbFirstJan1)

can return a week of the year.

The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
results in keeping with your local practice.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[CDB]

Or WeekDayName() function.

I followed Mike's suggestion of using a table a few years back, and it has
proved to be invaluable. The only system I know which handles the week
across the year change is the ISO system. My USA clients seem entirely happy
with the result - they probably do not realise that it is (gasp)
International!

Clive
"Mike Sherrill" <MS************ *@compuserve.co m> wrote in message
news:jp******** *************** *********@4ax.c om...

On 4 Mar 2004 12:20:16 -0800, pd******@steelc ase.com (PamelaDV) wrote:

I have two problems, actually. I am looking to see if there is a
function that will return the day of the week (Monday, Tuesday,
Wednesday, etc...) from a date. For instance 2/27/04 is a Friday.

See help for the Format() function.

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

Best Practice is to use a table.

--
Mike Sherrill
Information Management Systems

[Wayne Gillespie]

On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************ @Invalid.Com> wrote:

pd******@steel case.com (PamelaDV) wrote in news:b6783f18.0 403041220.39301 038
@posting.googl e.com:

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

An expression like

DatePart("ww ", vDate, vbSunday, vbFirstJan1)

can return a week of the year.

The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
results in keeping with your local practice.

Q200299 BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year

The article includes a function which returns the correct weeknumber to the ISO standard.
Wayne Gillespie
Gosford NSW Australia

[CDB]

Plus the wrong year, in certain cases. It was worse pre-A2K! Here is my
solution:

Public Function getISODate(varD ate As Variant) As String
'revised 13/5/03 to patch bug in Oleaut32.dll
Dim dtDate As Date
Dim intYear As Integer
Dim intWeek As Integer
Dim intDay As Integer
Dim intDayOfYear As Integer
Dim strRslt As String
On Err GoTo procerr
strRslt = "0000000"
If IsDate(varDate) Then
dtDate = CDate(varDate)
intYear = Year(dtDate)
If DatePart("ww", dtDate + 7, vbMonday, vbFirstFourDays ) = 2 Then
intWeek = 1
Else
intWeek = DatePart("ww", dtDate, vbMonday, vbFirstFourDays )
End If
intDay = Weekday(dtDate, vbMonday)
intDayOfYear = DatePart("y", dtDate, vbMonday, vbFirstFourDays )
Select Case intDayOfYear
Case Is < 4
If intDay > 4 Then intYear = intYear - 1
Case Is > 362
If intDay < 4 Then intYear = intYear + 1
End Select
strRslt = CStr(intYear) & right$("0" & intWeek, 2) &
right$(Str$(int Day), 1)
End If
procexit:
On Error Resume Next
getISODate = strRslt
Exit Function
procerr:
MsgBox Err.Description
Resume procexit
End Function

Public Function getISOWk(mydate As Variant) As String
getISOWk = Mid(getISODate( mydate), 5, 2)
End Function

Clive
"Wayne Gillespie" <be*****@NObest fitsoftwareSPAM .com.au> wrote in message
news:3s******** *************** *********@4ax.c om...

On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************ @Invalid.Com> wrote:

pd******@steel case.com (PamelaDV) wrote in news:b6783f18.0 403041220.39301 [e-mail] e.com:

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.
An expression like

DatePart("ww ", vDate, vbSunday, vbFirstJan1)

can return a week of the year.

The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
results in keeping with your local practice.

Q200299 BUG: Format or DatePart Functions Can Return Wrong Week Number for

Last Monday in Year
The article includes a function which returns the correct weeknumber to the ISO standard.

Wayne Gillespie
Gosford NSW Australia

[Lyle Fairfield]

Wayne Gillespie <be*****@NObest fitsoftwareSPAM .com.au> wrote in
news:3s******** *************** *********@4ax.c om:

On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************ @Invalid.Com>
wrote:

pd******@stee lcase.com (PamelaDV) wrote in
news:b6783f18 .0403041220.393 01038 @posting.google .com:

Also is there a way to group dates by week? I have a user who wants a
report to group by week. I know I could create a table and assign
dates to a week, but I'm wondering if anyone has anything out there
already done that may be a little bit simpler.

An expression like

DatePart("ww" , vDate, vbSunday, vbFirstJan1)

can return a week of the year.

The enumerators vbSunday, vbFirstJan1 can be manipulated to modify the
results in keeping with your local practice.

Q200299 BUG: Format or DatePart Functions Can Return Wrong Week Number
for Last Monday in Year

The article includes a function which returns the correct weeknumber to
the ISO standard.

Thank you for drawing this to my attention.

Is any error returned for
DatePart("ww", vDate, vbSunday, vbFirstJan1)?

Does the error occur only for
DatePart("ww", vDate, vbMonday, vbFirstFourDays )?
[that is for calculating the ISO 8601 standard week number?]

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)