Auto Update Duedate field

[Guest]

My problem is i want to update my clients duedate automatically, even i dont
open my Clients database file. So that when i want to look for those duedate
using parameter for dates they are already updated

For Quartely update

lets say my clients first duedate is January 13, 2006 supposed to be the
next dute will be April 13, 2006. But i want on March 13, 2005, 30 days
before the next due the Duedate field will automatically update January 13,
2006 to April 13, 2006. So that on March 13, 2006 i can look for those Due
date under April 13, 2006.

My Idea Code for Quarterly Update

lets say "If Now Date is 60 days greater to Duedate then Add 90 days to Due
Date"

I dont know the code and where to put it, but i know only this sentence
statements.

Pls help me on this
I want to be productive on my career with business.

Thank You

 

[Ron Hinds]

Without knowing your table and field names I'll just have to guess. You'll
need to replace the names I chose with whatever is appropriate for your
situation.

DoCmd.RunSQL "UPDATE clients_table_name SET duedate_field_name =
DateAdd(""m"", 3, [duedate_field_name]) WHERE DateAdd(""m"", 2,
[duedate_field_name]) = #" & Date & "#"

What this will do is update the duedate to a date 3 months greater than it
currently is when today's date is 2 months greater than the current duedate.

Put the code in the Open event for your form. Note it will only work if you
open the form at least once every day.

 

[Guest]

Sir Ron i really appreciate your help, luckyly your suggestion is near to
solve my problem.
I'am in an insurance business, particularly in car, non-life insurance that
my clients got to their certain duedate in a certain Day and certain Month.


My question is this.

How can i update their duedates automatically even not opening my database
in a certain period of time. Because currently i have clients numbering about
867 clients and their preferred modes are Quarterly. Certainly i have a hard
time to update them one by one. Im sure if you will put your shoes on my
shoes, certainly you will look for an alternative in manual updating to
Automatic update, like what i did manually in a past 5 years.

On my example yesterday that if my clients firstduedate is Jan 13, 2006,
luckyly i have 46 clients on this duedate. Certainly Sir it is a 4 cycle,
their future duedates are April 13, 2006, July 13, 2006, October 13, 2006,
Jan 13, 2007 and so on... But if i remind those 46 clients on their Exact
duedate, Certainly their policy will lapse, even there is a grace period of
one month. Because they will get disappointed on our system.

Sir i am creating a relationship with them, i want to REMIND their Duedates
One Month before their Exactduedate, so that they are Ready for their
payments One Month before. They have the time to accumulate a savings for
their Car Insurance.

Lets say Jan 13, 2006 is their firstduedate certainly April 13, 2006 is the
next duedate. But i want on March 13, 2006, 60 days greater to Jan 13, 2006.
My duedate field will turn the Jan 13, 2006 record to April 13, 2006
automatically, even not opening my database file. So that when i open my
datebase report on March 14, 2006 and type a Parameter in my duedatefied for
Beginning Date which is April 1, 2006 and Ending Date which is April 13,
2006, my report will show my 46 clients duedates under April 13, 2006.


Sir I certainly need your HELP,

Sorry for long elaboration, but i just want to explain my problem deeper.

Thank and God Bless you

 

[John Vinson]

How can i update their duedates automatically even not opening my database
in a certain period of time.

One suggestion:

Don't store the duedate AT ALL.

Instead, calculate it in a Query based on the maximum value of the
first duedate and the current date.

Then whenever you open the database, it will have the next due date.

John W. Vinson[MVP]

 

[Guest]

Don't store the duedate AT ALL.

Instead, calculate it in a Query based on the maximum value of the
first duedate and the current date.

Then whenever you open the database, it will have the next due date.

John W. Vinson[MVP]

Sir John I appreciate your help, your idea comes on my mind once, but it is
hard for me to manage my database. I'd like my database to manage its data
automatically even i dont manage them. My work is not only managing database.
I have also to do selling, setting appointments and other things related to
my work. Managing my database from time to time is really unmanageble for me.
I guess you can help me on my problem.


Thank you sir

 

[John Vinson]

