If/Then? Calculations

[quilter]

I have a text box with a calculation. When the result is >0 - I want to use it in another calculation. If not then ignore it. How can I do this?

Would this be a IF/Then expression and how do you use it and where?

 

[Dennisk]

I assume the text box is a calculated control.
if another calculated control is required based in the previous one then your only option is to use in in-line IF Function IIF() if you do not want to use VBA (which in this case may not be the preferred option)

 

[quilter]

calc

IIF(Moisture Exceedance>0)


I have no clue here what I am doing. Beginners! If I want to display only the values that are >0 what would be the expression? Then would I be able to use that in my nest calculation for a penalty fee on the number displayed?

 

[ramblinwreck]

Is this what you're asking:

if me.txtYourTextbox>0 then
'calculations here with your textbox when it's greater than 0
else
'other calculations here when it's <= 0
end if

 

[Dennisk]

ramblinwreck

where does the code go? before/after update event.


a text box (txtb02) that relies on the value from the previous text box (txtb01)

set up the control source

=IIF(me!txtb01> SomeAmount,8,0)

what this does if the me!txtb01 is greater than SomeAmount then set txtb02 to 8 else set it to 0

 

[quilter]

Not the correct number of arguments.

IIf(me![Moisture Exceed] >0)then([Quantity] * [Moisture Exceed] /100 * [Unit Price])

or

=IIf(me![Moisture Exceed] >0)then/100 * {Quantity]*[Unit Price])

I have placed these both in the control source of the text box. Is it not suppose to go there? And If I use it in the expression builder I get the wrong number of arguments.


This is what I am trying to do.
Moisture Exceed is the text box of my first calculation. If it is greater than "0" then divide by 100 and mulitply times the Quantity and Unit Price.

 

[fibayne]

IIf ..

Hi

Here is what I use for what looks like a similar calculation, it goes in the query that the form/report is based on, I have attached a screen print to show where it goes

CPMCalc:
IIf([CPM]='CPM' And [%Delivered]<90,[DeliveredImps]/1000*[OnlineCP],
IIf([CPM]='CPM' And [%Delivered]>90,[Quantity]/1000*[OnlineCP],
IIf([CPM]='CPV' And [%Delivered]<90,[DeliveredImps]*[OnlineCP],
IIf([CPM]='CPV' And [%Delivered]>90,[Quantity]*[OnlineCP],
IIf([CPM]='CPU' And [%Delivered]<90,[DeliveredImps]*[OnlineCP],
IIf([CPM]='CPU' And [%Delivered]>90,[Quantity]*[OnlineCP]))))))

hope this helps I spent ages trying to work this out and was helped by someone in this forum to come up with my answer

 

[Dennisk]

I sugest you look up IIf in the help to get the correct syntax.

 

[fibayne]

Hi Dennisk

was your response for me ??

 

[gemma-the-husky]

quilter

you ought to put your code in the afterupdate event of the control

properties/events/afterupdate - on the right hand side select event/code from the drop down, ,then click the ellipssis ... to go to the code window

-------------
you are now entering code that will happen AFTER you change the value and press tab or enter

so here you can put

if (whatevercondition) then
-------- do something
-------- eg - make some label visible.invisible etc
else
-------- do something else
end if


---------------
one thing to bear in mind is that this only first AFTER you change the field. Therefore you may ALSO need to put something in the forms current event
(properties/events/on current) to handle the initial situation.


you can also use the beforeupdate event to validate the input and reject it in certain cases.

pretty well everything in access is based on responding to various events that happen within the app - every form, text box, label, etc all carries its own events that can be checked and managed as appropriate.

 

[fibayne]

Hi quilter

Just wondered if you had found the answer from the various responses posted if you haven't I would be happy to have a look at your db to see if I can come up with the code for you ...learning curve for me also so wouldn't mind giving it a go

Fi