Absolute value of a checkbox in a query

  • Thread starter Thread starter MarkD
  • Start date Start date
M

MarkD

Using Access 2000

I have a query based on a table with a bunch of check
boxes. I want to sum the checkboxes based on a field then
transfer that to an Excel sheet. However, Access uses a -1
for a "yes" value instead of a 1. I know I can sum on the
absolute value of each field, but that requires me to
modify each field in the query. Is there a simpler way to
do this?

On an aside, what is the reason Microsoft chose to use
this instead of plain ol' 1?

Thanks,
-Mark
 
Thanks Tina...

Yeah, I know I can do that (or variants thereof), but I
don't want to change each of the 30 checkbox fields. I was
hoping there'd be a global way to change things. I'm just
lazy, I guess.

-Mark


-----Original Message-----
try

Sum(IIf([CheckboxName] = 0, 0, 1))

no answer for the second question, i'm afraid. :)

hth


Using Access 2000

I have a query based on a table with a bunch of check
boxes. I want to sum the checkboxes based on a field then
transfer that to an Excel sheet. However, Access uses a -1
for a "yes" value instead of a 1. I know I can sum on the
absolute value of each field, but that requires me to
modify each field in the query. Is there a simpler way to
do this?

On an aside, what is the reason Microsoft chose to use
this instead of plain ol' 1?

Thanks,
-Mark
Click to expand...


.
Click to expand...
 
well, i can't think of anything that wouldn't require you to enumerate the
fields in some fashion - even if you use a public function, you'd still have
to substitute the function call for each field in your query.
there may be a more elegant way, but it's beyond me. maybe somebody else
will post a solution, and we'll both learn something. :)


MarkD said:
Thanks Tina...

Yeah, I know I can do that (or variants thereof), but I
don't want to change each of the 30 checkbox fields. I was
hoping there'd be a global way to change things. I'm just
lazy, I guess.

-Mark


-----Original Message-----
try

Sum(IIf([CheckboxName] = 0, 0, 1))

no answer for the second question, i'm afraid. :)

hth


Using Access 2000

I have a query based on a table with a bunch of check
boxes. I want to sum the checkboxes based on a field then
transfer that to an Excel sheet. However, Access uses a -1
for a "yes" value instead of a 1. I know I can sum on the
absolute value of each field, but that requires me to
modify each field in the query. Is there a simpler way to
do this?

On an aside, what is the reason Microsoft chose to use
this instead of plain ol' 1?

Thanks,
-Mark
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks again Tina,

Hopefully there is a quick-n-ditry solution, but it looks
like I gotta change all fields manually. And I so wanted
my Friday before Memorial Day to be a lazy day at work!

-Mark
-----Original Message-----
well, i can't think of anything that wouldn't require you to enumerate the
fields in some fashion - even if you use a public function, you'd still have
to substitute the function call for each field in your query.
there may be a more elegant way, but it's beyond me. maybe somebody else
will post a solution, and we'll both learn something. :)


Thanks Tina...

Yeah, I know I can do that (or variants thereof), but I
don't want to change each of the 30 checkbox fields. I was
hoping there'd be a global way to change things. I'm just
lazy, I guess.

-Mark


-----Original Message-----
try

Sum(IIf([CheckboxName] = 0, 0, 1))

no answer for the second question, i'm afraid. :)

hth


Using Access 2000

I have a query based on a table with a bunch of check
boxes. I want to sum the checkboxes based on a field then
transfer that to an Excel sheet. However, Access uses a -1
for a "yes" value instead of a 1. I know I can sum on the
absolute value of each field, but that requires me to
modify each field in the query. Is there a simpler
Click to expand...
way
to
do this?

On an aside, what is the reason Microsoft chose to use
this instead of plain ol' 1?

Thanks,
-Mark



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top