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" statements
I have a convoluted issue and I hope I can describe it clear. I have a form
(frmCloseout) that is fed from a query (qryLink). In the design mode of the form, 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 form, 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 true, then perform a calculation that includes manhours which are located in the report header area of the form. If it is false, 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. Anyone want to tackle this with me |
#2
|
|||
|
|||
"if" statements
One question for clarification is the "false" part - when you say that you
want to "continue to the next cost code/cost type and check that", do you mean to say that for the current record which has been evaluated as false, you want the result for that "false" record to be blank/null/do nothing? To me, when you say continue, that means going to the next record. Try this: MyFieldName: IIf([Cost Code/Cost Type field] = -1, insert calculation here, Null) Where I have Null, you could put zero as well if you want to have a value in the field. HTH "Alberta Rose" wrote: I have a convoluted issue and I hope I can describe it clear. I have a form (frmCloseout) that is fed from a query (qryLink). In the design mode of the form, 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 form, 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 true, then perform a calculation that includes manhours which are located in the report header area of the form. If it is false, 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. Anyone want to tackle this with me |
#3
|
|||
|
|||
"if" statements
Yes, i want it to continue and look at the next record. Then on to the next
until it finds a match and run the formula on that record, then onto the next, etc. "Pendragon" wrote: One question for clarification is the "false" part - when you say that you want to "continue to the next cost code/cost type and check that", do you mean to say that for the current record which has been evaluated as false, you want the result for that "false" record to be blank/null/do nothing? To me, when you say continue, that means going to the next record. Try this: MyFieldName: IIf([Cost Code/Cost Type field] = -1, insert calculation here, Null) Where I have Null, you could put zero as well if you want to have a value in the field. HTH "Alberta Rose" wrote: I have a convoluted issue and I hope I can describe it clear. I have a form (frmCloseout) that is fed from a query (qryLink). In the design mode of the form, 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 form, 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 true, then perform a calculation that includes manhours which are located in the report header area of the form. If it is false, 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. Anyone want to tackle this with me |
#4
|
|||
|
|||
"if" statements
That's what I had guessed, so try out my suggestion. Post back if you need
more. "Alberta Rose" wrote: Yes, i want it to continue and look at the next record. Then on to the next until it finds a match and run the formula on that record, then onto the next, etc. "Pendragon" wrote: One question for clarification is the "false" part - when you say that you want to "continue to the next cost code/cost type and check that", do you mean to say that for the current record which has been evaluated as false, you want the result for that "false" record to be blank/null/do nothing? To me, when you say continue, that means going to the next record. Try this: MyFieldName: IIf([Cost Code/Cost Type field] = -1, insert calculation here, Null) Where I have Null, you could put zero as well if you want to have a value in the field. HTH "Alberta Rose" wrote: I have a convoluted issue and I hope I can describe it clear. I have a form (frmCloseout) that is fed from a query (qryLink). In the design mode of the form, 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 form, 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 true, then perform a calculation that includes manhours which are located in the report header area of the form. If it is false, 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. Anyone want to tackle this with me |
#5
|
|||
|
|||
"if" statements
Thanks, I'm unsure of where to put this code. Should it be in the box on my
report? Or in my query? Or ??? txtEstPerMhr: IIf([CostCode/CostType field]=-1, txtEstimatedCost/txtCraftLabEst, nul) or would I write it as: txtEstPerMhr: IIf([013210/05320]=-1, txtEstimatedCost/txtCraftLabEst, nul) Cost code and cost type are not in the same field, but both have to match before the formula is run. Thanks for your help with this "Pendragon" wrote: That's what I had guessed, so try out my suggestion. Post back if you need more. "Alberta Rose" wrote: Yes, i want it to continue and look at the next record. Then on to the next until it finds a match and run the formula on that record, then onto the next, etc. "Pendragon" wrote: One question for clarification is the "false" part - when you say that you want to "continue to the next cost code/cost type and check that", do you mean to say that for the current record which has been evaluated as false, you want the result for that "false" record to be blank/null/do nothing? To me, when you say continue, that means going to the next record. Try this: MyFieldName: IIf([Cost Code/Cost Type field] = -1, insert calculation here, Null) Where I have Null, you could put zero as well if you want to have a value in the field. HTH "Alberta Rose" wrote: I have a convoluted issue and I hope I can describe it clear. I have a form (frmCloseout) that is fed from a query (qryLink). In the design mode of the form, 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 form, 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 true, then perform a calculation that includes manhours which are located in the report header area of the form. If it is false, 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. Anyone want to tackle this with me |
Thread Tools | |
Display Modes | |
|
|