"If" Statement help needed

  • Thread starter Thread starter Alberta Rose
  • Start date Start date
A

Alberta Rose

OK, I have to try this again.

I have a convoluted issue and I hope I can describe it clear. I have a
report
(rptCloseout) that is fed from a query (qryLink). In the design mode of the
report, the "detail" area shows one line. When you run the report, all the
information for that contract comes in and has multiple lines on the report
view.

Now I need to do some calculations to show up in the detail area of
the report, but need to have my query recognize a specific cost code/cost
type
(the first two boxes in the detail line), and if this cost code/cost type is
for example 13210/5320, then perform a calculation that includes manhours
which are located in the report header area.

If it is not this combinatin of cost code/cost type, then continue to the
next cost code/cost type and check that. I know I need to add the formula to
the qryLink, but have no idea how to do it.

I have tried

txtEstPerMhr: IIf([costcode = 013210] and [costtype = 05320],
txtEstimatedCost/txtCraftLabEst, nul)

Also, would I put this formula in the qryLink, directly into the box on the
report or ???

Thanks in advance
 
Try this expression/column in your query:

txtEstPerMhr: IIf([costcode] = "013210" and [costtype] = "05320",
txtEstimatedCost/txtCraftLabEst, null)

This assumes txtEstimatedCost and txtCraftlabEst are values in your query.

If the "txt.." are text boxes in the report then try a control source of:
=IIf([costcode] = "013210" and [costtype] = "05320",
txtEstimatedCost/txtCraftLabEst, null)

Typically a don't like any expressions where values like "013210" and
"05320" are hard coded. There should be something in your tables that
identifies the "specialness" of these values.
 
You have some typos in your code. For example you have the ] bracket in the
wrong place twice and Nul isn't spelled correctly. Also as you have leading
zeors in costcode and costtype, you probably should have quotation marks
around the values.

Put the following in the Control Source for the text box where you want to
see it displayed. Make very sure that the text box names are correct.

=IIf([costcode]="013210" And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],Null)
 
Awesome, you guys are great - works like a charm. I need to add other cost
codes and cost types to this string, how would I do that?

=IIf([costcode]="013210" And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],Null)

=IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],Null)

=IIf([costcode]="064201" and
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],Null)

and so on...
 
I posted just one more question.....



Duane Hookom said:
Try this expression/column in your query:

txtEstPerMhr: IIf([costcode] = "013210" and [costtype] = "05320",
txtEstimatedCost/txtCraftLabEst, null)

This assumes txtEstimatedCost and txtCraftlabEst are values in your query.

If the "txt.." are text boxes in the report then try a control source of:
=IIf([costcode] = "013210" and [costtype] = "05320",
txtEstimatedCost/txtCraftLabEst, null)

Typically a don't like any expressions where values like "013210" and
"05320" are hard coded. There should be something in your tables that
identifies the "specialness" of these values.


--
Duane Hookom
Microsoft Access MVP


Alberta Rose said:
OK, I have to try this again.

I have a convoluted issue and I hope I can describe it clear. I have a
report
(rptCloseout) that is fed from a query (qryLink). In the design mode of the
report, the "detail" area shows one line. When you run the report, all the
information for that contract comes in and has multiple lines on the report
view.

Now I need to do some calculations to show up in the detail area of
the report, but need to have my query recognize a specific cost code/cost
type
(the first two boxes in the detail line), and if this cost code/cost type is
for example 13210/5320, then perform a calculation that includes manhours
which are located in the report header area.

If it is not this combinatin of cost code/cost type, then continue to the
next cost code/cost type and check that. I know I need to add the formula to
the qryLink, but have no idea how to do it.

I have tried

txtEstPerMhr: IIf([costcode = 013210] and [costtype = 05320],
txtEstimatedCost/txtCraftLabEst, nul)

Also, would I put this formula in the qryLink, directly into the box on the
report or ???

Thanks in advance
Click to expand...
Click to expand...
 
and so on...

How many more? You can start nesting IIf statement, but it gets slow and
hard to maintain after about 5 or 6 of them. It would look something like
this for just the 3 examples that you provided.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

