A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Aggregating Date Data into Weeks and Quarters



 
 
Thread Tools Display Modes
  #1  
Old July 11th, 2004, 05:28 AM
Roger
external usenet poster
 
Posts: n/a
Default Aggregating Date Data into Weeks and Quarters

I want to aggregate date data into weeks of the year. I have checked all date/time function and I can't seem to locate these types of functions. Seems strange to me since Access must have an internal calendar(s) to use the DateDiff function and other functions. What am I missing? Seems like this is a very common need. Any help would be greatly appreciated.

Thanks, Roger at
  #2  
Old July 11th, 2004, 06:04 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default Aggregating Date Data into Weeks and Quarters

Did you review the Format() function as well as DatePart()?

--
Duane Hookom
MS Access MVP


"Roger" wrote in message
...
I want to aggregate date data into weeks of the year. I have checked all

date/time function and I can't seem to locate these types of functions.
Seems strange to me since Access must have an internal calendar(s) to use
the DateDiff function and other functions. What am I missing? Seems like
this is a very common need. Any help would be greatly appreciated.

Thanks, Roger at



  #3  
Old July 11th, 2004, 12:59 PM
Gary Walter
external usenet poster
 
Posts: n/a
Default Aggregating Date Data into Weeks and Quarters


"Roger" wrote:
I want to aggregate date data into weeks of the year. I have checked all date/time function and I can't seem to locate these types of functions. Seems strange to me since Access must have an internal calendar(s) to use the DateDiff function and other functions. What am I missing? Seems like this is a very common need.


Hi Roger,

Here be a "distilled" overview of Dates in Access:

