If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"If" Statement help needed
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 |
#2
|
|||
|
|||
"If" Statement help needed
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" wrote: 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 |
#3
|
|||
|
|||
"If" Statement help needed
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) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Alberta Rose" wrote: 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 |
#4
|
|||
|
|||
"If" Statement help needed
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" wrote: 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 |
#5
|
|||
|
|||
"If" Statement help needed
I posted just one more question.....
"Duane Hookom" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
"If" Statement help needed
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" wrote: 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" wrote: 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 |
#7
|
|||
|
|||
"If" Statement help needed
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" wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|