IIF Statement Not Calculating Correctly.

[jontout]

For the life of me I can't get this query to work, does anyone have an idea?

This particular data, I should have the following figures, but the Incomplete doesn't calculate. These figures are for work done on a particular day. The SLA MET & NOT MET figures don't need to match the RECEIVED, it's a coincedance (for this particular date) that they do.

12 RECEIVED - New Records
5 SLA MET
7 SLA NOT MET
4 INCOMPLETE

So here's the code.


SELECT tblClaims.RecDate, Count(*) AS Received,
Sum(IIf(ProcessDays Between 1 And 3,1,0)) AS [SLA MET],
Sum(IIf(ProcessDays>3,1,0)) AS [SLA NOT MET],
Sum(IIf(ProcessDays<1,1,0)) AS [INCOMPLETE]
FROM tblClaims
WHERE (((tblClaims.RecDate)= #9/26/2007#)
AND ((tblClaims.CompDate) Not Between "#01/09/2007#" And "#30/09/2007#"))
GROUP BY tblClaims.RecDate
ORDER BY tblClaims.RecDate;

and the IMCOMPLETE section is where it's not counting.

Thanks in advance,

Jon

 

[HarleyQuinn]

Thanks Jon.

I'll re-post my original reply:

I'm not understanding how you can receive 12, but then have it break down into 16?

The SLA Met and SLA Not Met seem to account for all 12 records?

Cheers

HarleyQuinn
---------------------------------
If you never say your name out loud to anyone they can never ever call you by it - Regina Spektor

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[lespaul]

what does the data look like in tblClaims?

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[SkipVought]

Hi,

No BETWEEN in IIF()
\

Sum(IIf(ProcessDays>=1 And ProcessDays<=3,1,0)) AS [SLA MET],

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[HarleyQuinn]

Re-reading your new post I see my point is answered.

Sorry about the Jon

HarleyQuinn
---------------------------------
If you never say your name out loud to anyone they can never ever call you by it - Regina Spektor

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[dhookom]

Dates aren't delimited wit both double-quotes and #. Also, dates should be in the US date format of mm/dd/yyyy.

The first step I would take in trouble shooting is to create a query like:

SELECT tblClaims.*
FROM tblClaims
WHERE tblClaims.RecDate=  #9/26/2007#
AND tblClaims.CompDate Not Between #09/01/2007# And #09/30/2007#
AND ProcessDays<1;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jontout]

D'oh!
I've spent 3 days on this and I've just realised that the SUM IIF is working, but my date criteria isn't!

Thanks to Duanne for pointing this out, I realised the delimiters, but I've been hitting the cut'n'pastes pretty solidly for a while.

Thanks for the pointers,

Cheers,

Jon

 

[dhookom]

After reviewing this thread, I would recommend removing your business calculations and logic from your query. Queries should not contain this type of calculation since changing business logic should not require changing calculations in queries.

I would create a small user-defined function that accepts the ProcessDays and returns a text value like "SLA MET, SLA NOT MET, or INCOMPLETE.

Public Function ProcessStatus(intProcDays As Integer) As String
    Select Case intProcDays
        Case 1 To 3
            ProcessStatus = "SLA MET"
        Case Is < 1
            ProcessStatus = "INCOMPLETE"
        Case Else
            ProcessStatus = "SLA NOT MET"
    End Select
End Function

Save this function in a module with a name like "modBusinessCalcs".

You can then use a crosstab query with a column heading of
ColHeadrocessStatus([ProcessDays])

RecDate and Count of Received would be the Row Headings and Count of ProcessDays would be the value.

When your definitions of "MET" change, you can edit the function rather than opening a query design.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]