1 report field returning 2 values

[TammyT]

The field was put in to count the records for a certain day that had a field called Unavailable set to true (meaning it was checked - yes/no field). (has to do with QA survey calls)

The query is a crosstab query - here is the SQL if it helps:

PARAMETERS [Forms]![frmQASurveyReports]![StartDateChoice] DateTime, [Forms]![frmQASurveyReports]![EndDateChoice] DateTime;
TRANSFORM Count([QA Survey Table].TicketNo) AS [CountOfTicket#]
SELECT [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].TicketNo, [QA Survey Table].Date
FROM [QA Survey Table]
WHERE ((([QA Survey Table].Date) Between ([Forms]![frmQASurveyReports]![StartDateChoice]) And [Forms]![frmQASurveyReports]![EndDateChoice]))
GROUP BY [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].TicketNo, [QA Survey Table].Date
PIVOT "D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice]) In ("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9","D10","D11","D12","D13","D14","D15","D16","D17","D18","D19","D20","D21","D22","D23","D24","D25","D26","D27","D28","D29","D30");


(FYI - the reason for all of the "D0"/etc column headings is because I need to have the dates displayed horizontally in 1 header, not vertically in their own headers - someone at this site got me to that point!)

Okay, then I needed to get a total, by day for each survey that was marked as unavailable. After LOTS of trial & error, I have discovered that the only way I can get a by-day total is to create a footer for each "D0"/etc column. I did that for 1, to test, & I put in a text box & entered "=Abs(Sum([Unavailable]=-1))"

I only have 1 text box, but the report is displaying 2 numbers! It has an 85 & then below it a 6. 6 is the correct # of Unavailables for that day; the total for the report time period is 91, so the 85 is showing as the difference.

I hope this is clear!! Any ideas as to how to get it to just return the value of the day (6 in this case), & not the other value?

Thanks!

 

[PaulBricker]

You probably need to filter the expression on the DO value(even though it's in the DO footer.
=Sum(IIf([Unavailable]=-1 And SomeField = "D0",1,0))

You may have to adjust the expression some, but that gives you the idea. Or you might try using the
DCount() function.


Paul

 

[TammyT]

I thought about the DCount() Function, but I just couldn't get the expression right!

Any suggestions? The report is based on a query, so I thought that the query would be the best option for the domain part, but it doesn't work right. If I use the table itself I'm concerned that it will look at all of the records instead of just those for the 1 date I'm trying to get.

The query has a "between" date parameter (you can see that in the SQL), & those dates are pulled from what is entered on a form.

Thanks!

+Tammy

 

[PaulBricker]

The query should be acceptable as a source. The syntax is
DCount("[Unavailable]", "queryName", "[Unavailable] = -1")

Try it and post your expression if you can't get it to work and we can look at it.


Paul

 

[TammyT]

I got it after I posted & was so excited about typing in my 62 text boxes (31 for Unavaialable & 31 for Refused, same idea different reason) that I forgot to post.

My mistake was not using the quotes which was causing my errors.

Thanks for all of your help!

+Tammy

 

[PaulBricker]

Glad you got it.

Paul