By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

DateAdd and IIf

P: 36
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
Mar 19 '07 #1
Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,347
Try:
Expand|Select|Wrap|Line Numbers
  1. iif([Frequency of Monitoring] = "1 Month", DateAdd("m", 1, [Date Field]), DateAdd("m", 3, [Date Field]))
Mar 19 '07 #2

P: 36
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?
Mar 20 '07 #3

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 20 '07 #4

P: 36
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.
Mar 20 '07 #5

Rabbit
Expert Mod 10K+
P: 12,347
A nested iif statement is a quick and dirty fix, I would go with the VBA code, takes more time but is more robust.
Mar 20 '07 #6

P: 36
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
Mar 20 '07 #7

Denburt
Expert 100+
P: 1,356
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:

Expand|Select|Wrap|Line Numbers
  1. Select Case Me!MyIncrement
  2.   Case "Days"
  3.     Me![Assessment Review Date] = DateAdd("m", 1, Me![Date Field]), 
  4.   Case "Months"
  5.  
  6.  case "Years"
  7.  
  8. End select
  9.  
Get the idea?
Mar 20 '07 #8

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 20 '07 #9

Denburt
Expert 100+
P: 1,356
LOL yeah oops thanks for that clarification Rabbit. Fingers going faster than my brain. Guess I should preview my posts.
Mar 20 '07 #10

P: 36
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
Mar 20 '07 #11

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub  Date_field_AfterUpdate()
  2. MyInc
  3. End sub
  4.  
  5.  
  6. Private Sub Assessment_Review_Period_AfterUpdate()
  7. MyInc
  8. End sub
  9.  
  10.  
  11. Private Sub MyInc() 
  12. Select Case Me!MyIncrement
  13.   Case "Days"
  14.     Me![Assessment Review Date] = DateAdd("d", 1, Me![Date Field]), 
  15.   Case "Months"
  16.  
  17.  case "Years"
  18. End select
  19. End Sub
  20.  
Mar 20 '07 #12

P: 36
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.
Mar 20 '07 #13

Denburt
Expert 100+
P: 1,356
Glad I could help and as far as the brain thing I feel as though I have completely lost that sucker at times. ;)
Mar 20 '07 #14

Rabbit
Expert Mod 10K+
P: 12,347
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.
Mar 21 '07 #15

Denburt
Expert 100+
P: 1,356
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... :)
Mar 21 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.