Help! IF THENs in Forms

[AlfredNor]

Heres my problem...

I have a number field that I need to have a value based on the comparison of two other variables. Keep in mind each of these variables is a FIELD on the form.

Eg. If A > B then C (this field)= D+1

Ive tried editing the Control Source (making an expression) and putting in an IF THEN but I guess im not writing it correctly. If also tried to use an IF(logic_test,value_true,value_false) but that gives some #name error message.

Basically Im comparing dates (but since im not good with dates, per se..im using numbers which the people using the program will recognize as corresponding to months)

Can anyone help me? IM REALLY in a quandry. It'd be MUCH appreciated.



(The long explanation is:

IF [Referral Date] > [Bill Date] THEN [Application Month]=[Referral Month] +1 but IF >= [Bill Date] [Application Month]=[Referral Month]

(where all these [x] fields are just numbers. (pant) (pant) (pant)

 

[skwilliams]

Try putting this equation in VBA code within the form's module and trigger it with an on_exit event.

 

[AlfredNor]

ok...how

Ok im new to Access....So im setting up an event...based on a the keystroke of the other field?

HMMMMm not sure how to do what you're explaining...could you get a little more specific?

 

[skwilliams]

Sure.

Say I want to perform this equation once I've tabbed through a field or clicked on a button.

For this example, I'll click a button.

Go to Design View. Create your button and name it cmdcalcbutton. Right-click the button and go to properties. Scroll down until you see an event named on click. Single-click in the blank space next to it and a drop down arrow appears. Click the dropdown arrow and choose "Event Procedure". Now click the Ellipsis (...) next to it. This brings up the VB window.
Your cursor should be positioned within a procedure like this:

Private Sub cmdCalcButton_Click()

End Sub

In between these two lines type:
If A > B then C =D+1

Go to the Debug Menu on top and click Compile. If there's a runtime error with this procedure it should tell you here.

Close the VB window and return to form view.

Now when you click the button, it should perform your calculation.

Let me know if you need anything else.

HTH

Steve

 

[AlfredNor]

getting closer

Ok...getting closer. I appreciate you telling me where to click...that helps.

My next question is...how does this new button know which field I want the generated number to be put into?

Also if will it matter if those variables (A,B,C..etc in my explanation) are Fields/Field values?

You are really helping me btw.

 

[AlfredNor]

Ahhh

Ahh...i think I know how it will know...
If the logic im using is:
IF(logic_test,value_true,value_false)

Then I set the value for the specific variable using the value_true/value_false parts.

But the question then becomes does it place the actual number into the field on the form? (Im betting it does)

Let me know if im right.

 

[AlfredNor]

(( Is my syntax right?

Ok...not sure if im putting my argument in the right place....OR...what the CORRECT logic test is.

I tried this: (and it had errors in compiling)
---------------------------------------------------
Option Compare Database

Private Sub Application_Applied_Month_1_AfterUpdate()
IF
End Sub

Private Sub Application_Applied_Month_1_Change()

End Sub

Private Sub Command83_Click()
IF([Referral_Received_Day_1]>[Bill_DAY])THEN [Application_Applied_Month_1]=[Referral_Received_Month_1]+1,[Application_Applied_Month_1]=[Referral_Received_Month_1])

End Sub
----------------------------------------------------

Should I have put my logic after the first IF?

Thanks

 

[Rich]

As the control source for field C
=Iif( [Referral Date] > [Bill Date],[Referral Month] +1,[Referral Month])

 

[AlfredNor]

thank you very much

I got it to work thanks to you....DEEPLY APPRECIATED