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**********@g mail.comwrote in
news:11******** **************@ p77g2000hsh.goo glegroups.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**********@g mail.comwrote in
news:11******** **************@ g4g2000hsf.goog legroups.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(Yea r(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(Yea r(Date()),Month (Date())+1,1)

[Bob Quintal]

rinmanb70 <em**********@g mail.comwrote in
news:11******** **************@ q69g2000hsb.goo glegroups.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(Yea r(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(Yea r(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_Fo rmat(Cancel As Integer, FormatCount As
Integer)

Dim HardDate As Date
Dim NumberOfDaysSin ce As Integer
Dim modulus As Integer
Dim DaysToNext As Integer
Dim nextpayday As Date

DateToday = Date
HardDate = #5/9/2007#
NumberOfDaysSin ce = DateDiff("d", HardDate, DateToday)
modulus = NumberOfDaysSin ce 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.