We help IT Professionals succeed at work.

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
Comment
Watch Question

Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Are you familiar with the Nz() function?

Can you post the SQL for the query?

mx

Commented:
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.

Commented:
Sorry:

SELECT DateDiff("s",fld1DateTIme,fld2DateTime)/3600 AS HrsDiff FROM myTable;
Mike EghtebasDatabase and Application Developer

Commented:
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;
Mike EghtebasDatabase and Application Developer

Commented:
change Now Now()

It assumed fld1DateTIme may be null at times.
>> 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
....

Commented:
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?

Commented:
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?

Author

Commented:
Yep, I used this one.  Thanks a bunch!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.