Sir John I appreciate your help, your idea comes on my mind once, but it is
hard for me to manage my database. I'd like my database to manage its data
automatically even i dont manage them. My work is not only managing database.
I have also to do selling, setting appointments and other things related to
my work. Managing my database from time to time is really unmanageble for me.
I guess you can help me on my problem.

My suggestion requires absolutely ZERO intervention or action on your
part, once you have set up the query.

The due date will be calculated based on the stored starting date and
the current date. You or any other user will need to open a Form
(which could of course be opened automatically when you open the
database), select the account, and see the renewal date for that
account right there on the screen.

Again... my suggestion does not require that *you* run any query; it
does not require that some program update any record in your table.
It's all automatic.

John W. Vinson[MVP]

 

[Guest]

So you mean sir, I dont need any code?

Sir i really appreciate your effort and your time.
Iam just an intermediate user of Acces.

Please sir give your idea on how to update duedates, whether it is quartery,
semi-annual, and annually. Without doing anything on my Database File. I'll
just let the Acces to do updates automatically and when i need the data. It's
there, already updated.

Imagine sir i will update my 437 clients manually? It is a waste of my time.
That why Sir i am really mad crazy to look for an Alternative on how to
update them AUTOMATICALLY.

Thank You Sir.

I hope you can offer a solution.

God Bless You.

 

[John Vinson]

So you mean sir, I dont need any code?

Sir i really appreciate your effort and your time.
Iam just an intermediate user of Acces.

Please sir give your idea on how to update duedates, whether it is quartery,
semi-annual, and annually. Without doing anything on my Database File. I'll
just let the Acces to do updates automatically and when i need the data. It's
there, already updated.

Imagine sir i will update my 437 clients manually? It is a waste of my time.
That why Sir i am really mad crazy to look for an Alternative on how to
update them AUTOMATICALLY.

The solution I would recommend is:


DO NOT store the DueDate in *any* table, *anywhere*. If the due date
isn't stored, it doesn't need to be updated.

Instead, store the information necessary to *calculate* the DueDate,
and create a stored Query calculating the next due date, based on that
information and the computer clock's value of the current date. This
query will use the builtin DateAdd function.

Display this Query on a Form, and it will show each of your 437
client's current due date, calculated at the moment you open the
database, with no action required on your part.

Your original post said:

----
lets say my clients first duedate is January 13, 2006 supposed to be
the next dute will be April 13, 2006. But i want on March 13, 2005, 30
days before the next due the Duedate field will automatically update
January 13, 2006 to April 13, 2006. So that on March 13, 2006 i can
look for those Due date under April 13, 2006.
----

I'm afraid I don't fully understand. What is coming "due"? If nothing
happens on a client's duedate, does the duedate automatically change?
Do some clients have quarterly, some annual, some semiannual updates?
How do you determine (on the basis of what is stored in the table)
whether a given client is quarterly, semiannual, or annual?

I'll be glad to help you create this Query which should take care of
displaying current due dates for all your clients in the future - but
at present I don't clearly understand how you determine the duedate.



John W. Vinson[MVP]

 

[Guest]

Sir you are near to solve my problem i really appreciate it.

Sir if you dont mind can you provide me a sample code on how to update Jan
24, 2006 to April 24, 2006 which is the Duedate on March 24, 2006 30days
before the Due Date on April 24 2006 Automatically even not opening my data
base until March 30, 2006.

Thank You Sir and GodBless.

 

[John Vinson]

Sir you are near to solve my problem i really appreciate it.

Sir if you dont mind can you provide me a sample code on how to update Jan
24, 2006 to April 24, 2006 which is the Duedate on March 24, 2006 30days
before the Due Date on April 24 2006 Automatically even not opening my data
base until March 30, 2006.

Thank You Sir and GodBless.

Donjie...

Please reread this thread.

You have kept asking the same question again and again.

You have ignored my suggestions.

It is

ABSOLUTELY IMPOSSIBLE

to *update* a field stored in a table without opening the database.

It is also

ABSOLUTELY UNNECESSARY

to do so, if you realise that you do *not* need to store the Duedate
in the table, but instead calculate it on demand.

I'm trying to help, but it seems you are either misunderstanding or
rejecting my help.

John W. Vinson[MVP]