Perpetual due dates

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" wrote in message
...
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 " wrote in message
...
Thanks, Steve.

Jan

"Steve" wrote in message
m...
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

"Jan " wrote in message
...
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

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



"Jan " wrote in message
...
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" wrote in message
...
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 " wrote in message
...
Thanks, Steve.

Jan

"Steve" wrote in message
m...
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

"Jan " wrote in message
...
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

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 " wrote in message
...
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" wrote in message
...
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 " wrote in message
...
Thanks, Steve.

Jan

"Steve" wrote in message
m...
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

"Jan " wrote in message
...
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" wrote in message
...
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

Steve



"Jan " wrote in message
...
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" wrote in message
...
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 " wrote in message
...
Thanks, Steve.

Jan

"Steve" wrote in message
m...
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

"Jan " wrote in message
...
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" wrote in message
m...
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 " wrote in message
...
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" wrote in message
...
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 " wrote in message
...
Thanks, Steve.

Jan

"Steve" wrote in message
m...
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

"Jan " wrote in message
...
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

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 " wrote in message
...

"Steve" wrote in message
m...
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 " wrote in message
...
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" wrote in message
...
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 " wrote in message
...
Thanks, Steve.

Jan

"Steve" wrote in message
m...
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

"Jan " wrote in message
...
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" wrote in message
m...
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



"Jan " wrote in message
...

"Steve" wrote in message
m...
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 " wrote in message
...
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" wrote in message
...
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 " wrote in message
...
Thanks, Steve.

Jan

"Steve" wrote in message
m...
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

"Jan " wrote in message
...
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" wrote in message
m...
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 " wrote in message
...

"Steve" wrote in message
My previous response was too hasty!!!
Change your tables to look like this:
Steve


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