UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Output Of Crosstab Query Not Being Displayed As Number, Any Version    
 
   
CadetITGuy
post Sep 17 2019, 01:25 PM
Post#1



Posts: 11
Joined: 22-July 12



I am using a cross tab query to get data on attendance. When I use the output (expression) in a report I do not get the data generated by the query.

Field is AMS: Abs(Sum(IIf([Attendance]="A" And [[censored]]="M",1,0) And ([cadet/staff]="staff") And ([warrant number] Is Not Null)))
Total is Expression

In the report the value is always zero unless I add zero.
Control Source = =Sum([AMS]+0)

This works for each individual output however when I add up several the output is always zero
=Sum([ams]+[amci]+[amcv])

Is there a way to have AMS be a number as a control source on the report
Go to the top of the page
 
theDBguy
post Sep 17 2019, 01:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,397
Joined: 19-June 07
From: SunnySandyEggo


Hi. Can you post a sample copy of your db or post some screenshots? How can you tell it's not a number already?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
CadetITGuy
post Sep 17 2019, 02:00 PM
Post#3



Posts: 11
Joined: 22-July 12



Here is a screen shot of my report. Not sure if this is what you want
Attached File(s)
Attached File  Capture_NL203.JPG ( 34.5K )Number of downloads: 7
 
Go to the top of the page
 
MadPiet
post Sep 17 2019, 02:12 PM
Post#4



Posts: 3,332
Joined: 27-February 09



Nope. We wanted a copy of the database... with enough data in it to understand/solve the problem.
Go to the top of the page
 
June7
post Sep 17 2019, 03:36 PM
Post#5



Posts: 877
Joined: 25-January 16



The expression should return True or False (-1 or 0) and you are summing this result. The first condition IIf([Attendance]="A" And [[censored]]="M",1,0) returns 1 or 0.

So did some testing in immediate window:

?1 And True And True

returns 1

Other combinations return 0 (except ?-1 And True And True - returns -1)

Sum() should work and Abs() not needed.

This post has been edited by June7: Sep 17 2019, 04:00 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
CadetITGuy
post Sep 19 2019, 04:21 PM
Post#6



Posts: 11
Joined: 22-July 12



Thanks. I will give it a try
Go to the top of the page
 
June7
post Sep 19 2019, 04:35 PM
Post#7



Posts: 877
Joined: 25-January 16



My point is, what you have should actually be working so I cannot explain why it is not. Also suggest you provide db for analysis.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th October 2019 - 12:13 PM