Bi-Weekly Grouping Custom start date? - microsoft.public.access.reports

You can set your sorting and group expression to something like: =DateDiff("d",#1/11/2007#,[TheDate])\14 -- Duane Hookom Microsoft Access MVP "GrassHopper" wrote: > A little better then a novice user. > > I have a personal expenses DB. I would like to group it bi-weekly (my > pay-day) starting from 01/11/2007 (my date format mm/dd/yyyy). > > Thanks....

Thanks Duane: I've done your suggestion, replacing [TheDate] with my "field-name" and it worked! I wonder if you could help my understanding a bit? I've read a little about the Datediff function and I think I understand for my expression: "d" to be the Datepart #1/11/2007# to be my startdate. Questions: How does my "field-name" correlate to enddate? How does \14 work in relation to this expression? P.S. How could I now get the Bi-weekly Pay period dates dynamically displayed with each group header? I appreciate all the help and insight you've given me, and I hope I haven't been too much trouble. Thanks... William "Duane Hookom" wrote: > You can set your sorting and group expression to something like: > =DateDiff("d",#1/11/2007#,[TheDate])\14 > -- > Duane Hookom > Microsoft Access MVP > > > "GrassHopper" wrote: > > > A little better then a novice user. > > > > I have a personal expenses DB. I would like to group it bi-weekly (my > > pay-day) starting from 01/11/2007 (my date format mm/dd/yyyy). > > > > Thanks....

DateDiff() as provided finds the number of days between your start date and the date field in your query. I don't know what you mean by "enddate". The \14 does an integer division. It divides the number of days by 14 and returns only the integer part (rounds down). This gives you 14 day periods which you asked for. Whatever the value of the expression is, you should be able to multiple the result times 14 and add to your start date to bet the beginning of any time period. -- Duane Hookom Microsoft Access MVP "GrassHopper" wrote: > Thanks Duane: > I've done your suggestion, replacing [TheDate] with my "field-name" and it > worked! > I wonder if you could help my understanding a bit? I've read a little about > the Datediff function and I think I understand for my expression: > "d" to be the Datepart > #1/11/2007# to be my startdate. > Questions: > How does my "field-name" correlate to enddate? > How does \14 work in relation to this expression? > > P.S. How could I now get the Bi-weekly Pay period dates dynamically > displayed with each group header? I appreciate all the help and insight > you've given me, and I hope I haven't been too much trouble. > Thanks... > William > > > "Duane Hookom" wrote: > > > You can set your sorting and group expression to something like: > > =DateDiff("d",#1/11/2007#,[TheDate])\14 > > -- > > Duane Hookom > > Microsoft Access MVP > > > > > > "GrassHopper" wrote: > > > > > A little better then a novice user. > > > > > > I have a personal expenses DB. I would like to group it bi-weekly (my > > > pay-day) starting from 01/11/2007 (my date format mm/dd/yyyy). > > > > > > Thanks....

Sorry Duane: I'm trying it but not getting it. I would like to have a date heading for each 2 week period such as: Pay Period For 01/11/2007 (the 2 week records) Pay Period For 01/25/2007 (the 2 week records) Pay Period For 02/08/2007 ....etc Is it possible you could provide a expression example? Sorry! Thanks.. William "Duane Hookom" wrote: > DateDiff() as provided finds the number of days between your start date and > the date field in your query. I don't know what you mean by "enddate". > > The \14 does an integer division. It divides the number of days by 14 and > returns only the integer part (rounds down). This gives you 14 day periods > which you asked for. > > Whatever the value of the expression is, you should be able to multiple the > result times 14 and add to your start date to bet the beginning of any time > period. > -- > Duane Hookom > Microsoft Access MVP > > > "GrassHopper" wrote: > > > Thanks Duane: > > I've done your suggestion, replacing [TheDate] with my "field-name" and it > > worked! > > I wonder if you could help my understanding a bit? I've read a little about > > the Datediff function and I think I understand for my expression: > > "d" to be the Datepart > > #1/11/2007# to be my startdate. > > Questions: > > How does my "field-name" correlate to enddate? > > How does \14 work in relation to this expression? > > > > P.S. How could I now get the Bi-weekly Pay period dates dynamically > > displayed with each group header? I appreciate all the help and insight > > you've given me, and I hope I haven't been too much trouble. > > Thanks... > > William > > > > > > "Duane Hookom" wrote: > > > > > You can set your sorting and group expression to something like: > > > =DateDiff("d",#1/11/2007#,[TheDate])\14 > > > -- > > > Duane Hookom > > > Microsoft Access MVP > > > > > > > > > "GrassHopper" wrote: > > > > > > > A little better then a novice user. > > > > > > > > I have a personal expenses DB. I would like to group it bi-weekly (my > > > > pay-day) starting from 01/11/2007 (my date format mm/dd/yyyy). > > > > > > > > Thanks....

In the group header, you can add a text box with a control source of: ="Pay Period For " & (DateDiff("d",#1/11/2007#,[TheDate])\14)*14+#1/11/2007# -- Duane Hookom Microsoft Access MVP "GrassHopper" wrote: > Sorry Duane: > I'm trying it but not getting it. I would like to have a date heading for > each 2 week period such as: > Pay Period For 01/11/2007 > (the 2 week records) > Pay Period For 01/25/2007 > (the 2 week records) > Pay Period For 02/08/2007 ....etc > > Is it possible you could provide a expression example? > Sorry! > Thanks.. > William > > > > "Duane Hookom" wrote: > > > DateDiff() as provided finds the number of days between your start date and > > the date field in your query. I don't know what you mean by "enddate". > > > > The \14 does an integer division. It divides the number of days by 14 and > > returns only the integer part (rounds down). This gives you 14 day periods > > which you asked for. > > > > Whatever the value of the expression is, you should be able to multiple the > > result times 14 and add to your start date to bet the beginning of any time > > period. > > -- > > Duane Hookom > > Microsoft Access MVP > > > > > > "GrassHopper" wrote: > > > > > Thanks Duane: > > > I've done your suggestion, replacing [TheDate] with my "field-name" and it > > > worked! > > > I wonder if you could help my understanding a bit? I've read a little about > > > the Datediff function and I think I understand for my expression: > > > "d" to be the Datepart > > > #1/11/2007# to be my startdate. > > > Questions: > > > How does my "field-name" correlate to enddate? > > > How does \14 work in relation to this expression? > > > > > > P.S. How could I now get the Bi-weekly Pay period dates dynamically > > > displayed with each group header? I appreciate all the help and insight > > > you've given me, and I hope I haven't been too much trouble. > > > Thanks... > > > William > > > > > > > > > "Duane Hookom" wrote: > > > > > > > You can set your sorting and group expression to something like: > > > > =DateDiff("d",#1/11/2007#,[TheDate])\14 > > > > -- > > > > Duane Hookom > > > > Microsoft Access MVP > > > > > > > > > > > > "GrassHopper" wrote: > > > > > > > > > A little better then a novice user. > > > > > > > > > > I have a personal expenses DB. I would like to group it bi-weekly (my > > > > > pay-day) starting from 01/11/2007 (my date format mm/dd/yyyy). > > > > > > > > > > Thanks....

Thank You Duane: It worked perfectly! Does multiplying the expression's value by 14 convert the expression's "bi-weekly" integer value to days, and then this value added to the "startdate" gives the starting bi-weekly period date? Thanks Again, for your help!! William " Hookom" wrote: > In the group header, you can add a text box with a control source of: > ="Pay Period For " & (DateDiff("d",#1/11/2007#,[TheDate])\14)*14+#1/11/2007# > -- > Duane Hookom > Microsoft Access MVP > > > "GrassHopper" wrote: > > > Sorry Duane: > > I'm trying it but not getting it. I would like to have a date heading for > > each 2 week period such as: > > Pay Period For 01/11/2007 > > (the 2 week records) > > Pay Period For 01/25/2007 > > (the 2 week records) > > Pay Period For 02/08/2007 ....etc > > > > Is it possible you could provide a expression example? > > Sorry! > > Thanks.. > > William > > > > > > > > "Duane Hookom" wrote: > > > > > DateDiff() as provided finds the number of days between your start date and > > > the date field in your query. I don't know what you mean by "enddate". > > > > > > The \14 does an integer division. It divides the number of days by 14 and > > > returns only the integer part (rounds down). This gives you 14 day periods > > > which you asked for. > > > > > > Whatever the value of the expression is, you should be able to multiple the > > > result times 14 and add to your start date to bet the beginning of any time > > > period. > > > -- > > > Duane Hookom > > > Microsoft Access MVP > > > > > > > > > "GrassHopper" wrote: > > > > > > > Thanks Duane: > > > > I've done your suggestion, replacing [TheDate] with my "field-name" and it > > > > worked! > > > > I wonder if you could help my understanding a bit? I've read a little about > > > > the Datediff function and I think I understand for my expression: > > > > "d" to be the Datepart > > > > #1/11/2007# to be my startdate. > > > > Questions: > > > > How does my "field-name" correlate to enddate? > > > > How does \14 work in relation to this expression? > > > > > > > > P.S. How could I now get the Bi-weekly Pay period dates dynamically > > > > displayed with each group header? I appreciate all the help and insight > > > > you've given me, and I hope I haven't been too much trouble. > > > > Thanks... > > > > William > > > > > > > > > > > > "Duane Hookom" wrote: > > > > > > > > > You can set your sorting and group expression to something like: > > > > > =DateDiff("d",#1/11/2007#,[TheDate])\14 > > > > > -- > > > > > Duane Hookom > > > > > Microsoft Access MVP > > > > > > > > > > > > > > > "GrassHopper" wrote: > > > > > > > > > > > A little better then a novice user. > > > > > > > > > > > > I have a personal expenses DB. I would like to group it bi-weekly (my > > > > > > pay-day) starting from 01/11/2007 (my date format mm/dd/yyyy). > > > > > > > > > > > > Thanks....

Keep in mind that dates are numbers. Actually today's (Tuesday) date is 39196 or 39196 days since Dec 30, 1899. All I did was use some math to perform some calculations. To figure it all out just divide it into smaller pieces and work it out with pencil and paper. I think you have a good grasp of the numbers. -- Duane Hookom Microsoft Access MVP "GrassHopper" wrote: > Thank You Duane: > > It worked perfectly! > Does multiplying the expression's value by 14 convert the expression's > "bi-weekly" integer value to days, and then this value added to the > "startdate" gives the starting bi-weekly period date? > > Thanks Again, for your help!! > William > > " Hookom" wrote: > > > In the group header, you can add a text box with a control source of: > > ="Pay Period For " & (DateDiff("d",#1/11/2007#,[TheDate])\14)*14+#1/11/2007# > > -- > > Duane Hookom > > Microsoft Access MVP > > > > > > "GrassHopper" wrote: > > > > > Sorry Duane: > > > I'm trying it but not getting it. I would like to have a date heading for > > > each 2 week period such as: > > > Pay Period For 01/11/2007 > > > (the 2 week records) > > > Pay Period For 01/25/2007 > > > (the 2 week records) > > > Pay Period For 02/08/2007 ....etc > > > > > > Is it possible you could provide a expression example? > > > Sorry! > > > Thanks.. > > > William > > > > > > > > > > > > "Duane Hookom" wrote: > > > > > > > DateDiff() as provided finds the number of days between your start date and > > > > the date field in your query. I don't know what you mean by "enddate". > > > > > > > > The \14 does an integer division. It divides the number of days by 14 and > > > > returns only the integer part (rounds down). This gives you 14 day periods > > > > which you asked for. > > > > > > > > Whatever the value of the expression is, you should be able to multiple the > > > > result times 14 and add to your start date to bet the beginning of any time > > > > period. > > > > -- > > > > Duane Hookom > > > > Microsoft Access MVP > > > > > > > > > > > > "GrassHopper" wrote: > > > > > > > > > Thanks Duane: > > > > > I've done your suggestion, replacing [TheDate] with my "field-name" and it > > > > > worked! > > > > > I wonder if you could help my understanding a bit? I've read a little about > > > > > the Datediff function and I think I understand for my expression: > > > > > "d" to be the Datepart > > > > > #1/11/2007# to be my startdate. > > > > > Questions: > > > > > How does my "field-name" correlate to enddate? > > > > > How does \14 work in relation to this expression? > > > > > > > > > > P.S. How could I now get the Bi-weekly Pay period dates dynamically > > > > > displayed with each group header? I appreciate all the help and insight > > > > > you've given me, and I hope I haven't been too much trouble. > > > > > Thanks... > > > > > William > > > > > > > > > > > > > > > "Duane Hookom" wrote: > > > > > > > > > > > You can set your sorting and group expression to something like: > > > > > > =DateDiff("d",#1/11/2007#,[TheDate])\14 > > > > > > -- > > > > > > Duane Hookom > > > > > > Microsoft Access MVP > > > > > > > > > > > > > > > > > > "GrassHopper" wrote: > > > > > > > > > > > > > A little better then a novice user. > > > > > > > > > > > > > > I have a personal expenses DB. I would like to group it bi-weekly (my > > > > > > > pay-day) starting from 01/11/2007 (my date format mm/dd/yyyy). > > > > > > > > > > > > > > Thanks....

Similiar Articles:

Bi-Weekly Grouping Custom start date? - microsoft.public.access ...
A little better then a novice user. I have a personal expenses DB. I would like to group it bi-weekly (my pay-day) starting from 01/11/2007 (my date...

How do I display a custom date range for a graph in a report ...
Bi-Weekly Grouping Custom start date? - microsoft.public.access ... How do I display a custom date range for a graph in a report ... How do I display a custom date range ...

PSI custom fields update - changes task duration and finish date ...
Bi-Weekly Grouping Custom start date? - microsoft.public.access ... PSI custom fields update - changes task duration and finish date ... Bi-Weekly Grouping Custom start ...

change displayed milestone date? - microsoft.public.project ...
... Post Question Groups ... However, with a custom field and formula you can ... Year([Start]) You should now see the start date month ...

custom sort in report grouping - microsoft.public.access.reports ...
Bi-Weekly Grouping Custom start date? - microsoft.public.access ... William "Duane Hookom" wrote: > You can set your sorting and group ... I have also tried to do the ...

How do I add a grouping level to an existing report in Access ...
Bi-Weekly Grouping Custom start date? - microsoft.public.access ... How do I add a grouping level to an existing report in Access ... How do I display a custom date range ...

Custom Field to display start time in Hours and Minutes ...
Bi-Weekly Grouping Custom start date? - microsoft.public.access ... Custom Field to display start time in Hours and Minutes ..... Groups ... Custom Field to display start ...

Read custom fields using PSI - microsoft.public.project.server ...
Bi-Weekly Grouping Custom start date? - microsoft.public.access ... Read custom fields using PSI - microsoft.public.project.server ... Bi-Weekly Grouping Custom start date ...

Week start day - microsoft.public.windows.live.mail.desktop ...
Bi-Weekly Grouping Custom start date? - microsoft.public.access ... A little better then a novice user. I have a personal expenses DB. I would like to group it bi-weekly ...

Starting Access 2007 - microsoft.public.access
But I am being asked to use autonumber for the key but they want the key to start at ... I've posted this elsewhere in this discussion group, but have not received any ...

Bi-Weekly Grouping Custom start date? DataBase
A little better then a novice user. I have a personal expenses DB. I would like to group it bi-weekly (my pay-day) starting from 01/11/2007 (my date form

Microsoft Access: Bi-Weekly Grouping Custom start date? - database ...
database.itags.org: Microsoft Access question: Bi-Weekly Grouping Custom start date?, created at:Sun, 01 Jun 2008 11:54:00 GMT with 199 bytes, last updated: Monday ...

How to Calculate Dates for Bi-Weekly Payroll | eHow.com
You may create a payroll calendar depicting biweekly pay period start and end dates and ... A bi weekly budget planner template can be a useful way to track finances and ...

What Is Bi-Weekly Payroll? | eHow.com
... with a bi-weekly payroll as it is easier to process both pay groups during a bi-weekly ... How to Calculate Dates for Bi-Weekly Payroll. The most common pay cycles are weekly ...

7/26/2012 12:31:54 PM