DateAdd and IIf

[CrostonScottish]

Having a few problems putting together an expression with DateAdd and IIf together on a form i have in Access 2003.

Not sure if i am doing this right but I am using a macro with SetValue to run On Exit from field [Frequency of Monitoring] to calculate a new date for the field [Assessment Review Date], depending on the choice made from [Frequency of Monitoring] e.g. "1 Month", "3 Month".
Under SetValue in macro i have set Item as [Assessment Review Date] but not sure how to write statement so that if 1 Month selected from [Frequency of Monitoring] then DateAdd will add 1 month from [Date] field to enter new date +1 month in {Assessment Review Date] or if 3 Months selected, enter new date +3months

[Rabbit]

Try:

iif([Frequency of Monitoring] = "1 Month", DateAdd("m", 1, [Date Field]), DateAdd("m", 3, [Date Field]))

[CrostonScottish]

Thanks. ;) This works a treat but i want to allow a choice of review dates greater than just 1month and 3 month and it to return the appropriate value e.g. +1 month, +3 month, +6 month and +1 year.
Any ideas?

[Rabbit]

Thanks. ;) This works a treat but i want to allow a choice of review dates greater than just 1month and 3 month and it to return the appropriate value e.g. +1 month, +3 month, +6 month and +1 year.
Any ideas?

Nested iif statements or VBA code.

[CrostonScottish]

Nested iif statements if that is easier to just add to my expression in SetValue Macro or VBA if this is more robust.

Cheers! Should make myself a bit clearer.

[Rabbit]

A nested iif statement is a quick and dirty fix, I would go with the VBA code, takes more time but is more robust.

[CrostonScottish]

Sorry but any idea how i would write this in VBA? Would i still add it as code on the OnExit property from [Frequency of monitoring] field?

Thanks

[Denburt]

Just a suggestion but I would add 1 more box probably combo set it to Value list and that box could be used to choose month, day, or year. Then on the properties sheet where it has the "On Exit from field" set this to [Event Procedure] Click the little icon to the right and it takes you right into the VBA area.

In this area you will want to set up some error trapping etc. (Search Google "VBA Error Trapping").

Then in the center add this:

Select Case Me!MyIncrement
  Case "Days"
    Me![Assessment Review Date] = DateAdd("m", 1, Me![Date Field]), 
  Case "Months"
 
 case "Years"
 
End select
 

Get the idea?

[Rabbit]

Except days would be "d", Months "m", and Year "yyyy"
But basically a select case would be the best solution, you just have to fill in the blanks.

[Denburt]

LOL yeah oops thanks for that clarification Rabbit. Fingers going faster than my brain. Guess I should preview my posts.

[CrostonScottish]

Thanks for this. I have followed your code and it works great. I have sweated over this for a while.

Just to add the icing on the cake can i add anything to the code so that if i change the [Date field] to an earlier or later date rather than default todays date on the form but leave [Assessment Review Period] as it was e.g. at "3 Month" it will update the [Assessment Review Date] field.

At the moment if i go back and change the [Date field] i have to reselect the [Assessment Review Period] to calculate my new [Assessment Review Date]

Any ideas?

Cheers

[Denburt]

First I would take the select statement and place it in its own sub routine then call the routine from both of the controls on update events.

In the same forms module it would look something like this.

Private Sub  Date_field_AfterUpdate()
MyInc
End sub
 
 
Private Sub Assessment_Review_Period_AfterUpdate()
MyInc
End sub
 
 
Private Sub MyInc() 
Select Case Me!MyIncrement
  Case "Days"
    Me![Assessment Review Date] = DateAdd("d", 1, Me![Date Field]), 
  Case "Months"
 
 case "Years"
End select
End Sub
 

[CrostonScottish]

Cheers. Got it working like a dream. I got the "d" days and "m" month bit. At least your fingers can go quicker than the brain.

[Denburt]

Glad I could help and as far as the brain thing I feel as though I have completely lost that sucker at times. ;)

[Rabbit]

LOL yeah oops thanks for that clarification Rabbit. Fingers going faster than my brain. Guess I should preview my posts.

Not a problem, I do it too. The only difference between me and you is that I get to go back and edit my errors and make myself look better.

[Denburt]

Not a problem, I do it too. The only difference between me and you is that I get to go back and edit my errors and make myself look better.

LOL so not fair... :)