Omitting Fields from Query Results - Urgent (1 Viewer)

Hi!

I'm a student currently learning how to use access and I have a question about a certain query I'm trying to create that my instructor, unfortunately, does not know how to solve.

I'm creating a query that is linked to a form with toggle buttons relating to fields in a customer table (tblCustomer). The form has toggle buttons on it for each field in tblCustomer (see first image attachment). A button on a form runs a query with all tblCustomer fields but only returns data for the fields that are selected on the form (second and third image attachments show the design view of the query and the query results after pressing the button on the form). As you can see, there are a lot of blank fields which I need to be omitted captions and all in order to create a report.

Do you know how to go about omitting the columns and still allowing the columns to function if I needed?

This is fairly urgent for me as I have an important presentation tomorrow with this database. This aspect is not necessary, but I would really love to really learn and try something new.

Thank you for any and all help!

Jonathan



PS: I realize that I am supposed to post an introduction about myself as I am a new member. Unfortunately I'm running low on time and sleep so I will create this tomorrow. Excited to become a part of this forum.

Have a look at this YouTube https://sellfy.com/p/l24j/ The field list in the “Transpose Tool” is generated automatically in a list box. You can then select the fields from the list box.

If you want a free copy of the “Transpose Tool” so you can see how it's done, then see instructions on using the "Normalization Tool” http://www.niftyaccess.com/normalization-tool/ there are details explaining how to obtain a free copy.

Sent from my SM-G925F using Tapatalk

I believe you can do this by testing each toggle button and build your sql string
Something along the lines of


and so on until

then run the SQl

It could be carried out in a loop if you have decent naming conventions as well, but let's walk before trying to run.

I'd also get rid of any strange characters in the field names like / and ?
You can put them in the captions for the fields.

HTH

About every 3 months someone posts this exact issue. and every time my response is--'You do realize you are recreating a function of Access in Access that already exists.'

You are trying to build a majorly less functional and minorly easier Query Design tool. There's already a simple, intuitive, graphical interface way to build queries in Access.

But if your heart is set on this, it's going to require more than SQL, it's going to require VBA, and a more than rookie level experience with it.

From your form, you are looking at what fields to show, correct?
If true, the all you need to do is set each field you do not wish to show to a width of 0.

The following will tell you more about how controls are sized;
https://social.msdn.microsoft.com/F...085/unit-of-measure-in-access?forum=accessdev

If you do want to try this, I'd suggest working out how you want to move around fields by changing their properties on the report.

MUCH easier than working out how to change the data source for a report and then making sure you get the matching controls....

I happen to agree with plog. Anything you create will be a poor version of QBE. If you have sophisticated users who are capeable of creating their own queries, give them a separate database for their use only. Let them link to the BE tables (preferably in a non-update mode). They can then create their own queries and reports.

If you want to do something to help less experienced users, create a dozen or so specific exports that offer some pre-defined filtering. Add the option to export to Excel. The users can then take the set of basic data and polish up their report using Excel.