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 9 7266
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(useDate as Date) As String
Dim dayNum as Integer
If Not IsDate(useDate) Then Exit Function
dayNum = DatePart("w",Date)
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",Date). 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******@steelcase.com> wrote in message
news:b6**************************@posting.google.c om... 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
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******@steelcase.com> wrote in message
news:b6**************************@posting.google.c om... 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
Check the help files for WeekDay().
--
=================
Jeremy Wallace
AlphaBet City Dataworks
ABCDataworks dot com
"PamelaDV" <pd******@steelcase.com> wrote in message
news:b6**************************@posting.google.c om... 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
On 4 Mar 2004 12:20:16 -0800, pd******@steelcase.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 pd******@steelcase.com (PamelaDV) wrote in news:b6783f18.0403041220.39301038
@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)
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.com> wrote in message
news:jp********************************@4ax.com... On 4 Mar 2004 12:20:16 -0800, pd******@steelcase.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
On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************@Invalid.Com> wrote: pd******@steelcase.com (PamelaDV) wrote in news:b6783f18.0403041220.39301038 @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.
Wayne Gillespie
Gosford NSW Australia
Plus the wrong year, in certain cases. It was worse pre-A2K! Here is my
solution:
Public Function getISODate(varDate 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$(intDay), 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*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:3s********************************@4ax.com... On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************@Invalid.Com>
wrote:pd******@steelcase.com (PamelaDV) wrote in
news:b6783f18.0403041220.39301038@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.
Wayne Gillespie Gosford NSW Australia
Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in
news:3s********************************@4ax.com: On 6 Mar 2004 13:48:07 GMT, Lyle Fairfield <Mi************@Invalid.Com> wrote:
pd******@steelcase.com (PamelaDV) wrote in news:b6783f18.0403041220.39301038 @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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Hennie de Nooijer |
last post by:
Hi, This is a diffcult issue to explain. I hope to make my problem
clear to you.
SITUATION
I'm building A SLA Query for a customer. This customer has an awkward
way to determine the SLA results...
|
by: Shuffs |
last post by:
Could someone, anyone please tell me what I need to amend, to get this
function to take Sunday as the first day of the week?
I amended the Weekday parts to vbSunday (in my code, not the code...
|
by: deko |
last post by:
This runs, but does not narrow to current week.
suggestions appreciated!
SELECT lngEid, dtmApptDate, Subject, Appt_ID
FROM qry002
WHERE (dtmApptDate BETWEEN
DateAdd("d",-weekday()+2,) And...
|
by: Mark Reed |
last post by:
Hi all,
I am using office XP and have a question regarding the format function.
I am using wk:format(.,"WW") to show what week a
certain date is. The problem is that a week runs from Sun to Sat in...
|
by: Ray |
last post by:
I need to convert the normal calendar to show the week no., the period no.
and the financial year. The financial year format is as follows:-
Date start: 2 May, 2005
7 days a week, 4 weeks a...
|
by: Lee Harr |
last post by:
I wrote a function to return the first date of a given week
(and a few related functions) :
-- return the first date in the given week
CREATE or REPLACE FUNCTION week_start(integer, integer)...
|
by: Jana |
last post by:
Greetings Access Gurus! I am working on an app to send batch
transactions to our bank, and the bank requires that we place an
effective date on our files that is 'one business day in the future,...
|
by: sbaird |
last post by:
Aloha from Hawaii,
I'm beating my head on the wall here. I have a recruiting contact
managment database I'm trying to create. Managers (there ar 14 of
them) have to make a certain number of...
|
by: Keo Sophon |
last post by:
Fredrik Lundh wrote:
Hi,
I've tried calendar.month_name, it displays empty string, while
calendar.month_name is "January"? Why does calendar.month_name's
index not start with index 0 as...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
| |