Perpetual due dates

[Jan :\)]

Hi all, Access 2007, Vista Ultimate

I am setting up a monthly budget and need to address a few areas that I have
not quite figured out, and hoping I can get some advise.

1. I have monthly expenses such as rent/gas/elec/storage/etc., each with
their own due date each month. I have a separate Table for Creditors,
should I add a DueDate field to his table?

2. I have an entry form to record the bills for each when recieved each
month and the amount due. I only want to be able to enter the current
amount due for each billing, but, not sure if I need a separate table for
BillsPd, or some other name.

3. I want to be able to have a popup prompt that will come up as soon as the
db is opened within 7 days prior to the due date of each bill, so as to be
sure that the bill is not overlooked, so this would mean it would have to
look up the due date for that creditor and determine the date needed for the
prompt. Never done this before, so not sure how to set that up.

As I am in the planning stages of such at the moment, I would truly
appreciate any suggestions as to how to accomplish the end results, if
possible. I have yet to find anything, over the many years I have used
Access, that I have wanted/needed to do with this program that it has not
been capable of doing with the help of the expertise and talent of the
people who help in these groups.

Jan

 

[Steve]

Consider the following tables ....
TblCreditor
CreditorID
CreditorName
<Creditor contact fields>

TblExpense
ExpenseID
Expense 'rent/gas/elec/storage/etc
CreditorID

TblExpenseBill
ExpenseBillID
ExpenseID
DateReceived
AmountDue
DueDate
DatePaid

For 3 you need a query that includes all three tables and the following
fields:
CreditorName
Expense
DateReceived
AmountDue
DueDate
DatePaid

Put the following criteria in the DueDate field:
<=Date() + 7

Put the following criteria in the DatePaid field:
Is Null

This criteria will give you all bills that have not been paid and where the
due date is 7 or less days after today's date.

Now you need to create a continuous form based on this query that will list
those bills if any that need immediate attention. You can make this the
startup form or if you have a startup form already, you can put code in the
open event that opens the "Bills" form in acDialog mode.

Steve

 

[John W. Vinson]

Hi all, Access 2007, Vista Ultimate

I am setting up a monthly budget and need to address a few areas that I have
not quite figured out, and hoping I can get some advise.

1. I have monthly expenses such as rent/gas/elec/storage/etc., each with
their own due date each month. I have a separate Table for Creditors,
should I add a DueDate field to his table?

2. I have an entry form to record the bills for each when recieved each
month and the amount due. I only want to be able to enter the current
amount due for each billing, but, not sure if I need a separate table for
BillsPd, or some other name.

3. I want to be able to have a popup prompt that will come up as soon as the
db is opened within 7 days prior to the due date of each bill, so as to be
sure that the bill is not overlooked, so this would mean it would have to
look up the due date for that creditor and determine the date needed for the
prompt. Never done this before, so not sure how to set that up.

As I am in the planning stages of such at the moment, I would truly
appreciate any suggestions as to how to accomplish the end results, if
possible. I have yet to find anything, over the many years I have used
Access, that I have wanted/needed to do with this program that it has not
been capable of doing with the help of the expertise and talent of the
people who help in these groups.

Jan

I would REALLY, REALLY suggest that you not reinvent this particular wheel (or
to be more exact, this particular automobile). You can get Microsoft Money, or
Quicken, or Peachtree for a tiny fraction of the cost of reinventing such an
application yourself, and any of these programs does what you ask and much
more, with many programmer-years of development already invested.

 

[Jan :\)]

I would REALLY, REALLY suggest that you not reinvent this particular wheel
(or
to be more exact, this particular automobile). You can get Microsoft
Money, or
Quicken, or Peachtree for a tiny fraction of the cost of reinventing such
an
application yourself, and any of these programs does what you ask and much
more, with many programmer-years of development already invested.

Thanks, John. I'll take at look at these. I just didn't see the point in
buying a bank to cash a $5.00 check. Some of the ready-made products are too
inflaxible for and geared more for business than home use. But, I will look
in to the ones you suggest.

Thank you for your time and suggestions, much appreciated.

Jan

 

[Jan :\)]

Thanks, Steve.

Jan

Consider the following tables ....
TblCreditor
CreditorID
CreditorName
<Creditor contact fields>

