johnherrerajuan
Registered User.
- Local time
- Today, 07:06
- Joined
- Feb 25, 2013
- Messages
- 44
I need help with these formulas, i tried using this one but it told me it was to long
=IIf([Combo5]=“Building Service Engineer”,[Forms]![Estimate]![Text787],IIf([Combo5]=“Carpenter”,[Forms]![Estimate]![Text788],IIf([Combo5]=“Custodian”,[Forms]![Estimate]![Text789],IIf([Combo5]=“Custodian - Shift Pay (5am - 6am)”,[Forms]![Estimate]![Text790],IIf([Combo5]=“Electrician”,[Forms]![Estimate]![Text791],IIf([Combo5]=“Facilities Project Supervisor”,[Forms]![Estimate]![Text792],IIf([Combo5]=“Fire Marshal”,[Forms]![Estimate]![Text793],IIf([Combo5]=“Gardening Specialist”,[Forms]![Estimate]![Text794],IIf([Combo5]=“Grounds Worker”,[Forms]![Estimate]![Text795],IIf([Combo5]=“Interior Design”,[Forms]![Estimate]![Text796],IIf([Combo5]=“Irrigation Specialist”,[Forms]![Estimate]![Text797],IIf([Combo5]=“Laborer”,[Forms]![Estimate]![Text798],IIf([Combo5]=“Lead Auto/Equip Mechanic”,[Forms]![Estimate]![Text799],IIf([Combo5]=“Lead Custodian”,[Forms]![Estimate]![Text800],IIf([Combo5]=“Lead Grounds Worker”,[Forms]![Estimate]![Text801],IIf([Combo5]=“Light Auto/Equip Operator”,[Forms]![Estimate]![Text802],IIf([Combo5]=“Locksmith”,[Forms]![Estimate]![Text803],IIf([Combo5]=“Maintenance Mechanic”,[Forms]![Estimate]![Text804],IIf([Combo5]=“Painter”,[Forms]![Estimate]![Text805],IIf([Combo5]=“Pest Control Specialist”,[Forms]![Estimate]![Text806],IIf([Combo5]=“Plumber”,[Forms]![Estimate]![Text807],IIf([Combo5]=“Recycler (Laborer)”,[Forms]![Estimate]![Text808],IIf([Combo5]=“Refrigeration Mechanic”,[Forms]![Estimate]![Text809],IIf([Combo5]=“Supervising Building Service Engineer”,[Forms]![Estimate]![Text810],0)))))))))))))))))))))))))
So I approced it as a SWITCH functnion
=Switch([Combo5]=“Building Service Engineer”,[Forms]![Estimate]![Text787],[Combo5]=“Carpenter”,[Forms]![Estimate]![Text788],[Combo5]=“Custodian”,[Forms]![Estimate]![Text789],[Combo5]=“Custodian - Shift Pay (5am - 6am)”,[Forms]![Estimate]![Text790],[Combo5]=“Electrician”,[Forms]![Estimate]![Text791],[Combo5]=“Facilities Project Supervisor”,[Forms]![Estimate]![Text792],[Combo5]=“Fire Marshal”,[Forms]![Estimate]![Text793],[Combo5]=“Gardening Specialist”,[Forms]![Estimate]![Text794],[Combo5]=“Grounds Worker”,[Forms]![Estimate]![Text795],[Combo5]=“Interior Design”,[Forms]![Estimate]![Text796],[Combo5]=“Irrigation Specialist”,[Forms]![Estimate]![Text797],[Combo5]=“Laborer”,[Forms]![Estimate]![Text798],[Combo5]=“Lead Auto/Equip Mechanic”,[Forms]![Estimate]![Text799],[Combo5]=“Lead Custodian”,[Forms]![Estimate]![Text800],[Combo5]=“Lead Grounds Worker”,[Forms]![Estimate]![Text801],[Combo5]=“Light Auto/Equip Operator”,[Forms]![Estimate]![Text802],[Combo5]=“Locksmith”,[Forms]![Estimate]![Text803],[Combo5]=“Maintenance Mechanic”,[Forms]![Estimate]![Text804],[Combo5]=“Painter”,[Forms]![Estimate]![Text805],[Combo5]=“Pest Control Specialist”,[Forms]![Estimate]![Text806],[Combo5]=“Plumber”,[Forms]![Estimate]![Text807],[Combo5]=“Recycler (Laborer)”,[Forms]![Estimate]![Text808],[Combo5]=“Refrigeration Mechanic”,[Forms]![Estimate]![Text809],[Combo5]=“Supervising Building Service Engineer”,[Forms]![Estimate]![Text810])
That didnt work so I used it as a module
The thing is i do not know how to trigger this
---------------------------------------------------
I have a form where the data is entered
and im trying to create a new form with a few combo boxes that once selected it will show the amount associated in an unbound text box (calculated field from the first form)
=IIf([Combo5]=“Building Service Engineer”,[Forms]![Estimate]![Text787],IIf([Combo5]=“Carpenter”,[Forms]![Estimate]![Text788],IIf([Combo5]=“Custodian”,[Forms]![Estimate]![Text789],IIf([Combo5]=“Custodian - Shift Pay (5am - 6am)”,[Forms]![Estimate]![Text790],IIf([Combo5]=“Electrician”,[Forms]![Estimate]![Text791],IIf([Combo5]=“Facilities Project Supervisor”,[Forms]![Estimate]![Text792],IIf([Combo5]=“Fire Marshal”,[Forms]![Estimate]![Text793],IIf([Combo5]=“Gardening Specialist”,[Forms]![Estimate]![Text794],IIf([Combo5]=“Grounds Worker”,[Forms]![Estimate]![Text795],IIf([Combo5]=“Interior Design”,[Forms]![Estimate]![Text796],IIf([Combo5]=“Irrigation Specialist”,[Forms]![Estimate]![Text797],IIf([Combo5]=“Laborer”,[Forms]![Estimate]![Text798],IIf([Combo5]=“Lead Auto/Equip Mechanic”,[Forms]![Estimate]![Text799],IIf([Combo5]=“Lead Custodian”,[Forms]![Estimate]![Text800],IIf([Combo5]=“Lead Grounds Worker”,[Forms]![Estimate]![Text801],IIf([Combo5]=“Light Auto/Equip Operator”,[Forms]![Estimate]![Text802],IIf([Combo5]=“Locksmith”,[Forms]![Estimate]![Text803],IIf([Combo5]=“Maintenance Mechanic”,[Forms]![Estimate]![Text804],IIf([Combo5]=“Painter”,[Forms]![Estimate]![Text805],IIf([Combo5]=“Pest Control Specialist”,[Forms]![Estimate]![Text806],IIf([Combo5]=“Plumber”,[Forms]![Estimate]![Text807],IIf([Combo5]=“Recycler (Laborer)”,[Forms]![Estimate]![Text808],IIf([Combo5]=“Refrigeration Mechanic”,[Forms]![Estimate]![Text809],IIf([Combo5]=“Supervising Building Service Engineer”,[Forms]![Estimate]![Text810],0)))))))))))))))))))))))))
So I approced it as a SWITCH functnion
=Switch([Combo5]=“Building Service Engineer”,[Forms]![Estimate]![Text787],[Combo5]=“Carpenter”,[Forms]![Estimate]![Text788],[Combo5]=“Custodian”,[Forms]![Estimate]![Text789],[Combo5]=“Custodian - Shift Pay (5am - 6am)”,[Forms]![Estimate]![Text790],[Combo5]=“Electrician”,[Forms]![Estimate]![Text791],[Combo5]=“Facilities Project Supervisor”,[Forms]![Estimate]![Text792],[Combo5]=“Fire Marshal”,[Forms]![Estimate]![Text793],[Combo5]=“Gardening Specialist”,[Forms]![Estimate]![Text794],[Combo5]=“Grounds Worker”,[Forms]![Estimate]![Text795],[Combo5]=“Interior Design”,[Forms]![Estimate]![Text796],[Combo5]=“Irrigation Specialist”,[Forms]![Estimate]![Text797],[Combo5]=“Laborer”,[Forms]![Estimate]![Text798],[Combo5]=“Lead Auto/Equip Mechanic”,[Forms]![Estimate]![Text799],[Combo5]=“Lead Custodian”,[Forms]![Estimate]![Text800],[Combo5]=“Lead Grounds Worker”,[Forms]![Estimate]![Text801],[Combo5]=“Light Auto/Equip Operator”,[Forms]![Estimate]![Text802],[Combo5]=“Locksmith”,[Forms]![Estimate]![Text803],[Combo5]=“Maintenance Mechanic”,[Forms]![Estimate]![Text804],[Combo5]=“Painter”,[Forms]![Estimate]![Text805],[Combo5]=“Pest Control Specialist”,[Forms]![Estimate]![Text806],[Combo5]=“Plumber”,[Forms]![Estimate]![Text807],[Combo5]=“Recycler (Laborer)”,[Forms]![Estimate]![Text808],[Combo5]=“Refrigeration Mechanic”,[Forms]![Estimate]![Text809],[Combo5]=“Supervising Building Service Engineer”,[Forms]![Estimate]![Text810])
That didnt work so I used it as a module
Code:
Option Compare Database
Public Function fncRetValBasedOnCmb4() As String
Select Case Forms!ReportFinished!Combo4
Case "Building Service Engineer"
fncRetValBasedOnCmb4 = Forms!Estimate!Text787
Case "Carpenter"
fncRetValBasedOnCmb4 = Forms!Estimate!Text788
Case "Custodian"
fncRetValBasedOnCmb4 = Forms!Estimate!Text789
Case "Custodian - Shift Pay (5am - 6am)"
fncRetValBasedOnCmb4 = Forms!Estimate!Text790
Case "Electrician"
fncRetValBasedOnCmb4 = Forms!Estimate!Text791
Case "Facilities Project Supervisor"
fncRetValBasedOnCmb4 = Forms!Estimate!Text792
Case "Fire Marshal"
fncRetValBasedOnCmb4 = Forms!Estimate!Text793
Case "Gardening Specialist"
fncRetValBasedOnCmb4 = Forms!Estimate!Text794
Case "Grounds Worker"
fncRetValBasedOnCmb4 = Forms!Estimate!Text795
Case "Interior Design"
fncRetValBasedOnCmb4 = Forms!Estimate!Text796
Case "Irrigation Specialist"
fncRetValBasedOnCmb4 = Forms!Estimate!Text797
Case "Laborer"
fncRetValBasedOnCmb4 = Forms!Estimate!Text798
Case "Lead Auto/Equip Mechanic"
fncRetValBasedOnCmb4 = Forms!Estimate!Text799
Case "Lead Custodian"
fncRetValBasedOnCmb4 = Forms!Estimate!Text800
Case "Lead Grounds Worker"
fncRetValBasedOnCmb4 = Forms!Estimate!Text801
Case "Light Auto/Equip Operator"
fncRetValBasedOnCmb4 = Forms!Estimate!Text802
Case "Locksmith"
fncRetValBasedOnCmb4 = Forms!Estimate!Text803
Case "Maintenance Mechanic"
fncRetValBasedOnCmb4 = Forms!Estimate!Text804
Case "Painter"
fncRetValBasedOnCmb4 = Forms!Estimate!Text805
Case "Pest Control Specialist"
fncRetValBasedOnCmb4 = Forms!Estimate!Text806
Case "Plumber"
fncRetValBasedOnCmb4 = Forms!Estimate!Text807
Case "Recycler (Laborer)"
fncRetValBasedOnCmb4 = Forms!Estimate!Text808
Case "Refrigeration Mechanic"
fncRetValBasedOnCmb4 = Forms!Estimate!Text809
Case "Supervising Building Service Engineer"
fncRetValBasedOnCmb4 = Forms!Estimate!Text810
Case Else
fncRetValBasedOnCmb4 = 0
End Select
End Function
The thing is i do not know how to trigger this
---------------------------------------------------
I have a form where the data is entered
and im trying to create a new form with a few combo boxes that once selected it will show the amount associated in an unbound text box (calculated field from the first form)