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
|
|||
|
|||
Need help with calculation if field is null
I am trying to populate a text box on a bound subdatasheet with the most
appropriate value for the user to see.. Based on wether the component has a value for overhaul, a value for retirement, or a value for both And whether or not the sub datasheet calculates a value for time since new(retirement) or time since overhaul On my main form wich lists the components info Part number Serial Number Ect. there are also two text boxes, one for the components retirement life, and the other for its Overhaul life the part that plagues me is some components have overhaul criteria some have retirement criteria and others have Both.. The subdatasheet lists installation data for each place the component was installed Parent time at install component time at install ect. and two calculated controls for Removal_TSN (time since new) and removal_TSO (time since overhaul) in the control on the subform i am trying to populate i want to show the user what will be due next... i want to run the following checks if the component has a retirement life listed and the subdatasheet was able to calculate the records Removal_TSN calculate the difference between the components retirement time and the removal_TSN Then Check the same for Overhaul If the component has an overhaul time listed and the subdatasheet was able to calculate the records Removal_TSO calculate the difference between the components overhaul time and Removal_TSO Then compare the two calculations and display the one with the least time remaining all while taking into consideration that some records may have null Removal times. here is what i am currently using.. =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) wich has worked wonderfully until i found an item it could not calculate because the calculation for Removal_TSN was greater than the overhaul OHlife even though the calculation for removal_TSO Was NULL Sorry if it seems senseless Thanks for Any Help |
#2
|
|||
|
|||
Need help with calculation if field is null
You don't have Nz function in use for the True and False parts of the
expression: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-Nz([Removal_TSO]),"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-Nz([Removal_TSN])) Change the above to this: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... I am trying to populate a text box on a bound subdatasheet with the most appropriate value for the user to see.. Based on wether the component has a value for overhaul, a value for retirement, or a value for both And whether or not the sub datasheet calculates a value for time since new(retirement) or time since overhaul On my main form wich lists the components info Part number Serial Number Ect. there are also two text boxes, one for the components retirement life, and the other for its Overhaul life the part that plagues me is some components have overhaul criteria some have retirement criteria and others have Both.. The subdatasheet lists installation data for each place the component was installed Parent time at install component time at install ect. and two calculated controls for Removal_TSN (time since new) and removal_TSO (time since overhaul) in the control on the subform i am trying to populate i want to show the user what will be due next... i want to run the following checks if the component has a retirement life listed and the subdatasheet was able to calculate the records Removal_TSN calculate the difference between the components retirement time and the removal_TSN Then Check the same for Overhaul If the component has an overhaul time listed and the subdatasheet was able to calculate the records Removal_TSO calculate the difference between the components overhaul time and Removal_TSO Then compare the two calculations and display the one with the least time remaining all while taking into consideration that some records may have null Removal times. here is what i am currently using.. =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) wich has worked wonderfully until i found an item it could not calculate because the calculation for Removal_TSN was greater than the overhaul OHlife even though the calculation for removal_TSO Was NULL Sorry if it seems senseless Thanks for Any Help |
#3
|
|||
|
|||
Need help with calculation if field is null
In rereading your post, I think I missed the real question you had.
You're saying that your calculation isn't working the way you want any more because you have a situation where nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO]) is less than nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]) even though Removal_TSO has a NULL value? So, you need a different calculation? How about if you show us examples of data and results that you want for the different conditions that you possibly may have, and then we can show you an expression to give you the desired result. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Ken Snell" wrote in message ... You don't have Nz function in use for the True and False parts of the expression: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-Nz([Removal_TSO]),"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-Nz([Removal_TSN])) Change the above to this: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... I am trying to populate a text box on a bound subdatasheet with the most appropriate value for the user to see.. Based on wether the component has a value for overhaul, a value for retirement, or a value for both And whether or not the sub datasheet calculates a value for time since new(retirement) or time since overhaul On my main form wich lists the components info Part number Serial Number Ect. there are also two text boxes, one for the components retirement life, and the other for its Overhaul life the part that plagues me is some components have overhaul criteria some have retirement criteria and others have Both.. The subdatasheet lists installation data for each place the component was installed Parent time at install component time at install ect. and two calculated controls for Removal_TSN (time since new) and removal_TSO (time since overhaul) in the control on the subform i am trying to populate i want to show the user what will be due next... i want to run the following checks if the component has a retirement life listed and the subdatasheet was able to calculate the records Removal_TSN calculate the difference between the components retirement time and the removal_TSN Then Check the same for Overhaul If the component has an overhaul time listed and the subdatasheet was able to calculate the records Removal_TSO calculate the difference between the components overhaul time and Removal_TSO Then compare the two calculations and display the one with the least time remaining all while taking into consideration that some records may have null Removal times. here is what i am currently using.. =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) wich has worked wonderfully until i found an item it could not calculate because the calculation for Removal_TSN was greater than the overhaul OHlife even though the calculation for removal_TSO Was NULL Sorry if it seems senseless Thanks for Any Help |
#4
|
|||
|
|||
Need help with calculation if field is null
sorry Ken i forgot to check the notify me of replies box, i figured nobody
wanted to touch this mess.. i will try my best with sample data.. The parent form shows a particular Part Number T_PartNumber info; P/N K747 Main rotor blade has a retirement life of 10,000 hours P/N 540-011 Main rotor Fitting has a Reirement life of 10,000 Hours AND an Overhaul requirement every 2000 hours. P/N 212-060 Gearbox has a 1500 Hr overhaul. The Subform(datasheet) lists the times for each seperate (serialized) item with the Main Forms PartNumber T_SerialNumber Info; K747 Blade S/N A11 currently has 6,000 TSN (Time since new) so i would like RET 4000 to be displayed 540-011 Fitting S/N B22 has 5000 Hrs TSN and 800 hrs TSO (Time since overhaul) 10,000-5,000=5,000 to retire and 2,000-800=1,200 to overhaul 5,0001,200 show OH 1,200 540-011 Fitting S/N C33 has 9,000 TSN and 0 TSO so 10,000-9,000=1000 to retire and 2,000-0=2,000 to overhaul so 1,0002,000 show RET 1,000 The issue is 540-011 Fitting S/N D44 has 8500 TSN and the record is missing a TSO so display 10,000-8500=1,500 RET 1,500 The next item may be missing the TSN, Because this is only a visual for the data entry person should i stick with the TextBox Formula I know i am close to getting it right, or should i run these tests (if's) in vba and set the combo value?? Thanks for your Help Barry "Ken Snell" wrote: In rereading your post, I think I missed the real question you had. You're saying that your calculation isn't working the way you want any more because you have a situation where nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO]) is less than nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]) even though Removal_TSO has a NULL value? So, you need a different calculation? How about if you show us examples of data and results that you want for the different conditions that you possibly may have, and then we can show you an expression to give you the desired result. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Ken Snell" wrote in message ... You don't have Nz function in use for the True and False parts of the expression: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-Nz([Removal_TSO]),"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-Nz([Removal_TSN])) Change the above to this: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... I am trying to populate a text box on a bound subdatasheet with the most appropriate value for the user to see.. Based on wether the component has a value for overhaul, a value for retirement, or a value for both And whether or not the sub datasheet calculates a value for time since new(retirement) or time since overhaul On my main form wich lists the components info Part number Serial Number Ect. there are also two text boxes, one for the components retirement life, and the other for its Overhaul life the part that plagues me is some components have overhaul criteria some have retirement criteria and others have Both.. The subdatasheet lists installation data for each place the component was installed Parent time at install component time at install ect. and two calculated controls for Removal_TSN (time since new) and removal_TSO (time since overhaul) in the control on the subform i am trying to populate i want to show the user what will be due next... i want to run the following checks if the component has a retirement life listed and the subdatasheet was able to calculate the records Removal_TSN calculate the difference between the components retirement time and the removal_TSN Then Check the same for Overhaul If the component has an overhaul time listed and the subdatasheet was able to calculate the records Removal_TSO calculate the difference between the components overhaul time and Removal_TSO Then compare the two calculations and display the one with the least time remaining all while taking into consideration that some records may have null Removal times. here is what i am currently using.. =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) wich has worked wonderfully until i found an item it could not calculate because the calculation for Removal_TSN was greater than the overhaul OHlife even though the calculation for removal_TSO Was NULL Sorry if it seems senseless Thanks for Any Help . |
#5
|
|||
|
|||
Need help with calculation if field is null
May I suggest that you approach this as four separate scenarios, which then
can be put into a single expression after we work out the details for each scenario? I'm going to list here what I believe the four scenarios to be, and I request that you comment on each one to let me know if I'm on right track or not. Scenario 1: No value for TSN In this case, you want to use just the TSO for calculating the value to be displayed. Scenario 2: No value for TSO In this case, you want to use just the TSN for calculating the value to be displayed. Scenario 3: Values for both TSN and TSO, where time remaining for RET is less than time remaining for OH In this case, you want to display the time remaining for RET. Scenario 4: Values for both TSN and TSO, where time remaining for OH is less than time remaining for RET In this case, you want to display the time remaining for OH. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... sorry Ken i forgot to check the notify me of replies box, i figured nobody wanted to touch this mess.. i will try my best with sample data.. The parent form shows a particular Part Number T_PartNumber info; P/N K747 Main rotor blade has a retirement life of 10,000 hours P/N 540-011 Main rotor Fitting has a Reirement life of 10,000 Hours AND an Overhaul requirement every 2000 hours. P/N 212-060 Gearbox has a 1500 Hr overhaul. The Subform(datasheet) lists the times for each seperate (serialized) item with the Main Forms PartNumber T_SerialNumber Info; K747 Blade S/N A11 currently has 6,000 TSN (Time since new) so i would like RET 4000 to be displayed 540-011 Fitting S/N B22 has 5000 Hrs TSN and 800 hrs TSO (Time since overhaul) 10,000-5,000=5,000 to retire and 2,000-800=1,200 to overhaul 5,0001,200 show OH 1,200 540-011 Fitting S/N C33 has 9,000 TSN and 0 TSO so 10,000-9,000=1000 to retire and 2,000-0=2,000 to overhaul so 1,0002,000 show RET 1,000 The issue is 540-011 Fitting S/N D44 has 8500 TSN and the record is missing a TSO so display 10,000-8500=1,500 RET 1,500 The next item may be missing the TSN, Because this is only a visual for the data entry person should i stick with the TextBox Formula I know i am close to getting it right, or should i run these tests (if's) in vba and set the combo value?? Thanks for your Help Barry "Ken Snell" wrote: In rereading your post, I think I missed the real question you had. You're saying that your calculation isn't working the way you want any more because you have a situation where nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO]) is less than nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]) even though Removal_TSO has a NULL value? So, you need a different calculation? How about if you show us examples of data and results that you want for the different conditions that you possibly may have, and then we can show you an expression to give you the desired result. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Ken Snell" wrote in message ... You don't have Nz function in use for the True and False parts of the expression: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-Nz([Removal_TSO]),"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-Nz([Removal_TSN])) Change the above to this: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... I am trying to populate a text box on a bound subdatasheet with the most appropriate value for the user to see.. Based on wether the component has a value for overhaul, a value for retirement, or a value for both And whether or not the sub datasheet calculates a value for time since new(retirement) or time since overhaul On my main form wich lists the components info Part number Serial Number Ect. there are also two text boxes, one for the components retirement life, and the other for its Overhaul life the part that plagues me is some components have overhaul criteria some have retirement criteria and others have Both.. The subdatasheet lists installation data for each place the component was installed Parent time at install component time at install ect. and two calculated controls for Removal_TSN (time since new) and removal_TSO (time since overhaul) in the control on the subform i am trying to populate i want to show the user what will be due next... i want to run the following checks if the component has a retirement life listed and the subdatasheet was able to calculate the records Removal_TSN calculate the difference between the components retirement time and the removal_TSN Then Check the same for Overhaul If the component has an overhaul time listed and the subdatasheet was able to calculate the records Removal_TSO calculate the difference between the components overhaul time and Removal_TSO Then compare the two calculations and display the one with the least time remaining all while taking into consideration that some records may have null Removal times. here is what i am currently using.. =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) wich has worked wonderfully until i found an item it could not calculate because the calculation for Removal_TSN was greater than the overhaul OHlife even though the calculation for removal_TSO Was NULL Sorry if it seems senseless Thanks for Any Help . |
#6
|
|||
|
|||
Need help with calculation if field is null
Ken
sometimes its good to get away from the SQL window and spend a little time breaking the steps down on paper... Heres where i am, Its ugly but i think it is more or less working at least for my current data. i need to spend more time with it and get it correct but i have been putting out other fires.. =IIf(nz([Forms]![F_ComponentInfo].[OHLife],99999)-nz([Removal_TSO])nz([Forms]![F_ComponentInfo].[RTLife],99999)-nz([Removal_TSN]),"OH " & nz([Forms]![F_ComponentInfo].[OHLife],99999)-[Removal_TSO],"RET " & nz([Forms]![F_ComponentInfo].[RTLife],99999)-[Removal_TSN]) Any help would of course be greatly appreciated.. Thanks Barry "Ken Snell" wrote: May I suggest that you approach this as four separate scenarios, which then can be put into a single expression after we work out the details for each scenario? I'm going to list here what I believe the four scenarios to be, and I request that you comment on each one to let me know if I'm on right track or not. Scenario 1: No value for TSN In this case, you want to use just the TSO for calculating the value to be displayed. Scenario 2: No value for TSO In this case, you want to use just the TSN for calculating the value to be displayed. Scenario 3: Values for both TSN and TSO, where time remaining for RET is less than time remaining for OH In this case, you want to display the time remaining for RET. Scenario 4: Values for both TSN and TSO, where time remaining for OH is less than time remaining for RET In this case, you want to display the time remaining for OH. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... sorry Ken i forgot to check the notify me of replies box, i figured nobody wanted to touch this mess.. i will try my best with sample data.. The parent form shows a particular Part Number T_PartNumber info; P/N K747 Main rotor blade has a retirement life of 10,000 hours P/N 540-011 Main rotor Fitting has a Reirement life of 10,000 Hours AND an Overhaul requirement every 2000 hours. P/N 212-060 Gearbox has a 1500 Hr overhaul. The Subform(datasheet) lists the times for each seperate (serialized) item with the Main Forms PartNumber T_SerialNumber Info; K747 Blade S/N A11 currently has 6,000 TSN (Time since new) so i would like RET 4000 to be displayed 540-011 Fitting S/N B22 has 5000 Hrs TSN and 800 hrs TSO (Time since overhaul) 10,000-5,000=5,000 to retire and 2,000-800=1,200 to overhaul 5,0001,200 show OH 1,200 540-011 Fitting S/N C33 has 9,000 TSN and 0 TSO so 10,000-9,000=1000 to retire and 2,000-0=2,000 to overhaul so 1,0002,000 show RET 1,000 The issue is 540-011 Fitting S/N D44 has 8500 TSN and the record is missing a TSO so display 10,000-8500=1,500 RET 1,500 The next item may be missing the TSN, Because this is only a visual for the data entry person should i stick with the TextBox Formula I know i am close to getting it right, or should i run these tests (if's) in vba and set the combo value?? Thanks for your Help Barry "Ken Snell" wrote: In rereading your post, I think I missed the real question you had. You're saying that your calculation isn't working the way you want any more because you have a situation where nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO]) is less than nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]) even though Removal_TSO has a NULL value? So, you need a different calculation? How about if you show us examples of data and results that you want for the different conditions that you possibly may have, and then we can show you an expression to give you the desired result. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Ken Snell" wrote in message ... You don't have Nz function in use for the True and False parts of the expression: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-Nz([Removal_TSO]),"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-Nz([Removal_TSN])) Change the above to this: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... I am trying to populate a text box on a bound subdatasheet with the most appropriate value for the user to see.. Based on wether the component has a value for overhaul, a value for retirement, or a value for both And whether or not the sub datasheet calculates a value for time since new(retirement) or time since overhaul On my main form wich lists the components info Part number Serial Number Ect. there are also two text boxes, one for the components retirement life, and the other for its Overhaul life the part that plagues me is some components have overhaul criteria some have retirement criteria and others have Both.. The subdatasheet lists installation data for each place the component was installed Parent time at install component time at install ect. and two calculated controls for Removal_TSN (time since new) and removal_TSO (time since overhaul) in the control on the subform i am trying to populate i want to show the user what will be due next... i want to run the following checks if the component has a retirement life listed and the subdatasheet was able to calculate the records Removal_TSN calculate the difference between the components retirement time and the removal_TSN Then Check the same for Overhaul If the component has an overhaul time listed and the subdatasheet was able to calculate the records Removal_TSO calculate the difference between the components overhaul time and Removal_TSO Then compare the two calculations and display the one with the least time remaining all while taking into consideration that some records may have null Removal times. here is what i am currently using.. =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) wich has worked wonderfully until i found an item it could not calculate because the calculation for Removal_TSN was greater than the overhaul OHlife even though the calculation for removal_TSO Was NULL Sorry if it seems senseless Thanks for Any Help . . |
#7
|
|||
|
|||
Need help with calculation if field is null
I don't see your expression as "ugly". It's a single IIf function, which is
straightforward. If it's working, I'd continue to use it. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message news Ken sometimes its good to get away from the SQL window and spend a little time breaking the steps down on paper... Heres where i am, Its ugly but i think it is more or less working at least for my current data. i need to spend more time with it and get it correct but i have been putting out other fires.. =IIf(nz([Forms]![F_ComponentInfo].[OHLife],99999)-nz([Removal_TSO])nz([Forms]![F_ComponentInfo].[RTLife],99999)-nz([Removal_TSN]),"OH " & nz([Forms]![F_ComponentInfo].[OHLife],99999)-[Removal_TSO],"RET " & nz([Forms]![F_ComponentInfo].[RTLife],99999)-[Removal_TSN]) Any help would of course be greatly appreciated.. Thanks Barry "Ken Snell" wrote: May I suggest that you approach this as four separate scenarios, which then can be put into a single expression after we work out the details for each scenario? I'm going to list here what I believe the four scenarios to be, and I request that you comment on each one to let me know if I'm on right track or not. Scenario 1: No value for TSN In this case, you want to use just the TSO for calculating the value to be displayed. Scenario 2: No value for TSO In this case, you want to use just the TSN for calculating the value to be displayed. Scenario 3: Values for both TSN and TSO, where time remaining for RET is less than time remaining for OH In this case, you want to display the time remaining for RET. Scenario 4: Values for both TSN and TSO, where time remaining for OH is less than time remaining for RET In this case, you want to display the time remaining for OH. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... sorry Ken i forgot to check the notify me of replies box, i figured nobody wanted to touch this mess.. i will try my best with sample data.. The parent form shows a particular Part Number T_PartNumber info; P/N K747 Main rotor blade has a retirement life of 10,000 hours P/N 540-011 Main rotor Fitting has a Reirement life of 10,000 Hours AND an Overhaul requirement every 2000 hours. P/N 212-060 Gearbox has a 1500 Hr overhaul. The Subform(datasheet) lists the times for each seperate (serialized) item with the Main Forms PartNumber T_SerialNumber Info; K747 Blade S/N A11 currently has 6,000 TSN (Time since new) so i would like RET 4000 to be displayed 540-011 Fitting S/N B22 has 5000 Hrs TSN and 800 hrs TSO (Time since overhaul) 10,000-5,000=5,000 to retire and 2,000-800=1,200 to overhaul 5,0001,200 show OH 1,200 540-011 Fitting S/N C33 has 9,000 TSN and 0 TSO so 10,000-9,000=1000 to retire and 2,000-0=2,000 to overhaul so 1,0002,000 show RET 1,000 The issue is 540-011 Fitting S/N D44 has 8500 TSN and the record is missing a TSO so display 10,000-8500=1,500 RET 1,500 The next item may be missing the TSN, Because this is only a visual for the data entry person should i stick with the TextBox Formula I know i am close to getting it right, or should i run these tests (if's) in vba and set the combo value?? Thanks for your Help Barry "Ken Snell" wrote: In rereading your post, I think I missed the real question you had. You're saying that your calculation isn't working the way you want any more because you have a situation where nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO]) is less than nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]) even though Removal_TSO has a NULL value? So, you need a different calculation? How about if you show us examples of data and results that you want for the different conditions that you possibly may have, and then we can show you an expression to give you the desired result. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Ken Snell" wrote in message ... You don't have Nz function in use for the True and False parts of the expression: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-Nz([Removal_TSO]),"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-Nz([Removal_TSN])) Change the above to this: =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) -- Ken Snell http://www.accessmvp.com/KDSnell/ "Barry A&P" wrote in message ... I am trying to populate a text box on a bound subdatasheet with the most appropriate value for the user to see.. Based on wether the component has a value for overhaul, a value for retirement, or a value for both And whether or not the sub datasheet calculates a value for time since new(retirement) or time since overhaul On my main form wich lists the components info Part number Serial Number Ect. there are also two text boxes, one for the components retirement life, and the other for its Overhaul life the part that plagues me is some components have overhaul criteria some have retirement criteria and others have Both.. The subdatasheet lists installation data for each place the component was installed Parent time at install component time at install ect. and two calculated controls for Removal_TSN (time since new) and removal_TSO (time since overhaul) in the control on the subform i am trying to populate i want to show the user what will be due next... i want to run the following checks if the component has a retirement life listed and the subdatasheet was able to calculate the records Removal_TSN calculate the difference between the components retirement time and the removal_TSN Then Check the same for Overhaul If the component has an overhaul time listed and the subdatasheet was able to calculate the records Removal_TSO calculate the difference between the components overhaul time and Removal_TSO Then compare the two calculations and display the one with the least time remaining all while taking into consideration that some records may have null Removal times. here is what i am currently using.. =IIf(nz(Forms!F_ComponentInfo.OHLife,99999)-nz([Removal_TSO])nz(Forms!F_ComponentInfo.RTLife,99999)-nz([Removal_TSN]),"OH " & nz(Forms!F_ComponentInfo.OHLife,99999)-[Removal_TSO],"RET " & nz(Forms!F_ComponentInfo.RTLife,99999)-[Removal_TSN]) wich has worked wonderfully until i found an item it could not calculate because the calculation for Removal_TSN was greater than the overhaul OHlife even though the calculation for removal_TSO Was NULL Sorry if it seems senseless Thanks for Any Help . . |
Thread Tools | |
Display Modes | |
|
|