if statement with SELECT

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mr_doles - 07 Dec 2005 20:00 GMT

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?

Reply to this Message

John Vinson - 08 Dec 2005 00:30 GMT

>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:
[quoted text clipped - 7 lines]
>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]

Reply to this Message

mr_doles - 08 Dec 2005 19:08 GMT

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.

Reply to this Message