[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

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
0
Heather_D
Asked:
Heather_D
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Are you familiar with the Nz() function?

Can you post the SQL for the query?

mx
0
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now