Subtracting dcount from dcount

[mikerea90]

Hey all,

Is it possible to subtract dcount from dcount in a query? I am so far unable to do this.

My code looked like this:

Expr4: DCount("[Index]", "tblGPR", "[tblGPR]![ReceivedDate]") - DCount("[Index]", "tblGPR", "[tblGPR]![ClosedDate]")

I would prefer to have something like this:

Expr4: CountofClosedDate - CountofReceivedDate

Thanks!

 

[SOS]

Your DCounts are not correct. You are not passing the criteria properly.

For example, you have

DCount("[Index]", "tblGPR", "[tblGPR]![ReceivedDate]")

but [tblGPR]![ReceivedDate] is not a valid criteria. You need something like

"[ReceivedDate]=#" & [YourFieldInTheQuery] & "#"

 

[mikerea90]

I tried this:

Expr4: DCount("[Index]","tblGPR","[tblGPR]![DateReceived] = Between Forms![frmReports]![StartDate] And Forms![frmReports]![EndDate]”) - DCount("[Index]","tblGPR","[tblGPR]![Closed] = Between Forms![frmReports]![StartDate] And Forms![frmReports]![EndDate]")

But it didn't work

 

[SOS]

What is your full SQL Statement?

Also, the syntax would be:

DCount("[Index]","tblGPR","[DateReceived] = Between #" & [Forms]![frmReports]![StartDate] & "# And #" & [Forms]![frmReports]![EndDate] & "#”)

You need to keep the form references outside of the quotes and you need to use octothorpes (#). Also, since you have already specified the table in the table parameter you don't use it in the where clause. You just use the field.

 

[mikerea90]

SELECT tblGPR.Index, tblGPR.DateReceivedinA, tblGPR.DateReceivedinB, Count(tblGPR.DateReceivedinB) AS Expr1, tblGPR.Closed, Count(tblGPR.Closed) AS Expr2, tblGPR.AssignedTo, tblGPR.IssueType, Abs(tblGPR!Closed>tblGPR!DueDate) AS Expr3, tblGPR.DueDate
FROM tblGPR
GROUP BY tblGPR.Index, tblGPR.DateReceivedinA, tblGPR.DateReceivedinB, tblGPR.Closed, tblGPR.AssignedTo, tblGPR.IssueType, Abs(tblGPR!Closed>tblGPR!DueDate), tblGPR.DueDate
HAVING (((tblGPR.DateReceivedinB) Between [Forms]![frmReports]![StartDate] And [Forms]![frmReports]![EndDate]));

 

[mikerea90]

What would be ideal, is to have Expr4: Expr2 - Expr1. I'm not sure if you can do that though. Thank you so far for the help.

 

[mikerea90]

Could I just do Expr4: Count(Closed) - Count(DatedReceived)

 

[SOS]

Possibly, why are you using Expr4 instead of a meaningful name? I would rather have meaningful things in my SQL and code so it is apparent right away what it is supposed to be.

 

[mikerea90]

Alright well say I call it 'Open,' do you know how I would be able to write the above expression?

 

[SOS]

Well, my tests indicate you can just use the

Count([FieldName]) As Expr1, Count([Field2Name]) As Expr2, [Expr2]-[Expr1] As Expr3

but you would need to do those in a separate query because if you add any other fields in, it breaks.

 

[mikerea90]

Thanks! That worked out.

 

[SOS]

Glad to hear.