automatically update a field

[smoker1]

How can I make an update query that updates on the first of each month? (I know how to do it every so many days, but that doesn't work for my need.)

Thanks in advance for your help!!

--Angela

 

[WildHare]

Well, if you think about it, you need to do three things here.

1) Check to see if it's first of the month.
2) If it is, check to see if the update has been run yet.
3) If it hasn't, run it.

You'll probably need to make a tiny table with a field in it to store some flag that you can check , to see if you've already run this guy once today (today being the first of the month, right?...)

You can do that pretty much any of about 10 different ways, or just ignore it and run the update every time you open the database on the first of the month. That's up to you.

But here's how to check to see if TODAY is the FIRST of the month:

IF DATE() = 
   DateSerial(Year(date), Month(date),1)
THEN
   "Today is the first day of the month"
ELSE
   "Today is not the first day of the month"

HTH


Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[demoman]

If you need to add more sophistication to this process, you can use the Task Scheduler. I have several 'housekeeping' routines I run with it.

I create a small VB program to perform the task (update, archive, etc.). I then add the .exe to the Scheduler. It can be run with a wide variety of timing options.

WildHare is correct in stating you need some control(s) for these processes. I use log files. I get notified by e-mail when a process has successfully completed, or when it failed.

This takes a little work setting up, but really works well when you have many process to maintain/monitor.

Steve