Advertisement

07.23.2008 at 10:07AM PDT, ID: 23589331
[x]
Attachment Details

What's wrong with my Sum function? Trying to subtract dates within a sum function and then sum.

Asked by JA67 in Microsoft Access Database, SQL Query Syntax

Tags: Microsoft, Access, 2003

Is the below sum function valid in it's syntax? I am trying to subtract the requested date from the closed date and then sum the results. I am not getting an error, but the results are all zero, which is definately incorrect.

-Sum(Format(closed_dt,"yyyymm")=[Forms]![frmRSS_TtlsByFrq]![cmbYearMonth] - Format(requested_dt,"yyyymm")=[Forms]![frmRSS_TtlsByFrq]![cmbYearMonth]) AS TurnAroundTime


If I run the below query, it works and returns exactly the correct results.

SELECT
request_dept_nm,
sum(DateDiff("d", requested_dt, closed_dt)) As DaysDifference
FROM request_fact
WHERE
request_dept_nm="Information Delivery - Sales, Marketing, UW" AND
Format(closed_dt,"yyyymm")=[Forms]![frmRSS_TtlsByFrq]![cmbYearMonth]  AND
Format(requested_dt,"yyyymm")=[Forms]![frmRSS_TtlsByFrq]![cmbYearMonth]
GROUP BY request_dept_nmStart Free Trial
[+][-]07.23.2008 at 10:12AM PDT, ID: 22071456

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 10:30AM PDT, ID: 22071694

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 10:32AM PDT, ID: 22071719

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 11:29AM PDT, ID: 22072339

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 11:47AM PDT, ID: 22072526

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 11:52AM PDT, ID: 22072580

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Access Database, SQL Query Syntax
Tags: Microsoft, Access, 2003
Sign Up Now!
Solution Provided By: peter57r
Participating Experts: 2
Solution Grade: A
 
 
[+][-]07.23.2008 at 11:57AM PDT, ID: 22072623

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 11:59AM PDT, ID: 22072635

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.28.2008 at 12:26PM PDT, ID: 22106090

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628