Check box Crosstab Query

douglasjoh

New Member
Joined
Nov 17, 2016
Messages
15
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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).
 
Last edited:

Forum statistics

Threads
1,136,434
Messages
5,675,838
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top