There are Select Case statements in VB code that does the job better
especially if you are using ranges such as Between 1 and 5 = "Blue".

However it's possible that the best bet is another table or two. You could
then link the data between the two tables to get the numbers quicker.

Also that you have fields like txtCraftLabEst, txtFirewatchLabEst, and
txtCraftSuprLabEst make me wonder if your database isn't properly
designed/normalized and that's the root cause of the problem with the need
for convoluted IIf statements.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Alberta Rose said:
Awesome, you guys are great - works like a charm. I need to add other cost
codes and cost types to this string, how would I do that?

=IIf([costcode]="013210" And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],Null)

=IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],Null)

=IIf([costcode]="064201" and
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],Null)

and so on...


Alberta Rose said:
OK, I have to try this again.

I have a convoluted issue and I hope I can describe it clear. I have a
report
(rptCloseout) that is fed from a query (qryLink). In the design mode of the
report, the "detail" area shows one line. When you run the report, all the
information for that contract comes in and has multiple lines on the report
view.

Now I need to do some calculations to show up in the detail area of
the report, but need to have my query recognize a specific cost code/cost
type
(the first two boxes in the detail line), and if this cost code/cost type is
for example 13210/5320, then perform a calculation that includes manhours
which are located in the report header area.

If it is not this combinatin of cost code/cost type, then continue to the
next cost code/cost type and check that. I know I need to add the formula to
the qryLink, but have no idea how to do it.

I have tried

txtEstPerMhr: IIf([costcode = 013210] and [costtype = 05320],
txtEstimatedCost/txtCraftLabEst, nul)

Also, would I put this formula in the qryLink, directly into the box on the
report or ???

Thanks in advance
Click to expand...
Click to expand...
 
I've nested the if statements and they are working wonderful! One more
little question, what do I need to add to this in the instance that there
are no records to calculate on specific contracts, and I need it either to
skip over or to put a zero (preferably) in these cases.

Thanks....Laurie

Jerry Whittle said:
and so on...
Click to expand...

How many more? You can start nesting IIf statement, but it gets slow and
hard to maintain after about 5 or 6 of them. It would look something like
this for just the 3 examples that you provided.

=IIf([costcode]="013210" And [costtype]="05320",
[txtEstimatedCost]/[txtCraftLabEst], IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],
IIf([costcode]="064201" and [costtype]="05320",
[txtEstimatedCost]/[txtCraftSuprLabEst],Null)))

There are Select Case statements in VB code that does the job better
especially if you are using ranges such as Between 1 and 5 = "Blue".

However it's possible that the best bet is another table or two. You could
then link the data between the two tables to get the numbers quicker.

Also that you have fields like txtCraftLabEst, txtFirewatchLabEst, and
txtCraftSuprLabEst make me wonder if your database isn't properly
designed/normalized and that's the root cause of the problem with the need
for convoluted IIf statements.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Alberta Rose said:
Awesome, you guys are great - works like a charm. I need to add other cost
codes and cost types to this string, how would I do that?

=IIf([costcode]="013210" And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],Null)

=IIf([costcode]="020110" and
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],Null)

=IIf([costcode]="064201" and
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],Null)

and so on...


Alberta Rose said:
OK, I have to try this again.

I have a convoluted issue and I hope I can describe it clear. I have a
report
(rptCloseout) that is fed from a query (qryLink). In the design mode of the
report, the "detail" area shows one line. When you run the report, all the
information for that contract comes in and has multiple lines on the report
view.

Now I need to do some calculations to show up in the detail area of
the report, but need to have my query recognize a specific cost code/cost
type
(the first two boxes in the detail line), and if this cost code/cost type is
for example 13210/5320, then perform a calculation that includes manhours
which are located in the report header area.

If it is not this combinatin of cost code/cost type, then continue to the
next cost code/cost type and check that. I know I need to add the formula to
the qryLink, but have no idea how to do it.

I have tried

txtEstPerMhr: IIf([costcode = 013210] and [costtype = 05320],
txtEstimatedCost/txtCraftLabEst, nul)

Also, would I put this formula in the qryLink, directly into the box on the
report or ???

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top