Check box Crosstab Query

[douglasjoh]

Hello all,

Looking for some help/advice regarding the database I am currently trying to build.

In the crosstab query that I have created I am trying to filter this through the use of check boxes on a form.

I have tried to do this by entering the following iif statement - IIf([Forms]![MPS]![Check18]=True,0,1) in the criteria row of the stage column in my Crosstab query.

However, when trying to run I am presented with the following error message "The Microsoft Access database engine does not recognise '[Forms]![MPS]![Check18]' as a valid field name or expression".

I believe this has something to do with the parameters, however, I am unsure how to set them.

Any help would be greatly appreciated.

 

[stumac]

Crosstab queries require you to declare the parameter...

If you set up the parameter first from the Design tab on the Ribbon you should be able to use the parameter in the criteria expression.

 

[xenou]

I'm not 100% sure - might work without explicit declaration - but either way you need to have the form open. A common mistake is to run such a query with the form not open. Otherwise check the field names.

Also since the form value is a check box already (true or false) you could forgo the IIF() formula.

Also, since you mention check boxes (plural) make sure you have correctly identified the problem field.

Note that since you are saying this is a criteria, you need to be comparing it to something that is 0 or 1 (which is a little strange to me, since MSAccess stores true and false as -1 and 0 ... another possible source of errors).