Formula... (1 Viewer)

[mariaw]

Hi

I have a form that people fill in where they can choose from 4 boxes (and they can tick as many as they want - it is a yes/no box)

I have tried to write a formula for a queryto show where none of the boxes have been ticked.....this doesn't work:

=(IIf(IsNull([Q3(i)]) And iif(IsNull([Q3(ii)]) And iif (IsNull([Q3(iii)]) And iif(IsNull([Q3(iv)]),"1","0"))

???? what should I write??

Maria

 

[Matt Greatorex]

If they're checkboxes, 0 or False would indicate not checked, as opposed to Null (-1 or True would indicate checked).

Do you have the boxes' Tripe State property set to No? If not, you may want to do so, unless there's a particular reason why you want there to be True/False/Other as available states. It just avoids confusion, as all can then only be either True or False.

 

[Jon K]

Since you mentioned a query, I presume the form is a bound form. By default, a Yes/No field in a table can have only two values: -1 when ticked, 0 when not ticked. So

= -([Q3(i)]+[Q3(ii)]+[Q3(iii)]+[Q3(iv)]) should return the number of check boxes ticked, ie 0, 1, 2, 3, or 4.


If it is an unbound form, you can use Nz() to convert Nulls to zeros in the calculation like this:-

= -(NZ([Q3(i)])+Nz([Q3(ii)])+Nz([Q3(iii)])+Nz([Q3(iv)]))
.

 

[mariaw]

Thanks guys- got it working how I want it now - had a revelation at 3am in the morning...

Cheers

Maria