429,101 Members | 1,335 Online
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

# Sum IF in Access

 P: n/a Is there a SumIF function in Access? I have to SUM a duration of time, but only if a logical Yes/No check box is TRUE. I am working with the expression builder and a Text Box. Thanks for the help. Forgive me...I'm a beginner programmer with expert expectations. *** Sent via Developersdex http://www.developersdex.com *** Aug 8 '06 #1
4 Replies

 P: n/a Hi - You need to nest your IF and SUM functions, like so: IF(y/n field=y,SUM(value1,value2)null) This means if y/n criteria is true, the sum of value1 and value2 appears, if not then a null (blank) value appears. Eddie Tee GEE wrote: Is there a SumIF function in Access? I have to SUM a duration of time, but only if a logical Yes/No check box is TRUE. I am working with the expression builder and a Text Box. Thanks for the help. Forgive me...I'm a beginner programmer with expert expectations. *** Sent via Developersdex http://www.developersdex.com *** Aug 8 '06 #2

 P: n/a =Sum(IIf([OK]=True,[Date],0)) This is going to Sum the data. If you want to sum the duration such as from 8am to 4pm then you might look at the TimeValue function. Returns one of two parts, depending on the evaluation of an expression. Syntax IIf(expr, truepart, falsepart) The IIf function syntax has these named arguments: Part Description expr Required. Expression you want to evaluate. truepart Required. Value or expression returned if expr is True. falsepart Required. Value or expression returned if expr is False. Remarks IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True. Tee GEE wrote: Is there a SumIF function in Access? I have to SUM a duration of time, but only if a logical Yes/No check box is TRUE. I am working with the expression builder and a Text Box. Thanks for the help. Forgive me...I'm a beginner programmer with expert expectations. *** Sent via Developersdex http://www.developersdex.com *** Aug 8 '06 #3

 P: n/a =Sum(IIf([Your logical yes/no field]=True,[Your duration of time field],0)) This is going to Sum the data. If you want to sum the duration such as from 8am to 4pm then you might look at the TimeValue function. Returns one of two parts, depending on the evaluation of an expression. Syntax IIf(expr, truepart, falsepart) The IIf function syntax has these named arguments: Part Description expr Required. Expression you want to evaluate. truepart Required. Value or expression returned if expr is True. falsepart Required. Value or expression returned if expr is False. Remarks IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True. Tee GEE wrote: Is there a SumIF function in Access? I have to SUM a duration of time, but only if a logical Yes/No check box is TRUE. I am working with the expression builder and a Text Box. Thanks for the help. Forgive me...I'm a beginner programmer with expert expectations. *** Sent via Developersdex http://www.developersdex.com *** Aug 8 '06 #4

 P: n/a And, another way would be to use the DSUM function: Dsum("[Timefield]","TableName","CheckField=True") Chris Nebinger Tee GEE wrote: Is there a SumIF function in Access? I have to SUM a duration of time, but only if a logical Yes/No check box is TRUE. I am working with the expression builder and a Text Box. Thanks for the help. Forgive me...I'm a beginner programmer with expert expectations. *** Sent via Developersdex http://www.developersdex.com *** Aug 8 '06 #5

### This discussion thread is closed

Replies have been disabled for this discussion.