TblExpense
ExpenseID
Expense 'rent/gas/elec/storage/etc
CreditorID

TblExpenseBill
ExpenseBillID
ExpenseID
DateReceived
AmountDue
DueDate
DatePaid

For 3 you need a query that includes all three tables and the following
fields:
CreditorName
Expense
DateReceived
AmountDue
DueDate
DatePaid

Put the following criteria in the DueDate field:
<=Date() + 7

Put the following criteria in the DatePaid field:
Is Null

This criteria will give you all bills that have not been paid and where
the due date is 7 or less days after today's date.

Now you need to create a continuous form based on this query that will
list those bills if any that need immediate attention. You can make this
the startup form or if you have a startup form already, you can put code
in the open event that opens the "Bills" form in acDialog mode.

Steve

 

[Klatuu]

Actually, both Quicken and Microsoft Money are aimed at managing personal
accounts. They both offer business level applications, but that isn't what
you want.

I am with John on this one.

I have been using Quicken for my checking account an billpaying for over 10
years and am very happy with it.

Everything you describe can be done and much more, but just because a
feature is there doesn't mean you have to use it.

The think I like most about Quicken is it has the most intuitive data entery
UI I have ever encountered.

--
Dave Hargis, Microsoft Access MVP

I would REALLY, REALLY suggest that you not reinvent this particular wheel
(or
to be more exact, this particular automobile). You can get Microsoft
Money, or
Quicken, or Peachtree for a tiny fraction of the cost of reinventing such
an
application yourself, and any of these programs does what you ask and much
more, with many programmer-years of development already invested.

Thanks, John. I'll take at look at these. I just didn't see the point in
buying a bank to cash a $5.00 check. Some of the ready-made products are too
inflaxible for and geared more for business than home use. But, I will look
in to the ones you suggest.

Thank you for your time and suggestions, much appreciated.

Jan

 

[Jan :\)]

Actually, both Quicken and Microsoft Money are aimed at managing personal
accounts. They both offer business level applications, but that isn't
what
you want.

I am with John on this one.

I have been using Quicken for my checking account an billpaying for over
10
years and am very happy with it.

Everything you describe can be done and much more, but just because a
feature is there doesn't mean you have to use it.

The think I like most about Quicken is it has the most intuitive data
entery
UI I have ever encountered.

Thank you for the confirmation on the Quicken....I will look at that and see
how it works for me. I only have 5 creditors that I need to deal with for a
joint account. We alternate paying the expenses, and the other party is not
really that up to snuff in doing the entry stuff, so I *really* need to keep
it simple. I thought that by doing it in Access I could create a custom
user part that is super simple, and get it to do all the heavy lifting in
the background, otherwise, they may not want to use it, and I am not always
available to do the entry for the incoming bills and monthly payments.

Thank you very much for your time and help, it is much appreciated.

Jan

--
Dave Hargis, Microsoft Access MVP

Hi all, Access 2007, Vista Ultimate

I am setting up a monthly budget and need to address a few areas that I
have
not quite figured out, and hoping I can get some advise.

1. I have monthly expenses such as rent/gas/elec/storage/etc., each
with
their own due date each month. I have a separate Table for Creditors,
should I add a DueDate field to his table?

2. I have an entry form to record the bills for each when recieved each
month and the amount due. I only want to be able to enter the current
amount due for each billing, but, not sure if I need a separate table
for
BillsPd, or some other name.

3. I want to be able to have a popup prompt that will come up as soon
as
the
db is opened within 7 days prior to the due date of each bill, so as to
be
sure that the bill is not overlooked, so this would mean it would have
to
look up the due date for that creditor and determine the date needed
for
the
prompt. Never done this before, so not sure how to set that up.

As I am in the planning stages of such at the moment, I would truly
appreciate any suggestions as to how to accomplish the end results, if
possible. I have yet to find anything, over the many years I have used
Access, that I have wanted/needed to do with this program that it has
not
been capable of doing with the help of the expertise and talent of the
people who help in these groups.

Jan



I would REALLY, REALLY suggest that you not reinvent this particular
wheel
(or
to be more exact, this particular automobile). You can get Microsoft
Money, or
Quicken, or Peachtree for a tiny fraction of the cost of reinventing
such
an
application yourself, and any of these programs does what you ask and
much
more, with many programmer-years of development already invested.