A Date *is not a string*. It's stored internally as a double float number.
From Access Help:
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
that represent dates ranging from 1 January 100 to 31 December 9999
and times from 0:00:00 to 23:59:59.
Any recognizable literal date values can be assigned to Date variables.
Date literals must be enclosed within number signs (#), for example, #January 1, 1993# or #1 Jan 93#.
Date variables display dates according to the short date format recognized by your computer.
Times display according to the time format (either 12-hour or 24-hour) recognized by your computer.
When other numeric types are converted to Date,
values to the left of the decimal represent date information (number of days since Dec. 30 ,1899)
while values to the right of the decimal represent time.
Midnight is 0 and midday is 0.5.
************************************************** ******************
Positive whole numbers represent number of days since Dec 30, 1899.
Negative whole numbers represent number of days before 30 December 1899
************************************************** ***************
Literal dates (surrounded by "#") must be in US format.
One method to provide literals and assure US format:
Format(yourdate, "\#mm\/dd\/yyyy\#")
SELECT ... WHERE [datefield] = Format(yourdate, "\#mm\/dd\/yyyy\#");
--------------------------
Date/Time Functions:
-------------------------
Now() Returns a Variant (Date) specifying the current date *** and time *** according your computer's system date and time.
Date() Returns a Variant (Date) containing the current system date (time part is always "0" = midnight)
Time() Returns a Variant (Date) indicating the current system time (date part is always 12/30/1899)
DateSerial(year, month, day) Returns a Variant (Date) for a specified year, month, and day.
( "Powerful" function--year,month,day can be any expressions that result in integer)
"intervals" used by following 3 functions
" yyyy" Year | "y" Day of year
"q" Quarter | "ww" Week of Year (1-54)
"m" Month | "w" Day of Week(1-7)
"d" Day | *except DateDiff("w",..) -# of weeks
"h" Hour | "n" Minute
"s" Second
DateAdd(interval, number, date) Returns a Variant (Date) containing a date to which a specified time interval has been added.
DateDiff(interval, Earlierdate1, Laterdate2) --- (note earlier date goes first) Returns a Variant (Long) specifying the number
intervals between two specified dates.
DatePart(interval, date[,firstdayofweek[, firstweekofyear]]) Returns a Variant (Integer) containing the specified part of a given date.

DateValue(date) Returns a Variant (Date) of any expression that can represent a date, a time, or both a date and time
Day(date) Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month
Weekday(date, [firstdayofweek]) Returns a Variant (Integer) containing a whole number representing the day of the week.
WeekdayName(weekday,[ abbreviate(Boolean), firstdayofweek]) Returns a string indicating the specified day of the week. WeekdayName(1)="Sunday"
Month(date) Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year
MonthName(month[, abbreviate(Boolean)]) Returns a string indicating the specified month. MonthName(1) = "January"
Year(date) Returns a Variant (Integer) containing a whole number representing the year
TimeSerial(inthour,intmin,intsec) Returns a Variant (Date) containing the time for a specific hour, minute, and second.
TimeValue(strtime) Returns a Variant (Date) containing the time. (same as using date literals--#strTime#)
IsDate(expression) returns True if the expression is a date or can be converted to a valid date; otherwise, it returns False.
CDate(date expression) Coerces "date expression" to type date Where "date expression" is
any expression that can be interpreted as a date, including date literals, numbers that look like dates,
strings that look like dates, and dates returned from functions. A date expression is limited to numbers
or strings, in any combination, that can represent a date from January 1, 100 – December 31, 9999.
************************************************** **************
Date( ) vs Now( )

?Now()
8/23/2001 12:40:42 AM
?Date()
8/23/2001
?Format(Date(),"mm/dd/yyyy hh:mm")
08/23/2001 00:00 ------ time portion of Date() is always midnight (the "start" of the day)
(NOTE: Format(...) will always return a STRING, no longer working w/ a DATE/TIME)
************************************************** **************

How can I display the weeknumber of the year in a query?????

DatePart("ww",Date()) would return the week number for today's date (1-54).

************************************************** ****************
LastYear : Year(Date()) - 1
LastDayOfMonth : DateSerial(Year([Adate]), Month([Adate]) + 1, 0)
DaysInMonth : Day(DateSerial(Year([Adate]), Month([Adate]) + 1, 0))
12:00 AM of First day of current month one year ago : DateSerial(Year(Date()) -1, Month(Date()), 1)
12:00 AM of Last day of current month one year ago : DateSerial(Year(Date()) -1, Month(Date()) + 1, 0)
11:59 PM of Last day of current month one year ago :ateAdd("n", 1439, DateSerial(Year(Date()) -1, Month(Date()) + 1, 0))
12:00 AM of Last day of current month one year ago + 24 hrs : DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
FirstDay of PayPeriodThisWeek = Date - WeekDay(Date,0) -1
LastDayofPayPeriodThisWeek = Date - WeekDay(Date,0) +7

12:00 AM of Friday of last week =DateAdd("d",-1- WeekDay(Date()), Date()) = Date() -1 - WeekDay(Date())
12:00 AM of Saturday of last week=DateAdd("d",- WeekDay(Date()), Date()) = Date() - WeekDay(Date())
12:00 AM of Sunday of this week=DateAdd("d", 1 - WeekDay(Date()), Date()) = Date() +1 - WeekDay(Date())
12:00 AM of Monday of this week=DateAdd("d", 2 - WeekDay(Date()), Date()) = Date() +2 - WeekDay(Date())
etc.
12:00 AM of Friday of this week=DateAdd("d", 6 - WeekDay(Date()), Date()) = Date() +6 - WeekDay(Date())
12:00 AM of Saturday of this week=DateAdd("d", 7 - WeekDay(Date()), Date()) = Date()+7 - WeekDay(Date())
12:00 AM of Sunday of next week=DateAdd("d", 8 - WeekDay(Date()), Date()) = Date() +8 - WeekDay(Date())

************************************************** ****************
  #4  
Old July 11th, 2004, 05:56 PM
Duane Hookom
external usenet poster
 
Posts: n/a
Default Aggregating Date Data into Weeks and Quarters

I feel like you should get some kind of award for "Posts above and beyond". Thanks for being so thorough.

--
Duane Hookom
MS Access MVP


"Gary Walter" wrote in message ...

"Roger" wrote:
I want to aggregate date data into weeks of the year. I have checked all date/time function and I can't seem to locate these types of functions. Seems strange to me since Access must have an internal calendar(s) to use the DateDiff function and other functions. What am I missing? Seems like this is a very common need.


Hi Roger,

Here be a "distilled" overview of Dates in Access:

A Date *is not a string*. It's stored internally as a double float number.
From Access Help:
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
that represent dates ranging from 1 January 100 to 31 December 9999
and times from 0:00:00 to 23:59:59.
Any recognizable literal date values can be assigned to Date variables.
Date literals must be enclosed within number signs (#), for example, #January 1, 1993# or #1 Jan 93#.
Date variables display dates according to the short date format recognized by your computer.
Times display according to the time format (either 12-hour or 24-hour) recognized by your computer.
When other numeric types are converted to Date,
values to the left of the decimal represent date information (number of days since Dec. 30 ,1899)
while values to the right of the decimal represent time.
Midnight is 0 and midday is 0.5.
************************************************** ******************
Positive whole numbers represent number of days since Dec 30, 1899.
Negative whole numbers represent number of days before 30 December 1899
************************************************** ***************
Literal dates (surrounded by "#") must be in US format.
One method to provide literals and assure US format:
Format(yourdate, "\#mm\/dd\/yyyy\#")
SELECT ... WHERE [datefield] = Format(yourdate, "\#mm\/dd\/yyyy\#");
--------------------------
Date/Time Functions:
-------------------------
Now() Returns a Variant (Date) specifying the current date *** and time *** according your computer's system date and time.
Date() Returns a Variant (Date) containing the current system date (time part is always "0" = midnight)
Time() Returns a Variant (Date) indicating the current system time (date part is always 12/30/1899)
DateSerial(year, month, day) Returns a Variant (Date) for a specified year, month, and day.
( "Powerful" function--year,month,day can be any expressions that result in integer)
"intervals" used by following 3 functions
" yyyy" Year | "y" Day of year
"q" Quarter | "ww" Week of Year (1-54)
"m" Month | "w" Day of Week(1-7)
"d" Day | *except DateDiff("w",..) -# of weeks
"h" Hour | "n" Minute
"s" Second
DateAdd(interval, number, date) Returns a Variant (Date) containing a date to which a specified time interval has been added.
DateDiff(interval, Earlierdate1, Laterdate2) --- (note earlier date goes first) Returns a Variant (Long) specifying the number
intervals between two specified dates.
DatePart(interval, date[,firstdayofweek[, firstweekofyear]]) Returns a Variant (Integer) containing the specified part of a given date.

DateValue(date) Returns a Variant (Date) of any expression that can represent a date, a time, or both a date and time
Day(date) Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month
Weekday(date, [firstdayofweek]) Returns a Variant (Integer) containing a whole number representing the day of the week.
WeekdayName(weekday,[ abbreviate(Boolean), firstdayofweek]) Returns a string indicating the specified day of the week. WeekdayName(1)="Sunday"
Month(date) Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year
MonthName(month[, abbreviate(Boolean)]) Returns a string indicating the specified month. MonthName(1) = "January"
Year(date) Returns a Variant (Integer) containing a whole number representing the year
TimeSerial(inthour,intmin,intsec) Returns a Variant (Date) containing the time for a specific hour, minute, and second.
TimeValue(strtime) Returns a Variant (Date) containing the time. (same as using date literals--#strTime#)
IsDate(expression) returns True if the expression is a date or can be converted to a valid date; otherwise, it returns False.
CDate(date expression) Coerces "date expression" to type date Where "date expression" is
any expression that can be interpreted as a date, including date literals, numbers that look like dates,
strings that look like dates, and dates returned from functions. A date expression is limited to numbers
or strings, in any combination, that can represent a date from January 1, 100 – December 31, 9999.
************************************************** **************
Date( ) vs Now( )

?Now()
8/23/2001 12:40:42 AM
?Date()
8/23/2001
?Format(Date(),"mm/dd/yyyy hh:mm")
08/23/2001 00:00 ------ time portion of Date() is always midnight (the "start" of the day)
(NOTE: Format(...) will always return a STRING, no longer working w/ a DATE/TIME)
************************************************** **************

How can I display the weeknumber of the year in a query?????

DatePart("ww",Date()) would return the week number for today's date (1-54).

************************************************** ****************
LastYear : Year(Date()) - 1
LastDayOfMonth : DateSerial(Year([Adate]), Month([Adate]) + 1, 0)
DaysInMonth : Day(DateSerial(Year([Adate]), Month([Adate]) + 1, 0))
12:00 AM of First day of current month one year ago : DateSerial(Year(Date()) -1, Month(Date()), 1)
12:00 AM of Last day of current month one year ago : DateSerial(Year(Date()) -1, Month(Date()) + 1, 0)
11:59 PM of Last day of current month one year ago :ateAdd("n", 1439, DateSerial(Year(Date()) -1, Month(Date()) + 1, 0))
12:00 AM of Last day of current month one year ago + 24 hrs : DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
FirstDay of PayPeriodThisWeek = Date - WeekDay(Date,0) -1
LastDayofPayPeriodThisWeek = Date - WeekDay(Date,0) +7

12:00 AM of Friday of last week =DateAdd("d",-1- WeekDay(Date()), Date()) = Date() -1 - WeekDay(Date())
12:00 AM of Saturday of last week=DateAdd("d",- WeekDay(Date()), Date()) = Date() - WeekDay(Date())
12:00 AM of Sunday of this week=DateAdd("d", 1 - WeekDay(Date()), Date()) = Date() +1 - WeekDay(Date())
12:00 AM of Monday of this week=DateAdd("d", 2 - WeekDay(Date()), Date()) = Date() +2 - WeekDay(Date())
etc.
12:00 AM of Friday of this week=DateAdd("d", 6 - WeekDay(Date()), Date()) = Date() +6 - WeekDay(Date())
12:00 AM of Saturday of this week=DateAdd("d", 7 - WeekDay(Date()), Date()) = Date()+7 - WeekDay(Date())
12:00 AM of Sunday of next week=DateAdd("d", 8 - WeekDay(Date()), Date()) = Date() +8 - WeekDay(Date())

************************************************** ****************
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.