creating a follow up report by date and updating dates

[willmafingerdo]

Hello,


I need to create a follow up report for X amount of time based off of a given code. I think I might know how to do it but I don't know how to write the IIf statement.

something like: Followup: iif([FollowUpDate]>=[StartDate],[FollowUpDate]=[StartDate] and [FollowUpDate]=[StartDate]+Dateadd("m",X amount of time,[StartDate]))

Access will accept the statement but it doesn't work... can anyone offer any input please?
Thanks!!
Will

[JoeM]

An IIF statement has three arguments
1st argument - Condition to Check: Should result in a TRUE/FALSE calculation
2nd argument - What to return if TRUE: this cannot do anything other than return some value or calculation
3rd argument - What to return if FALSE: this cannot do anything other than return some value or calculation

So, your first argument looks fine. But it looks like you are trying to set some fields equal to some other fields or something in your other arguments. You cannot do that. All you can do is to return a value (though that value can be a calculation).

If you cannot figure out how to do what you want, please explain to us (in plain English), exactly what this calculation should be doing.

[willmafingerdo]

Here's what's going on:
Each record will have a start date, and a follow up date. The follow up date comes from a case code in the file. When the follow up date arrives I need a report of various data from the records to do a follow up on them (name, case num, ect..). Then I need to reset the start date and repeat.

I was thinking if I used an update query I would be able to reset the original start date to the original follow up date once the day arrives to keep a running start date each time the follow up date comes.

[JoeM]

In an Update Query, the key components are:
- The criteria (which records are being selected to be updated)
- The field being updated
- What it is being Updated to

See here for an example: http://www.databasedev.co.uk/update_query.html

[willmafingerdo]

I think i got it started, I have the query that calculates the follow up date


and everything looks good, this is what it shows when i run it


but when i run the update query:


This is what the dates look like after i run the update query 1 time:

I was wondering if anyone could give me some idea of why it adding extra years to what is given.

[willmafingerdo]

I got this figured out, I just had to use the dateadd function in the update query and added date()>= [StartDate] to the criteria.