Determine which fields are displayed in a query based on form checkboxes

[GavinBlackburn]

Hi,



I'm not sure which forum to post this in as it's about both queries and forms.

I have a form that I use to determine the Criteria to search my database. Currently the showallfields option is selected in the query. What I would like to do is have a checkbox for each field and allow the user to determine which fields they want to see.

I've tried this:

IIf([Forms]![SearchF]![ChkBoxMet]=0,Like "*" & [Forms]![SearchF]![Met] & "*",ColumnHidden[Query]![SearchQ]![Metabolite])

but it doesn't work. Is there a better way to do this? I'm happy to do a bit a code writing but I may end up asking a lot more questions!

Thanks,

Gavin.

[jgelpi16]

I've tried this:

IIf([Forms]![SearchF]![ChkBoxMet]=0,Like "*" & [Forms]![SearchF]![Met] & "*",ColumnHidden[Query]![SearchQ]![Metabolite])

Where have you put this bit? Is the user viewing the query? Why are they viewing a query vs. a sub-form?

[orange]

I'm not sure what you intended that iif statement to do. I suggest you take a look at this tutorial by Martin Green on Building Queries on the Fly. I'm sure it will give you some valuable hints.

http://www.fontstuff.com/access/acctut17.htm

Good luck.

[GavinBlackburn]

Where have you put this bit? Is the user viewing the query? Why are they viewing a query vs. a sub-form?

I put that bit in the Criteria section of the Query. I'm new to access and allowing them to view the query seemed the easiest way to display the data that was searched for.

[GavinBlackburn]

I'm not sure what you intended that iif statement to do. I suggest you take a look at this tutorial by Martin Green on Building Queries on the Fly. I'm sure it will give you some valuable hints.

http://www.fontstuff.com/access/acctut17.htm

Good luck.

Thanks for the link, I'll take a look but the database is quite simple so there aer not many different ways people can query it. The other issue is that it contains 40,000+ records so dropdown boxes aren't very useful for a lot of the fields.

All i wanted the code to do was display a particular field if a particular check box was checked. It sounds straight-forward but I can't find a striaght-forward way to do it...

Thanks,

Gavin.