Query returns negative values

[Toolpusher]

Hi

I have a query that does a count of ID,s created during certain date periods and also returns the number of yes/no (tick boxes that are ticked) for a field by just using the term sum to count them up.

The query works perfect but returns the value of the tick boxes that are ticked as a -number. I am using this query in a report and it looks fine except I want it to report it as a positive number 5 e.g. instead of -5

Cant see how to do this any help appreciated

TP

 

[plog]

Multiply by -1.

Or Use the ABS() function.

Or Use a conditional statement that turns the TRUE values into 1 and FALSE into 0.

 

[Toolpusher]

Hi

Thanks for the info but I am struggling to see how I can multiply the result by -1 in the query as that seems the easiest way of achieving it

TP

 

[plog]

Whats your SQL?

 

[gemma-the-husky]

a "true" value (ie a checked tick box) is stored as -1
a "false" value is stored as 0

hence a - total indicates the number of "trues"

just do dcount("*",sometable","somefield = " & true) instead

 

[Toolpusher]

Solved this the easy way and maybe (the unprofessional way) by inserting #,###;#,###;0 in the format for the text box in report which now hides the minus sign.

Thanks