Expression to Output Number as a Number

[Solar.Paul]

I've created several expressions in a query to test for values in a field (one column for each value for use in a report).

The expressions output the value 1 where the test is true. I planned to sum them to establish how many times the value is true but this isn't working. I am only offered a Count and this returns the number of records. Other fields (numeric) are offering me a sum at the total line and are working as expected.

I assume the expressions are outputting the value one as text rather than as a number but I can't work out how to change this. Can anyone help please?

 

[pbaldy]

Try wrapping your expression in CInt() to convert the output to an integer. Make sure your expression either returns Null or 0 when false.

 

[Solar.Paul]

Thanks Paul

Your suggestion is working a treat. I can now add my count but it is returning either a zero(0) or #error rather than a null which would be much easier to read in a report. I'm having trouble getting it to return a blank if not one of the tested values. I would appreciate any suggestions as to how to show a null. Here is my expression as it currently stands:

Expr1: CInt(IIf([tblRaceYacht]![RESULT_TYPE_ID]=1,1,""))
returns #error

Expr1: CInt(IIf([tblRaceYacht]![RESULT_TYPE_ID]=1,1,IsError(Null)))
returns 0

Expr1: CInt(IIf([tblRaceYacht]![RESULT_TYPE_ID]=1,1,IsError("")))
returns 0

Hoping you can help me
Solar Paul

 

[pbaldy]

I would return 0 and use the Format property of the textbox to hide 0's on the report.

 

[Solar.Paul]

Thanks Paul

Sounds like good advice. You've helped me out with this

 

[pbaldy]

Happy to help!