Question Convert formula to vbcode..

[shieriel]

I am creating formulas for my form, unfortunately I amnot that good in access programming especially vb codes.
How can I translate this formula into vb code:

IIf([QTYperHr]>0,(IIf([SetupTime],[SetupTime]+([QTYToRun]/[QTYperHr]),0)),[SetupTime])

I used this formula under the expression builder, but since i will have to use it again on my subform with vb code, i know it's a different format. Any help will be very much appreciated...

 

[CBrighton]

What is SetupTime? The embedded Iif statement just says "Iif([SetupTime],", which means it is just checking if it is true / 1. Is that correct?

This is a direct translation, but I don't like that SetupTime If statement with no criteria. It assumes the field you are populating is called "FieldName":

If QTYPerHr > 0 then
     If SetupTime then
          FieldName = SetupTime + (QTYToRun / QTYPerHour)
     Else
          FieldName = 0
Else
     FieldName = SetupTime
End if

 

[Galaxiom]

What is SetupTime? The embedded Iif statement just says "Iif([SetupTime],", which means it is just checking if it is true / 1. Is that correct?

....... I don't like that SetupTime If statement with no criteria.

Without a comparision the Immediate If will return True for any text value or non-zero value in a numeric field and False for a Null.

Another example of this usage is the DLookUp which can determine the existence of a record. This very tidy expression can be tested like a Boolean since it returns a Null for no results and hence False when tested with IF:

DLookUp("expression", "domain")

 

[CBrighton]

Sorry, yes. You're right.

It checks for anything except null / false (0) rather than checking for true (1).

 

[DCrake]

Create this function in a standard module

IIf([QTYperHr]>0,(IIf([SetupTime],[SetupTime]+([QTYToRun]/[QTYperHr]),0)),[SetupTime])

Publuc Function GetSetUpTime(ByVal QtyPH,ByVal QtyTR, ByVal SetUpTime) As Variant

If QtyPH > 0 Then
   
     If SetupTime then
          GetSetUpTime = SetupTime + (QtyTR / QtyPH)
     Else
          GetSetUpTime = 0
     End If
Else
     GetSetUpTime = SetupTime
End if

End Function

Then whenever you need to use this function you just pass the function the three required elements.

Quantity per hour
Quantity to Run
setup time

Such As = Query

ActSetupTime : GetSetUpTime([QuantityPH],[QuantityTR],[SetupTime])

Such as = Form

Me.TxtSetUpTime = GetSetUpTime(Me.TxtQtyPH,Me.QtyTR,Me.TxtSetup)

Don't forget I have been using made up names of controls for brevity only, use the real names that you have.

 

[shieriel]

Thank you very much for all your help. I will try to use the function on my project now..

 

[shieriel]

Create this function in a standard module

IIf([QTYperHr]>0,(IIf([SetupTime],[SetupTime]+([QTYToRun]/[QTYperHr]),0)),[SetupTime])

Publuc Function GetSetUpTime(ByVal QtyPH,ByVal QtyTR, ByVal SetUpTime) As Variant

If QtyPH > 0 Then
   
     If SetupTime then
          GetSetUpTime = SetupTime + (QtyTR / QtyPH)
     Else
          GetSetUpTime = 0
     End If
Else
     GetSetUpTime = SetupTime
End if

End Function

Then whenever you need to use this function you just pass the function the three required elements.

Quantity per hour
Quantity to Run
setup time

Such As = Query

ActSetupTime : GetSetUpTime([QuantityPH],[QuantityTR],[SetupTime])

Such as = Form

Me.TxtSetUpTime = GetSetUpTime(Me.TxtQtyPH,Me.QtyTR,Me.TxtSetup)

Don't forget I have been using made up names of controls for brevity only, use the real names that you have.

Sir, I tried creating the module and do as what you said but when i applied on my query i got the following error: "Undefined function "GetLoadingTime" in expression."
Here is my query>>>
LoadingTime: GetLoadingTime([QTYPerHr],[QTYToRun],[SetupTime])

What i do in my function is this (named it "GetLoadingTime"):

Public Function GetLoadingTime(ByVal QTYperHr, ByVal QTYToRun, ByVal SetupTime) As Variant
If QTYperHr > 0 Then
GetLoadingTime = SetupTime + (QTYToRun / QTYperHr)
Else
GetLoadingTime = SetupTime + QTYToRun
End If
End Function

What is my fault here

 

[pbaldy]

David is probably asleep. Make sure you put that in a standard module, not behind a form, and that the module does not have the same name as the function (a common mistake).