Turning ticks into numerical values?

[Bloodrayne]

Hello all - third post in less than two days, I'm on a roll!

I was wondering if there's a way to get a numerical value out of a Yes/No field (say Yes = 1, No = 0) - for the purpose of reports (and more importantly the totals)

Cheers

 

[KenHigg]

You can evaluate it at runtime with something like the following in the query:

MyNewFld: iif([YesNoFld] = "Yes", 1, 0)

Would this work?
kh

 

[Rich]

=Sum(Abs(MyField))
=Sum(Iif(MyField,1,0))
=Sum(Iif(MyField=False,1,0))

 

[Bloodrayne]

Thanks for the answers...

But... umm... where do I put the suggested code?

 

[KenHigg]

You can put the code I posted in the underlying query, or you can put Rich's in the report footer.

kh

 

[Bloodrayne]

The criteria in the query?

I've added the below code:

"MyNewFld: iif[Flight_requested]"=("Yes",1,0)

Which doesn't work! Should I have altered the MyNewFld part?

I also get an error about the syntax (comma), could this be due to my other criteria?

 

[KenHigg]

Like this...

 

[Bloodrayne]

Ah - can't try it straight away, give me some time and I'll say if I've got it to work yet...

Here's hoping!

 

[Bloodrayne]

Decided to go for the Report Footer route, but am struggling with that too (darn it)

=Sum(Abs([Flight_Booked]))=Sum(IIf([Flight_Booked],1,0))=Sum(IIf([Flight_Booked]=False,1,0))

Which just results in an error (the field name is Flight Booked)

 

[Brianwarnock]

Hi

You only need =Sum(Abs([Flight_Booked]))

Assuming a tick in the box for booked flights


=Sum(IIf([Flight_Booked],1,0)) will also sum all ticked boxes
=Sum(IIf([Flight_Booked]=False,1,0)) will sum all non ticked boxes

Brian

 

[Bloodrayne]

Thanks - the report no longer says error, but now comes up with a zero as the value? No matter which bit of code I use...

Is there anything I should have selected that I could've missed?

 

[Brianwarnock]

Nothing obvious, are you sure you've selected any data, sorry about that

Can you post your db?

 

[Bloodrayne]

I deserved that

The question you should've asked is

"Did you type the field name in properly, you silly silly person?"

Doh...

Works now!

 

[Brianwarnock]

Don't worry about it, we've all been there, done that. Syntax errors are the most common and a b****r to find as we all read what we think we've typed.

Brian