Report Sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I previously used the following to sum query selections from option groups that were converted from numbers to text and it worked fine.

Now that I have reset the option groups to work as they are designed to(numbers), I get a data type mismatch in criteria expression for calculated text boxes. Type of Service Option Group selections are either Physical or Phone.

=Sum(IIf([Type of Service]="Physical",1,0)) worked for the text conversion.

After putting option group back to as designed I tried
=Sum(IIf([Type of Service]="1",1,0)) as Physical is "1" and Phone is "2". I still get the mismatch problem.

How do I write the expression to sum correctly?
 
If [Type of Service] is now a Number type field, don't use quotes areound
its value:
=Sum(IIf([Type of Service]=1,1,0)

You could also try:
=-Sum(([Type of Service] = 1))
The expression:
([Type of Service] = 1)
is True when the field has the value 1, False for any other value (and Null
if the field has no value). Access uses -1 for True, and 0 for False, so
summing the field gives the negative of the count.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bdehning said:
I previously used the following to sum query selections from option groups
Click to expand...
that were converted from numbers to text and it worked fine.
Now that I have reset the option groups to work as they are designed
Click to expand...
to(numbers), I get a data type mismatch in criteria expression for
calculated text boxes. Type of Service Option Group selections are either
Physical or Phone.
=Sum(IIf([Type of Service]="Physical",1,0)) worked for the text conversion.

After putting option group back to as designed I tried
=Sum(IIf([Type of Service]="1",1,0)) as Physical is "1" and Phone is "2".
Click to expand...
I still get the mismatch problem.
 
bdehning said:
I previously used the following to sum query selections from option groups
Click to expand...
that were converted from numbers to text and it worked fine.
Now that I have reset the option groups to work as they are designed
Click to expand...
to(numbers), I get a data type mismatch in criteria expression for
calculated text boxes. Type of Service Option Group selections are either
Physical or Phone.
=Sum(IIf([Type of Service]="Physical",1,0)) worked for the text conversion.

After putting option group back to as designed I tried
=Sum(IIf([Type of Service]="1",1,0)) as Physical is "1" and Phone is "2".
Click to expand...
I still get the mismatch problem.
 
Try removing the quotes around the number

=Sum(IIf([Type of Service]=1,1,0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sflym said:
bdehning said:
I previously used the following to sum query selections from option
Click to expand...
groups
that were converted from numbers to text and it worked fine.
Now that I have reset the option groups to work as they are designed
Click to expand...
to(numbers), I get a data type mismatch in criteria expression for
calculated text boxes. Type of Service Option Group selections are either
Physical or Phone.
=Sum(IIf([Type of Service]="Physical",1,0)) worked for the text conversion.

After putting option group back to as designed I tried
=Sum(IIf([Type of Service]="1",1,0)) as Physical is "1" and Phone is
Click to expand...
"2".
I still get the mismatch problem.
How do I write the expression to sum correctly?
Click to expand...
Click to expand...
 
Back
Top