Thanks, John. I'll take at look at these. I just didn't see the point
in
buying a bank to cash a $5.00 check. Some of the ready-made products are
too
inflaxible for and geared more for business than home use. But, I will
look
in to the ones you suggest.

Thank you for your time and suggestions, much appreciated.

Jan

 

[Steve]

Jan,

You also might want to look at past due bills!!!

Make a copy of your previous query and put the following criteria in the
DueDate field:
<Date()

Make a copy of your form and use this query as the recordsource to get a
list of past due bills.

Steve

 

[Jan :\)]

Jan,

You also might want to look at past due bills!!!

Make a copy of your previous query and put the following criteria in the
DueDate field:
<Date()

Make a copy of your form and use this query as the recordsource to get a
list of past due bills.

Yes, I want a past due prompt as well , to alert me when bills are past due.
That could mean they had not been paid, or perhaps not entered, either of
which will beed attention.

Thank you for the additional information. It is truly appreciated.

Jan

 

[Jan :\)]

One thing that still confuses me...

I need to set a beginning due date for each creditor (there are only 5),
each has their own specific due date. Example....house payment is due on the
4th of each month, gas bill is due on the 11th, electric bill due on the
17th of each month, etc. How do I enter that kind of date information so
that each subsequent due date for that creditor can be calculated against
that due date when it is entered into the db? In the case of long term and
on-going expenses, this could also include more than one year.

Jan

 

[Steve]

All you need to do is to move the field DueDate from TblExpenseBill to
TblExpense and account for this change if you built your forms yet.

Steve

 

[Steve]

My previous response was too hasty!!!

Change your tables to look like this:

TblExpense
ExpenseID
Expense 'rent/gas/elec/storage/etc
CreditorID


TblExpenseBill
ExpenseBillID
ExpenseID
DateReceived
YearDue
MonthDue
AmountDue
DatePaid

In your query, use the DateSerial function with DayOfMonthDue, YearDue and
MonthDue to get the due date. Eliminate the DueDate field because it can
always be calculated. Look up the DateSerial function in the Help file.

Steve

 

[Jan :\)]

All you need to do is to move the field DueDate from TblExpenseBill to
TblExpense and account for this change if you built your forms yet.

Ok, thanks, I am still building, so good timing.

Jan

 

[Jan :\)]

My previous response was too hasty!!!

Change your tables to look like this:

You sure?...?? ))

TblExpense
ExpenseID
Expense 'rent/gas/elec/storage/etc
CreditorID


TblExpenseBill
ExpenseBillID
ExpenseID
DateReceived
YearDue
MonthDue
AmountDue
DatePaid

In your query, use the DateSerial function with DayOfMonthDue, YearDue and
MonthDue to get the due date. Eliminate the DueDate field because it can
always be calculated. Look up the DateSerial function in the Help file.

OK...thank you, I will work with this and see how it pans out. Hopefully, it
will be gold. <g>

Jan

 

[Steve]

Jan,

Sorry!!!

Add a field named DayOfMonthDue to TblExpense.

Use the DateSerial function like this in your query:

DueDate = DateSerial(YearDue,MonthDue,DayOfMonthDue)

Dyeve

 

[Jan :\)]

Jan,

Sorry!!!

Add a field named DayOfMonthDue to TblExpense.

Use the DateSerial function like this in your query:

DueDate = DateSerial(YearDue,MonthDue,DayOfMonthDue)

Dyeve

Thanks, Steve, had't got that far yet so no harm done. I truly appreciate
you time and help.

Jan

 

[John... Visio MVP]

Jan, Sorry!!!

Add a field named DayOfMonthDue to TblExpense.
Use the DateSerial function like this in your query:
DueDate = DateSerial(YearDue,MonthDue,DayOfMonthDue)

Dyeve

Still posting incompetent answers?

These newsgroups are provided by Microsoft for FREE peer to peer support,
not for you to prey on unsuspecting victims.

Your feeble attempt to get respectablility by posting inadequate answers
will not compensate for your disregarding the rules by trolling for work.

John... Visio MVP