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 |
Thread Tools | |
Display Modes | |
|
|