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
.