Report setup, bi-weekly periods

[rinmanb70]

I use a db for keeping up with my checkbook and I'm having trouble
setting up a new functionality. I get paid every two weeks on Friday,
and I need a report to figure my balance that includes only
transactions that are dated before the date of my next payday. Any
transactions that are dated on or after my next payday (ones I have
scheduled at my bank's website) I'd like to have NOT included in my
balance. Is there a way to do this?

[Bob Quintal]

rinmanb70 <em**********@gmail.comwrote in
news:11**********************@p77g2000hsh.googlegr oups.com:

I use a db for keeping up with my checkbook and I'm having
trouble setting up a new functionality. I get paid every two
weeks on Friday, and I need a report to figure my balance that
includes only transactions that are dated before the date of
my next payday. Any transactions that are dated on or after
my next payday (ones I have scheduled at my bank's website)
I'd like to have NOT included in my balance. Is there a way
to do this?

yes, use the dateadd() function to add 14 days to the current
payday. Filter the query so that the transaction date
criteria is BETWEEN payday AND dateadd("d",14,payday).
payday could be a passed parameter or looked up
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[rinmanb70]

But how will it figure all upcoming paydays as time goes on?

[Bob Quintal]

rinmanb70 <em**********@gmail.comwrote in
news:11**********************@g4g2000hsf.googlegro ups.com:

But how will it figure all upcoming paydays as time goes on?

You will have to tell it the paydays. Use the same dateadd function
to add two weeks to a known payday and store it in a table.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[rinmanb70]

I use this code now for my twice a month paydays. Is there not an
equivalent (simple) way for bi-weekly paydays?

If todays date was between the 1st and the 14th of the month, I need
transactions dated before the 15th to be included in my balance and
here's how I find them:

IIf([DateTransaction]<DateSerial(Year(Date()),Month(Date()),15)

And if today's date is between the 15th and the last day of the month,
I want the transactions dated before the first day of next month to be
included in my balance. Here's how I do it:

IIf([DateTransaction]<DateSerial(Year(Date()),Month(Date())+1,1)

[Bob Quintal]

rinmanb70 <em**********@gmail.comwrote in
news:11**********************@q69g2000hsb.googlegr oups.com:

I use this code now for my twice a month paydays. Is there
not an equivalent (simple) way for bi-weekly paydays?

If todays date was between the 1st and the 14th of the month,
I need transactions dated before the 15th to be included in my
balance and here's how I find them:

IIf([DateTransaction]<DateSerial(Year(Date()),Month(Date

()),15)

>
And if today's date is between the 15th and the last day of
the month, I want the transactions dated before the first day
of next month to be included in my balance. Here's how I do
it:

IIf([DateTransaction]<DateSerial(Year(Date()),Month(Date())+

1,1

)

It's not so simple for fized intervals between dates.

Somewhere, for bi-weekly paydays, you will need a reference
date. From that date, you can calculate every date that falls
15*N days after that date.

Store your reference date in a 1 column, 1 row table. Then you
need to determine the number of days between that date and the
current date. take the modulus 15 of that number, That is the
number of days since last payday. Subtract that from the current
date and you have the starting date of the current pay period.



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[rinmanb70]

OK, thanks for the help.
I knew I'd have to somewhere reference the first payday date to begin
with, but I didn't know what form it should take.
I can set up a dedicated form with only one column and one record and
it will be the first payday date.
After that I'm afraid I don't understand what you're meaning.
Can you help me with "15*N" and "modulus 15" ?
Even though I don't understand these things right now, I'm encouraged
because it seems like you understand what I'm trying to do.
Thanks again...

[rinmanb70]

I looked up modulus, so I think I'm beginning to understand that it
could used to find the number of days since my last paycheck. What is
the syntax for getting access to return a modulus? Can Access's
handling of rounding decimal numbers be set so that it would always
round up or down? Which way would I want it to be rounded for my
purpose?

[rinmanb70]

I've done more now. I set up a simple report for testing. I'm using
DateDiff and then dividing the result by 14. However, it seems to
always round down to the nearest whole number instead of returning a
remainder. How can I get the remainder? I think I have an idea of
how to use the remainder to figure the number of days since the last
payday.

[rinmanb70]

Success!
Here's how I did it:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)

Dim HardDate As Date
Dim NumberOfDaysSince As Integer
Dim modulus As Integer
Dim DaysToNext As Integer
Dim nextpayday As Date

DateToday = Date
HardDate = #5/9/2007#
NumberOfDaysSince = DateDiff("d", HardDate, DateToday)
modulus = NumberOfDaysSince Mod 14
DaysToNext = 14 - modulus
nextpayday = Date + DaysToNext

I'm pretty new to Access and VBA, so I'd love to hear any suggestions
you have on anything I should change or ways it could be better/best
practices, etc.
btw, thanks for your guidance. Your suggestion about finding the
modulus was THE simple solution I had been hoping for (people at
another forum were talking about embedding a dmax into a dateadd
statement and using a dedicated table of paydays...) I knew (hoped)
there'd be a better way.

[CDMAPoster]

On May 28, 10:43 pm, rinmanb70 <[e-mail]:

Success!
Here's how I did it:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)

Dim HardDate As Date
Dim NumberOfDaysSince As Integer
Dim modulus As Integer
Dim DaysToNext As Integer
Dim nextpayday As Date

DateToday = Date
HardDate = #5/9/2007#
NumberOfDaysSince = DateDiff("d", HardDate, DateToday)
modulus = NumberOfDaysSince Mod 14
DaysToNext = 14 - modulus
nextpayday = Date + DaysToNext

I'm pretty new to Access and VBA, so I'd love to hear any suggestions
you have on anything I should change or ways it could be better/best
practices, etc.
btw, thanks for your guidance. Your suggestion about finding the
modulus was THE simple solution I had been hoping for (people at
another forum were talking about embedding a dmax into a dateadd
statement and using a dedicated table of paydays...) I knew (hoped)
there'd be a better way.

You've found something you understand and like so stick with that, but
here are some additional ideas about bi-weekly date computations:

http://groups.google.com/group/micro...20d274163ed81d

A few comments:

I wrote a checkbook balancing database back in Access 2.0. I found it
helpful to be able to include a checkbox so that I could run "what if"
scenarios to help identify checks that are still afloat.

The HardDate looks a little, well, hard. Much of your code can be
moved to a separate function that would allow you to grab a flexible
date from a form.

Although it was fashionable not long ago to simplify date math by
adding an integer number of days to a date, I would replace:

nextpayday = Date + DaysToNext

with:

nextpayday = DateAdd("d", DaysToNext, Date)

This fits in more with modular programming techniques where an
interface hides a particular implementation. By not depending on the
details of a particular implementation, Microsoft can change the
implementation and as long as they update the date functions also,
nothing will break. It is very unlikely that Microsoft is going to
change how dates are implemented anytime soon, but my training as a
mathematician and as an engineer causes me to try to anticipate such
possibilities and to err on the side of too much robustness when
possible :-).

James A. Fortune
CD********@FortuneJames.com

[rinmanb70]

I wrote a checkbook balancing database back in Access 2.0.

Do you still use it? Why or why not (if you don't mind)? I was just
telling my wife last night that it seems to me like anyone who knows
Access should do this.

>I found it
helpful to be able to include a checkbox so that I could run "what if"
scenarios to help identify checks that are still afloat.

Considering your expertise and professional qualifications, I'm proud
to be able to tell you that I already have a report I called Unposted
Transactions. It shows transactions that are checking (as opposed to
non-checking, like credit card transactions) and that do not have a
post date (I enter post dates using a calendar form for each checking
transaction).

>
The HardDate looks a little, well, hard. Much of your code can be
moved to a separate function that would allow you to grab a flexible
date from a form.

Actually I only called it a hard date on my test db just because it
was a set date that would represent my first bi-weekly payday and
subsequent paydays would be figured from that one. I can not really
anticipate any need to change that date after I'm up and running so
I'm thinking there's no need for a form to key it or another date into
for that purpose.

Although it was fashionable not long ago to simplify date math by
adding an integer number of days to a date, I would replace:

nextpayday = Date + DaysToNext

with:

nextpayday = DateAdd("d", DaysToNext, Date)

I'll follow your advice and do this, and being new to all this Access
and VBA, it'll give me an example of how to use DateAdd too.

This fits in more with modular programming techniques where an
interface hides a particular implementation.

I don't know what you mean here, but I'd like to if you don't mind
elaborating.

>By not depending on the
details of a particular implementation, Microsoft can change the
implementation and as long as they update the date functions also,
nothing will break. It is very unlikely that Microsoft is going to
change how dates are implemented anytime soon, but my training as a
mathematician and as an engineer causes me to try to anticipate such
possibilities and to err on the side of too much robustness when
possible :-).

I'm with you here. Anticipating as many things that can go wrong as
possible and investing some time on the front end can eliminate major
headaches, not to mention pains in the @55 later.
Thanks for your help,
Chris

[Bob Quintal]

rinmanb70 <em**********@gmail.comwrote in
news:11**********************@m36g2000hse.googlegr oups.com:

Success!
Here's how I did it:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount
As Integer)

Dim HardDate As Date
Dim NumberOfDaysSince As Integer
Dim modulus As Integer
Dim DaysToNext As Integer
Dim nextpayday As Date

DateToday = Date
HardDate = #5/9/2007#
NumberOfDaysSince = DateDiff("d", HardDate, DateToday)
modulus = NumberOfDaysSince Mod 14
DaysToNext = 14 - modulus
nextpayday = Date + DaysToNext

I'm pretty new to Access and VBA, so I'd love to hear any
suggestions you have on anything I should change or ways it
could be better/best practices, etc.
btw, thanks for your guidance. Your suggestion about finding
the modulus was THE simple solution I had been hoping for
(people at another forum were talking about embedding a dmax
into a dateadd statement and using a dedicated table of
paydays...) I knew (hoped) there'd be a better way.

If it works for you... I'm glad you learned to fish, tis better
than me giving you a fish. :-)

You are new to Access and VBA, but you've already learned how to
use, datediff(), mod, and other things too.

I personally would store the harddate in a table and dlookup()
to retreive it, simply because you can make a form for users to
edit it if the company changes payday from Friday to Thursday.
rather than letting them edit code.

You define DateToday as date, then use it in one place and date
itself in another. DateToday is really redundant. .
modulus is really redundant too, You are using variables instead
of comments to document your code.

A common practice, and useful if you have lots of variables, is
to use a naming convention, say i for integer, dt as date, b for
boolean (true/false) and st for string variables.

so your vars become
Dim dtHard As Date
Dim dtNextPay As Date

you entire procedure can be written as
dtHard = #5/9/2007#
dtNextPay = date + 14 - DateDiff("d", dtHard, Date) mod 14
(all on 1 line)

but that's just my style.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[rinmanb70]

I personally would store the harddate in a table and dlookup()

to retreive it, simply because you can make a form for users to
edit it if the company changes payday from Friday to Thursday.
rather than letting them edit code.

I see what you mean and I agree philosophically, but it is only for me
and my wife to do our checkbook (mostly me). btw, I changed
"HardDate" to FirstPayday".

You define DateToday as date, then use it in one place and date
itself in another. DateToday is really redundant.

I caught that after looking at the code a little more and corrected it
by removing the variable for Date().

modulus is really redundant too,
You are using variables instead
of comments to document your code.

I do use apostrophes to explain it later to myself (for future
reference after I've completely forgotten how in the world I did
something) but this was a rough draft just used to figure out how to
do the bi-weekly thing.

A common practice, and useful if you have lots of variables, is
to use a naming convention, say i for integer, dt as date, b for
boolean (true/false) and st for string variables.

so your vars become
Dim dtHard As Date
Dim dtNextPay As Date

Good tip, I'll do this.

you entire procedure can be written as
dtHard = #5/9/2007#
dtNextPay = date + 14 - DateDiff("d", dtHard, Date) mod 14
(all on 1 line)

but that's just my style.

Impressive to me. Like a trainee watching the veteran work.
Thanks for your time.

[CDMAPoster]

On May 29, 5:09 pm, rinmanb70 <[e-mail]:

I wrote a checkbook balancing database back in Access 2.0.

Do you still use it? Why or why not (if you don't mind)? I was just
telling my wife last night that it seems to me like anyone who knows
Access should do this.

I wrote it for a store in connection with their POS system. At the
end of the day, Access would read and parse the cash register data by
department. Access also wrote out checks for various expenses,
providing cash flow data for each category of goods sold. It is still
in use today. The ideas I learned from that database form the core
for much of the custom business software I wrote for much larger
companies later.

Considering your expertise and professional qualifications, I'm proud
to be able to tell you that I already have a report I called Unposted
Transactions. It shows transactions that are checking (as opposed to
non-checking, like credit card transactions) and that do not have a
post date (I enter post dates using a calendar form for each checking
transaction).

It sounds like you're getting the hang of it. To clarify, the "what
if" scenarios are usually done prior to the bank statement. I might
make a deposit and get a current balance for the account. With a few
clicks I can usually tell which checks have cleared without having to
bother a bank teller or having to wait for the statement.

The HardDate looks a little, well, hard. Much of your code can be
moved to a separate function that would allow you to grab a flexible
date from a form.

Actually I only called it a hard date on my test db just because it
was a set date that would represent my first bi-weekly payday and
subsequent paydays would be figured from that one. I can not really
anticipate any need to change that date after I'm up and running so
I'm thinking there's no need for a form to key it or another date into
for that purpose.

I see. The initial date I cited didn't need to change either.

I'll follow your advice and do this, and being new to all this Access
and VBA, it'll give me an example of how to use DateAdd too.

This fits in more with modular programming techniques where an
interface hides a particular implementation.

I don't know what you mean here, but I'd like to if you don't mind
elaborating.

I'll try to come up with a different example for you.

James A. Fortune
CD********@FortuneJames.com

[rinmanb70]

This fits in more with modular programming techniques where an

interface hides a particular implementation.

I don't know what you mean here, but I'd like to if you don't mind
elaborating.

I'll try to come up with a different example for you.

Don't go to any trouble. I've re-read the passage a few times and
believe I understand what you mean now. At least the gist of it...
Thanks again for your help.