if then else statement

A

Alberta Rose

On my report I have a field that I need help with. I have written an =If
statement to try and capture all of the variables, but it is too long, so now
I need to write the same expression in code. Here is what I have in my =If
statement (which resides in the Detail area of the Report):

=IIf([costcode]="013210" And
[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110"
And
[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201"
And
[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301"
And
[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101"
And
[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101"
And
[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211"
And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311"
And
[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))

So, what it says is check this cost code and cost type and then perform the
calculation of txtActualCost divided by the txt box I've named that
corresponds to the hours for this cost code/cost type that resides in the
Report Header area of the report.

I have tried:

If cost code = 013210 and
costtype = 05320
Then txtActualCost/txtCraftLabHrs
Else
End If

I hope someone can help me with this. The txtActualCost is set as a
currency field, the txtCraftLabHrs is set as standard.
Thanks.
 
P

Paolo

Hi Alberta Rose,
you can use the if then else in this way

If costcode = "013210" and costtype = "05320" Then
yourfield=txtActualCost/txtCraftLabHrs
Elseif costcode="020110" and costtype = "05320" Then
yourfield=txtActualCost/txtFirewatchLabAct
elseif etc.etc.

End If

or you can use a select statement

tmp_var= costcode & costtype
select case tmp_var
case "01321005320"
yourfield=txtActualCost/txtCraftLabHrs
case "02011005320"
yourfield=txtActualCost/txtFirewatchLabAct
case etc. etc.

case else
do something if none of the above. You can also avoid the case else
if you are sure that a condions is always met
end select

HTH Paolo
 
J

John W. Vinson

On my report I have a field that I need help with. I have written an =If
statement to try and capture all of the variables, but it is too long, so now
I need to write the same expression in code. Here is what I have in my =If
statement (which resides in the Detail area of the Report):

=IIf([costcode]="013210" And
[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110"
And
[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201"
And
[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301"
And
[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101"
And
[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101"
And
[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211"
And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311"
And
[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))

So, what it says is check this cost code and cost type and then perform the
calculation of txtActualCost divided by the txt box I've named that
corresponds to the hours for this cost code/cost type that resides in the
Report Header area of the report.
Click to expand...

I've got some real concerns about your table structure!!! If you have multiple
textboxes for the various LabAct fields, I'm guessing that you have multiple
table fields as well: essentially embedding a one to many relationship in a
single record. What if you decide you need another type of LabAct? Change the
structure of your table, all your queries, all your forms, redo all your
calculations??? Ouch!

That said, try using the Switch() function instead. It's often useful in place
of multilevel IIF. It takes arguments in pairs, and evaluates them left to
right; when it first encounters a pair for which the first of the pair is TRUE
it returns the second of that pair and quits. So

=Switch([costcode]="013210" And [costtype]="05320",
[txtActualCost]/[txtCraftLabAct],
[costcode]="020110" And [costtype]="05320",
[txtActualCost]/[txtFirewatchLabAct],
[costcode]="064201" And [costtype]="05320",
[txtActualCost]/[txtCraftSuprLabAct],
[costcode]="061301" And [costtype]="05320",
[txtActualCost]/[txtQAQCSuprvLabAct],
[costcode]="061101" And [costtype]="05310",
[txtActualCost]/[txtSiteTeamLabAct],
[costcode]="031101" And [costtype]="05110",
[txtActualCost]/[txtOfficeTeamLabAct],
[costcode]="042211" And [costtype]="05110",
[txtActualCost]/[txtEngLabAct],
[costcode]="045311" And [costtype]="05130",
[txtActualCost]/[txtThirdPartyLabAct],
[costcode]="032101" And [costtype]="05110",
[txtActualCost]/[txtProcurementLabAct],
True, Null)

This would be much simpler if you had a table relating Costtype to LabActs -
you could simply join it or use DLookUp.
 
A

Alberta Rose

Thanks Paolo. I tried the first example, and no luck, no error messages, but
nothing populated in the field. It has to be the way this report was set up
and I'm at a loss of what to do next. Could it be because the LabHrs are
actually in the Header area of the report and the calculation is in the
Detail area?

Tks..Laurie


Paolo said:
Hi Alberta Rose,
you can use the if then else in this way

If costcode = "013210" and costtype = "05320" Then
yourfield=txtActualCost/txtCraftLabHrs
Elseif costcode="020110" and costtype = "05320" Then
yourfield=txtActualCost/txtFirewatchLabAct
elseif etc.etc.

End If

or you can use a select statement

tmp_var= costcode & costtype
select case tmp_var
case "01321005320"
yourfield=txtActualCost/txtCraftLabHrs
case "02011005320"
yourfield=txtActualCost/txtFirewatchLabAct
case etc. etc.

case else
do something if none of the above. You can also avoid the case else
if you are sure that a condions is always met
end select

HTH Paolo

Alberta Rose said:
On my report I have a field that I need help with. I have written an =If
statement to try and capture all of the variables, but it is too long, so now
I need to write the same expression in code. Here is what I have in my =If
statement (which resides in the Detail area of the Report):

=IIf([costcode]="013210" And
[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110"
And
[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201"
And
[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301"
And
[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101"
And
[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101"
And
[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211"
And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311"
And
[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))

So, what it says is check this cost code and cost type and then perform the
calculation of txtActualCost divided by the txt box I've named that
corresponds to the hours for this cost code/cost type that resides in the
Report Header area of the report.

I have tried:

If cost code = 013210 and
costtype = 05320
Then txtActualCost/txtCraftLabHrs
Else
End If

I hope someone can help me with this. The txtActualCost is set as a
currency field, the txtCraftLabHrs is set as standard.
Thanks.
Click to expand...
Click to expand...
 
A

Alberta Rose

Hi John. Yes I agree, this database setup is not optimal for sure. It's one
that I've inherited and have to make work. The multiple text boxes are only
on this report. The table actually is pretty clean as far as setup goes. I
have a table with the cost codes/cost types in it. The LabAct refers to the
actual labour costs which are in each record of the main table under the
field of ActualCost. If I use the =Switch expression, won't I run into the
same issue of not having enough space in the txt box to insert all the code I
need (it was about 1/2 of it that I put here as an example)? I have tried
the If Then Else coding, and nothing...

Any suggestions? Thanks Laurie...

John W. Vinson said:
On my report I have a field that I need help with. I have written an =If
statement to try and capture all of the variables, but it is too long, so now
I need to write the same expression in code. Here is what I have in my =If
statement (which resides in the Detail area of the Report):

=IIf([costcode]="013210" And
[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110"
And
[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201"
And
[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301"
And
[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101"
And
[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101"
And
[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211"
And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311"
And
[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))

So, what it says is check this cost code and cost type and then perform the
calculation of txtActualCost divided by the txt box I've named that
corresponds to the hours for this cost code/cost type that resides in the
Report Header area of the report.
Click to expand...

I've got some real concerns about your table structure!!! If you have multiple
textboxes for the various LabAct fields, I'm guessing that you have multiple
table fields as well: essentially embedding a one to many relationship in a
single record. What if you decide you need another type of LabAct? Change the
structure of your table, all your queries, all your forms, redo all your
calculations??? Ouch!

That said, try using the Switch() function instead. It's often useful in place
of multilevel IIF. It takes arguments in pairs, and evaluates them left to
right; when it first encounters a pair for which the first of the pair is TRUE
it returns the second of that pair and quits. So

=Switch([costcode]="013210" And [costtype]="05320",
[txtActualCost]/[txtCraftLabAct],
[costcode]="020110" And [costtype]="05320",
[txtActualCost]/[txtFirewatchLabAct],
[costcode]="064201" And [costtype]="05320",
[txtActualCost]/[txtCraftSuprLabAct],
[costcode]="061301" And [costtype]="05320",
[txtActualCost]/[txtQAQCSuprvLabAct],
[costcode]="061101" And [costtype]="05310",
[txtActualCost]/[txtSiteTeamLabAct],
[costcode]="031101" And [costtype]="05110",
[txtActualCost]/[txtOfficeTeamLabAct],
[costcode]="042211" And [costtype]="05110",
[txtActualCost]/[txtEngLabAct],
[costcode]="045311" And [costtype]="05130",
[txtActualCost]/[txtThirdPartyLabAct],
[costcode]="032101" And [costtype]="05110",
[txtActualCost]/[txtProcurementLabAct],
True, Null)

This would be much simpler if you had a table relating Costtype to LabActs -
you could simply join it or use DLookUp.
Click to expand...
 
J

John W. Vinson

Hi John. Yes I agree, this database setup is not optimal for sure. It's one
that I've inherited and have to make work. The multiple text boxes are only
on this report. The table actually is pretty clean as far as setup goes. I
have a table with the cost codes/cost types in it. The LabAct refers to the
actual labour costs which are in each record of the main table under the
field of ActualCost. If I use the =Switch expression, won't I run into the
same issue of not having enough space in the txt box to insert all the code I
need (it was about 1/2 of it that I put here as an example)? I have tried
the If Then Else coding, and nothing...
Click to expand...

The limit is pretty high; I've used some Switch functions much larger than
your posted example. There's a limit of 65536 characters in a textbox
(expressions can't be that big and I can't quickly find the limit but it's
well over 1000 characters). However, if your underlying data is more
normalized than your example suggests, I would hope that you could have a
table-driven solution, looking up the labor cost directly (say by using
DLookUp), using the costcode and costtype as criteria.

The alternative would be to work through Paolo's VBA code, which can certainly
be made to work.
 
P

Paolo

Hi Laurie,
the code I posted must be pasted in the on format event of your report, in
the section where the data changes. So if you have a single report for your
costcode etc. put it in the on format of the header section. If in the
details you have a list of different costcode with different calculations you
have to add it to the on format of the details.

Cheers Paolo

Alberta Rose said:
Thanks Paolo. I tried the first example, and no luck, no error messages, but
nothing populated in the field. It has to be the way this report was set up
and I'm at a loss of what to do next. Could it be because the LabHrs are
actually in the Header area of the report and the calculation is in the
Detail area?

Tks..Laurie


Paolo said:
Hi Alberta Rose,
you can use the if then else in this way

If costcode = "013210" and costtype = "05320" Then
yourfield=txtActualCost/txtCraftLabHrs
Elseif costcode="020110" and costtype = "05320" Then
yourfield=txtActualCost/txtFirewatchLabAct
elseif etc.etc.

End If

or you can use a select statement

tmp_var= costcode & costtype
select case tmp_var
case "01321005320"
yourfield=txtActualCost/txtCraftLabHrs
case "02011005320"
yourfield=txtActualCost/txtFirewatchLabAct
case etc. etc.

case else
do something if none of the above. You can also avoid the case else
if you are sure that a condions is always met
end select

HTH Paolo

Alberta Rose said:
On my report I have a field that I need help with. I have written an =If
statement to try and capture all of the variables, but it is too long, so now
I need to write the same expression in code. Here is what I have in my =If
statement (which resides in the Detail area of the Report):

=IIf([costcode]="013210" And
[costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110"
And
[costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201"
And
[costtype]="05320",[txtActualCost]/[txtCraftSuprLabAct],IIf([costcode]="061301"
And
[costtype]="05320",[txtActualCost]/[txtQAQCSuprvLabAct],IIf([costcode]="061101"
And
[costtype]="05310",[txtActualCost]/[txtSiteTeamLabAct],IIf([costcode]="031101"
And
[costtype]="05110",[txtActualCost]/[txtOfficeTeamLabAct],IIf([costcode]="042211"
And [costtype]="05110",[txtActualCost]/[txtEngLabAct],IIf([costcode]="045311"
And
[costtype]="05130",[txtActualCost]/[txtThirdPartyLabAct],IIf([costcode]="032101" And [costtype]="05110",[txtActualCost]/[txtProcurementLabAct],Null)))))))))

So, what it says is check this cost code and cost type and then perform the
calculation of txtActualCost divided by the txt box I've named that
corresponds to the hours for this cost code/cost type that resides in the
Report Header area of the report.

I have tried:

If cost code = 013210 and
costtype = 05320
Then txtActualCost/txtCraftLabHrs
Else
End If

I hope someone can help me with this. The txtActualCost is set as a
currency field, the txtCraftLabHrs is set as standard.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can you help me make this string shorter? 2
If Then Else? 3
Query Question regarding IIf's 5
"If" Statement help needed 6

Top