Bi-Weekly Grouping Custom start date?

[Guest]

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....

 

[Guest]

You can set your sorting and group expression to something like:
=DateDiff("d",#1/11/2007#,[TheDate])\14

 

[Guest]

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

You can set your sorting and group expression to something like:
=DateDiff("d",#1/11/2007#,[TheDate])\14
--
Duane Hookom
Microsoft Access MVP

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....

 

[Guest]

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

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

You can set your sorting and group expression to something like:
=DateDiff("d",#1/11/2007#,[TheDate])\14
--
Duane Hookom
Microsoft Access MVP

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....

 

[Guest]

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

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

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

You can set your sorting and group expression to something like:
=DateDiff("d",#1/11/2007#,[TheDate])\14
--
Duane Hookom
Microsoft Access MVP


:

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....

 

[Guest]

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

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

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

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


:

You can set your sorting and group expression to something like:
=DateDiff("d",#1/11/2007#,[TheDate])\14
--
Duane Hookom
Microsoft Access MVP


:

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....

 

[Guest]

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

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

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

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


:

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


:

You can set your sorting and group expression to something like:
=DateDiff("d",#1/11/2007#,[TheDate])\14
--
Duane Hookom
Microsoft Access MVP


:

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....

 

[Guest]

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

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

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

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



:

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


:

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


:

You can set your sorting and group expression to something like:
=DateDiff("d",#1/11/2007#,[TheDate])\14
--
Duane Hookom
Microsoft Access MVP


:

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....