Report concantanates instead of adding values.

[Juan Schwartz]

I am using the query:


TRANSFORM Count(NZ(Count([Itemized Records].Subject),0)) AS Calls
SELECT Format([Date],"yyyy-mm-dd") AS SortDate, [Itemized
Records].Subject
FROM [Itemized Records]
WHERE (((Format([Date],"mm/dd/yy"))=[Enter date(mm/dd/yy): ]))
GROUP BY Format([Date],"yyyy-mm-dd"), [Itemized Records].Subject
ORDER BY Format([Date],"yyyy-mm-dd")
PIVOT Format([Date],"hh") & ":" &
IIf(Int(Format([Date],"n"))>=30,"30","00") In
("00:00","00:30","01:00","01:30","02:00","02:30","03:00","03:30","04:00","0­4:30","05:00","05:30","06:00","06:30","07:00","07:30","08:00","08:30","09:0­0","09:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30",­"14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18­:30","19:00","19:30","20:00","20:30","21:00","21:30","22:00","22:30","23:00­","23:30");



When I display the numbers in the report, they work fine. However, when

I use...


=[00:00]+[00:30]+[01:00]+[01:30]+[02:00]+[02:30]+[03:00]+[03:30]+[04:00]+[0­4:30]+[05:00]+[05:30]+[06:00]+[06:30]+[07:00]+[07:30]+[08:00]+[08:30]+[09:0­0]+[09:30]+[10:00]+[10:30]+[11:00]+[11:30]



In the report to total the row horizontally in the detail portion of
the query, it does not work...


In the footer, I use SUM([00:00]) and that works to add the totals
vertically.


Any help would be appreciated...

 

[Duane Hookom]

Try change your SQL to create another row heading:

TRANSFORM Count(NZ(Count([Itemized Records].Subject),0)) AS Calls
SELECT Format([Date],"yyyy-mm-dd") AS SortDate, [Itemized
Records].Subject, Sum(Abs(TimeValue([Date])<.5)) as AMCount
FROM [Itemized Records]
WHERE (((Format([Date],"mm/dd/yy"))=[Enter date(mm/dd/yy): ]))
GROUP BY Format([Date],"yyyy-mm-dd"), [Itemized Records].Subject
ORDER BY Format([Date],"yyyy-mm-dd")
PIVOT Format([Date],"hh") & ":" &
IIf(Int(Format([Date],"n"))>=30,"30","00") In
("00:00","00:30","01:00","01:30","02:00","02:30","03:00","03:30","04:00","0­4:30","05:00","05:30","06:00","06:30","07:00","07:30","08:00","08:30","09:0­0","09:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30",­"14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18­:30","19:00","19:30","20:00","20:30","21:00","21:30","22:00","22:30","23:00­","23:30");

Your other option may be to change your expression to use
=Val([00:00])+Val([00:30])+...

--
Duane Hookom
MS Access MVP
--

I am using the query:


TRANSFORM Count(NZ(Count([Itemized Records].Subject),0)) AS Calls
SELECT Format([Date],"yyyy-mm-dd") AS SortDate, [Itemized
Records].Subject
FROM [Itemized Records]
WHERE (((Format([Date],"mm/dd/yy"))=[Enter date(mm/dd/yy): ]))
GROUP BY Format([Date],"yyyy-mm-dd"), [Itemized Records].Subject
ORDER BY Format([Date],"yyyy-mm-dd")
PIVOT Format([Date],"hh") & ":" &
IIf(Int(Format([Date],"n"))>=30,"30","00") In
("00:00","00:30","01:00","01:30","02:00","02:30","03:00","03:30","04:00","0­4:30","05:00","05:30","06:00","06:30","07:00","07:30","08:00","08:30","09:0­0","09:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30",­"14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18­:30","19:00","19:30","20:00","20:30","21:00","21:30","22:00","22:30","23:00­","23:30");



When I display the numbers in the report, they work fine. However, when

I use...


=[00:00]+[00:30]+[01:00]+[01:30]+[02:00]+[02:30]+[03:00]+[03:30]+[04:00]+[0­4:30]+[05:00]+[05:30]+[06:00]+[06:30]+[07:00]+[07:30]+[08:00]+[08:30]+[09:0­0]+[09:30]+[10:00]+[10:30]+[11:00]+[11:30]



In the report to total the row horizontally in the detail portion of
the query, it does not work...


In the footer, I use SUM([00:00]) and that works to add the totals
vertically.


Any help would be appreciated...