Date Partition Function
in [Help]
|
Prev: Lock File Issue - 2007
Next: Are there problems linking tables in mdb files with Access 2007 and SQL 2008 ?
From: Craig on 13 Apr 2010 13:23 I have a circumstance where I need to calculate counts based on user set start times, end times, and intervals. It looks like the partition function (http://office.microsoft.com/en-us/access/ HA012288921033.aspx) would do exactly that but it requires whole numbers not dates. SO...... 1) Is there a partition function based on dates? 2) Is there a way to convert dates to numbers to use the function? 3) Is there another way to get the same sort of effect as the partition function?
From: Bob Quintal on 13 Apr 2010 17:42
Craig <cjohns38(a)gmail.com> wrote in news:400494cd-4af5-4941-855f-063647e15055(a)i37g2000yqn.googlegroups.co m: > I have a circumstance where I need to calculate counts based on > user set start times, end times, and intervals. It looks like the > partition function (http://office.microsoft.com/en-us/access/ > HA012288921033.aspx) would do exactly that but it requires whole > numbers not dates. SO...... > > 1) Is there a partition function based on dates? > 2) Is there a way to convert dates to numbers to use the function? > 3) Is there another way to get the same sort of effect as the > partition function? > Dates or date/times? date/times are stored in Access tables as the number of days since December 31st, 1899, with the time as a fraction of a day. You could therefore convert today to a long integer using clng(date()) which is day 40281 You could alternatively use several of the date functions to get the specified interval I regularly run a totals query by month using the datepart("yyyy",[dt_created])*100+datepart("ww",[dt_created]) in a totals query to return the count of records added each week -- Bob Quintal PA is y I've altered my email address. |