A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need help with calculation if field is null



 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old November 14th, 2009, 12:41 AM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.