if statement with SELECT

[mr_doles]

I have a table with 1 row (Fees) and 3 columns: Fee, Next_Fee_Date, and
Freq. I want to write a query that goes like this:

If Fees.Next_Fee_Date < now() then
1) Insert Fee into another table with todays date
2) Add Freq in months to Next_Fee_Date (ie 12/07/05 + 1 = 1/07/05)
Else
Do Nothing

I want this to run with the access application is opend. Is this
do-able?

 

[John Vinson]

I have a table with 1 row (Fees) and 3 columns: Fee, Next_Fee_Date, and
Freq. I want to write a query that goes like this:

If Fees.Next_Fee_Date < now() then
1) Insert Fee into another table with todays date
2) Add Freq in months to Next_Fee_Date (ie 12/07/05 + 1 = 1/07/05)
Else
Do Nothing

I want this to run with the access application is opend. Is this
do-able?

Easily - but you need to wrap your mind around a new paradigm.

Queries ARE NOT PROCEDURES. No "if" logic is needed.

Simply create a Query using <Date() on the Next_Fee_Date field (Now()
does not return today's date, it returns the current date and time
accurate to a few microseconds). Make it an Update query and update
Next_Fee_Date to

DateAdd("m", [Freq], [Next_Fee_Date])

Run this query from the Startup macro or from the Open event of your
startup form and you should be in good shape (assuming that you don't
mind blindly updating an unknown number of records without review or
checking to see if they actually need to be updated!)

John W. Vinson[MVP]

 

[mr_doles]

I got the update statement to work for the date but I am not sure how
to run the other insert if the date is greater then the Next_Fee_Date.
See part 1 of example below.

Looks at Next_Fee_Date, if todays date is later or the same as
Next_Fee_Date I want two things to happen. 1) Insert the Fee and
todays date into another table (an expense table) then 2) Add Freq to
the Next_Fee_Date, that way the next time the form is opened todays
date will be less then Next_Fee_Date and neither one of these things
will happen.