IF statements

[rwilliams]

I'm having trouble forming a multiple condition IF statement.

Basically, I have 2 radio buttons with values of "option 1" = 1 and "option 2" = 2 which values are stored in field [CPR_option]... then I have 2 date/time text boxes "CPR_date" and "CPR_date_expires".

I am trying to write a statement to place in "CPR_date_expires" that says: if CPR_option = 1 then add 2 years to "CPR_date", else if CPR_option = 2, add 1 year to "CPR_date", and if nothing is selected (value 0) then don't calculate anything. I'm not sure if you have to specify the don't calculate (null) function.

This is what I have so far... but it's not working - pretty sure my syntax is wrong:

=IIf([CPR_option]="1",DateAdd("m",24,[CPR_Date_Issue]), ([CPR_option]="2",DateAdd("m",12,[CPR_Date_Issue]))

Help?

 

[rwilliams]

Ah, well I always give up right before I find my answer...

Wasn't getting much on google until i found this.

My resulting statement was:

=IIf([CPR_option]="1", DateAdd("m",24,[CPR_Date_Issue]),Iif([CPR_option]="2",DateAdd("m",12,[CPR_Date_Issue]),Null))

And it did what I wanted.

 

[rwilliams]

values calculated on form not being stored in table

I'd placed this db to the side for a while... now I'm back to it, and I'm noticing a problem.

I have various "date" text boxes and related "expire date" text boxes.

I am wanting to place a "default value", essentially, for expire date fields. I have placed the correct code, that I figured out above, in the control sources for text boxes because Access wouldn't allow me to write an expression in the default value option under table design.

What I've noticed, is that by placing the expression in the control source on the form, the calculated values are not being stored in the table. They show up for the correct records on the form... but they do not show up in the table.

I need those values stored so I can run my queries and reports. How can I still have the "default value"-like calculation and have the form store those values in my table?

 

[rwilliams]

*bump*

still needing help with getting the calculated form values stored in my table.

 

[motleyjew]

set your text box to whatever a field in your table so it stores. Then put your code in the On Current Event Procedure of the form, or maybe even the after updates of your fields that you want to trigger the rule. I'm new to VBA but I think thats what you can do for it to work.

 

[rwilliams]

I appreciate the reply!

I don't know any VBA either... so i'm not sure how to write that based on the big IF statement I have in post #2.

Here's some further information that might help in writing the statement.

The form is "frmVacinations"
My form section looks like this:
~~~~~~~~~~~~~~~~~~~~
__________CPR__________
o AHA option button - value 1
o ARC option button - value 2

Date Issued
|----text box----| named CPR_Date_Issue

Expiration Date
|-----text box-----| named CPR_Expiration_date
~~~~~~~~~~~~~~~~~~~~

the table I'm using is called "tblVacinations"
the fields are:
[CPR_option] stores value from option buttons
[CPR_Date_Issue] stores date entered into date issue text box
[CPR_Expiration_Date] needs to store date from Expiration Date text box

 

[motleyjew]

I'll give it a go, but i usually its easier for me to use the expression builder to get the right syntex.
I think you actually have the code finished if I'm not mistaken. You just need to know where to put it in VBA. First you can put it in the On Current event.
Go to the properties of the form and scroll down to "On Current". Hit the drop down and have it display "Event Procedures". Once that is done, click the 3 dot button next to the event. You are now in VBA. You will see something similar to this:

Private Sub Form_Current()

End Sub

Paste your code in between so it looks like this

Private Sub Form_Current()
=IIf([CPR_option]="1", DateAdd("m",24,[CPR_Date_Issue]),Iif([CPR_option]="2",DateAdd("m",12,[CPR_Date_Issue]),Null))

End Sub

Then go to the top of the scren and click debug and choose compile. Go back to debug and click step into.
You can then close VBA and try your code out. You may want to put you code in the after update of your option group so it triggers after you make your selection. Hope this works.

 

[neileg]

If you can calculate the value you shouldn't need to store it anyway. Do the calculations in a query and use the query as the record source for other queris or reports.

 

[rwilliams]

Thanks for your efforts guys. Queries didn't work - and either way I had to get these values stored. Motleyjew was close with the VB stuff....

I finally found what I needed:
Linky.

It says to create a macro of SetValues and call the macro for OnCurrent in the form, and also AfterUpdate for each field. It's so wonderful! hehe