Access Ignore Null values in Equations

Greetings,

I have several equations in a query that converts time to different formats.   I have another query that subtracts one of the equation values from a view, and then displays on a form.  Works great unless one of the fields used for the equation happens to be null.  The problem is that one of the two fields will always be null (time in and time out) for a given period of time.

How do I have the equation to ignore the nulls but provide the information for the rows that contains both fields?

Thanks,

Heather
Heather_DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are you familiar with the Nz() function?

Can you post the SQL for the query?

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
SELECT DateDiff("s",fld1DateTIme,fld2DateTime) AS HrsDiff FROM myTable;

Will give you Null if either or both values is null, and decimal hours if both fields are valid.  Is is acceptable to have a null as a return?  This doesn't throw any error.  Perhaps you could give a few examples of what you have, and what you want to calculate, including how you are trying to do it.
0
GRayLCommented:
Sorry:

SELECT DateDiff("s",fld1DateTIme,fld2DateTime)/3600 AS HrsDiff FROM myTable;
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mike EghtebasDatabase and Application DeveloperCommented:
Is it acceptable to use Now() when the date is missing? If so use GRayL's solution like:

SELECT DateDiff("s",Nz(fld1DateTIme, Now),fld2DateTime)/3600 AS HrsDiff FROM myTable;
0
Mike EghtebasDatabase and Application DeveloperCommented:
change Now Now()

It assumed fld1DateTIme may be null at times.
0
Jinesh KamdarCommented:
>> How do I have the equation to ignore the nulls but provide the information for the rows that contains both fields?

If u wanna ignore all the rows for which any of these fields are NULL and select only those for which both fields have values, then filter them out in the WHERE clause.

SELECT ...
FROM ...
WHERE ...
AND field1 IS NOT NULL AND field2 IS NOT NULL
....
0
GRayLCommented:
Heather:  Ff you run the Select query I gave you using two datetime fields per record, any record with a null in either or both values will return a null, otherwise the value in what ever units you specify in the DateDiff() function.  I chose seconds and then divided by 3600 seconds per hour to give hours.  You could wrap the result in the Round() function to limit the decimal places to your liking.  Seem like a plan?
0
GRayLCommented:
Heather:  If you run the Select query I gave you using two datetime fields per record, any record with a null in either or both values will return a null, otherwise the value in what ever units you specify in the DateDiff() function.  I chose seconds and then divided by 3600 seconds per hour to give hours.  You could wrap the result in the Round() function to limit the decimal places to your liking.  Seem like a plan?
0
Heather_DAuthor Commented:
Yep, I used this one.  Thanks a bunch!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.