Date update problem

[AngiW]

I think I'm overthinking this, so if someone can tell me the easy way, i'd
appreciate it. I'm new to AccXP...was used to Acc97, so treat me like an
idiot.

I'm trying to write an accrual db. My table consists of used, avail, carried
and earned fields for vac and sick. Every last day of the month you get 5
hours sick and then some other number for vacation is earned every month
according to your anniversary date. My problem is having the db update each
record when the db is opened using today's date without going into each record.
Without doing a slew of If statements, i'm not sure how to do this. I figure
I can create and use a hidden Last Updated field to make sure it doesn't do it
more than once for a given month, but how do i tell it the last day of every
month when months end in 28, 29, 30, or 31? Thanks!!

Angi

 

[John Spencer (MVP)]

You can use the DateSerial function to get the last day of a month.

DateSerial(Year(Date()),Month(Date())+1,0)

The above returns the last day of the month for the current date - Jan 31, 2004
right now.

 

[AngiW]

Uh....how do get to the Open or Load action in Acc02? Told ya....treat me like
an idiot. If they kept it the same, i'd be fine!

Thanks,
Ang

 

[John Spencer (MVP)]

Uhmm! Several ways, but it sounds as if you don't have much of a background at all.

The simplest way to catch the fact that the database is loading would be to use
a startup macro. Try using the keywords "macros, Autoexec" in the online help
and see if that gives you information on how to build a macro that executes at
startup. Then you could test the current date against the end of the current
month (or test to see if the current date is the first of the month) and if so
execute your SQL statement.