Hello!
I am trying to count the number of records where the "BusinessHours" field equals a certain timeframe. My timeframes are <=4 hours, Between 4:01 And 5 hours, Between 5:01 And 10 Hours and >10 Hours.
Something is off and I am counting too many records. For example, I have 178 <=4 but I am calculating 188. The field is formatted in the table as Date/Time - Short Time.
Here is my current SQL
Code:
SELECT Count([tbl_HomeOwner Claims].Claim) AS CountOfClaim, Count(IIf([tbl_HomeOwner Claims]![BusinessHours]<=#12/30/1899 4:0:0#,[Claim],Null)) AS [Contact <4 Hours], [Contact <4 Hours]/[CountOfClaim] AS [Percent < 4 Hours], Count(IIf([tbl_HomeOwner Claims]![BusinessHours] Between #12/30/1899 4:1:0# And #12/30/1899 5:0:0#,[Claim],Null)) AS [Contact Between 4-5 Hours], [Contact Between 4-5 Hours]/[CountOfClaim] AS [Percent Between 4-5 Hours], Count(IIf([tbl_HomeOwner Claims]![BusinessHours] Between #12/30/1899 5:1:0# And #12/30/1899 10:0:0#,[Claim],Null)) AS [Contact Between 5-10 Hours], [Contact Between 5-10 Hours]/[CountOfClaim] AS [Percent Between 5-10 Hours], Count(IIf([tbl_HomeOwner Claims]![BusinessHours]>#12/30/1899 10:0:0#,[Claim],Null)) AS [Contact >10 Hours], [Contact >10 Hours]/[CountOfClaim] AS [Percent >10 Hours], Count(IIf([tbl_HomeOwner Claims]![DaysReportedToClosed] Is Null,[Claim],Null)) AS [Open Claims]
FROM [tbl_HomeOwner Claims];
Thank you for whatever help you can offer!