Trouble counting or summing field

[debbieg]

I did a search on my problem but I can't get it to work. Here's what I have.

I have a field that stores a 0, 1, 2, or 3. When I use this field I use a combo box with text describing the 4 options. Let's say the options would be:

1 = Red
2 = Green
3 = Blue
0 = no response

My client wants a report that list the people's names and their choice like so:

Name Red Green Blue
Joe 1
Cindy 1
Larry 1
Sally 1
etc.

Total 2 1 1


For the 1's in the report, for example, under Red I put an unbound field called Red with =IIf([Field]=1,1,0). They are resolving just fine.

Then in the Report Footer, I put an unbound field with =sum([Red]). But I get a Parameter dialog box asking for a value for Red. I get the same results if I use =count([Red]).

Does anyone know what I'm doing wrong?

Thanks in advance for any help.
Debbie

 

[Steve101]

Try

=sum(IIf([Field]=1,1,0))

as the controlsource for the total reds; similar expressions for the other colours. that should resolve OK.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[debbieg]

Steve,

Thank you for your quick reply.

I copy and pasted your answer into my control (changing the field name, of course), and I still get a parameter dialog box for my field.

What am I doing wrong?

Debbie

 

[Steve101]

Debbie,

I presume that you're putting the =sum(...) formula in the controlsource property for controls in the ReportFooter section of the report (or some other sorting and grouping field.

An alternative approach that you can try is to alter the recordsource of the report, to include the expresssions as separate columns in the query; eg.

SELECT Name, 
       IIf([Field]=1,1,0) AS Red,
       IIf([Field]=2,1,0) AS Green,
       IIf([Field]=3,1,0) AS Blue, 
       IIf([Field]=0,1,0) AS None 
       FROM tblYourTable

This way you can simply set up and bind the respective 'color' columns on your report, and use simple =sum(red), =sum(Green) etc to get the totals in a group / report footer section of the report.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[debbieg]

Now I'm getting error:

Data type mismatch in criteria expression.

I've been working on this for 3 hours and I'm getting very frustrated.

 

[Steve101]

Can you set up a stand alone query, similar to the one I have above and get it to work?

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[debbieg]

I cried too soon ...

this is what finally worked:

=Sum(Abs([Red]="1")).

Thanks for much for your time. I love Tek-Tips!