Need help with calculation if field is null

B

Barry A&P

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
 
K

Ken Snell

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 said:
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
Click to expand...
 
K

Ken Snell

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Barry A&P

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,000>1,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,000<2,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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell

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 said:
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,000>1,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,000<2,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 said:
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 said:
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/



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
Click to expand...


.
Click to expand...
Click to expand...
 
B

Barry A&P

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 said:
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 said:
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,000>1,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,000<2,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 said:
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/


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/



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




.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell

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 said:
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 said:
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 said:
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,000>1,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,000<2,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

:

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/


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/



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




.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

couple subs I need help with 1
Need help with an IF Function 4
Access 97 - Simple Calculation 12
Help with an expression to calculate a profit from one of three fi 2
HELP NEEDED: Running Balance with Data Containing NULL 2
Combine IIf with NZ to get null for zero calculated sum? 3
Need help with a formula 8
Need help with formula 7

Top