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
.