Using checkboxes to select query parameters (1 Viewer)

maff811

Registered User.
Local time
Today, 18:31
Joined
May 1, 2014
Messages
45
Hi all,

I have two tables, let's say A and B. Table A contains a number of Yes/No fields while Table B has text fields that contain various statuses. Tables A and B are linked via the status field.

I also have a form on which I have multiple checkboxes so that a user can select a which Yes/No fields they can filter in order to run a query and generate a report. This works fine for fields in Table A which have the Yes/No fields. However, I am wondering how I would list the text field values as checkboxes so that users can select which values to include in the query, but cannot think how I can overcome the text and Yes/No difference.

I am using the QBE Expression Builder as follows:

Expr1: [FieldinTable]=[Forms]![Navigation Form]![NavigationSubform].[Form]![FieldonForm]=True Or [Forms]![Navigation Form]![NavigationSubform].[Form]![FieldonForm]=False

This works perfectly for the Yes/No fields in Table A, But when I list the statuses as checkboxes, I am not sure how I convert the 'yes' in the checkbox to the relevant status from Table B.

Hopefully this makes sense and any help would be most appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
Maybe you can use an IIf() expression to convert the text into yes/no. But, multiple yes/no fields is usually a sign of a bad table structure.
 

maff811

Registered User.
Local time
Today, 18:31
Joined
May 1, 2014
Messages
45
Thanks theDBguy. The Yes/No fields are used for tags so that users can categorise text for further thematic analysis.

I did try an IIF expression, but maybe I didn't get it quite right. I'll try again...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
Thanks theDBguy. The Yes/No fields are used for tags so that users can categorise text for further thematic analysis.

I did try an IIF expression, but maybe I didn't get it quite right. I'll try again...
Show us what you've tried...
 

maff811

Registered User.
Local time
Today, 18:31
Joined
May 1, 2014
Messages
45
Initially this:

Expr1: [ResponseStatus]=IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![Implemented]=True,"Implemented","")

As far as I can see, this should convert a checkbox for the status 'Implemented' that is selected to text that says 'Implemented'.

I am then trying to get it to work in combination with others like this:

Expr2: [TypeServiceDelivery]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ServiceDelivery]=True Or [Forms]![Navigation Form]![NavigationSubform].[Form]![ServiceDelivery]=False
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
Initially this:

Expr1: [ResponseStatus]=IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![Implemented]=True,"Implemented","")

As far as I can see, this should convert a checkbox for the status 'Implemented' that is selected to text that says 'Implemented'.

I am then trying to get it to work in combination with others like this:

Expr2: [TypeServiceDelivery]=[Forms]![Navigation Form]![NavigationSubform].[Form]![ServiceDelivery]=True Or [Forms]![Navigation Form]![NavigationSubform].[Form]![ServiceDelivery]=False
That is a bit hard to interpret without seeing your db. I was thinking you merely wanted something like this.
Code:
Terminated: IIf(FieldName="Terminated",True,False)
 

maff811

Registered User.
Local time
Today, 18:31
Joined
May 1, 2014
Messages
45
Thanks theDBguy. The database is attached. If you go to the Navigation Form and then the REport tab, you'll see what I'm trying to do.

For the time being, i have used the combobox to select a status, but it turns out having the ability to select multiple statuses is preferable.
 

Attachments

  • AOP Database.zip
    554.9 KB · Views: 114

theDBguy

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
Thanks theDBguy. The database is attached. If you go to the Navigation Form and then the REport tab, you'll see what I'm trying to do.

For the time being, i have used the combobox to select a status, but it turns out having the ability to select multiple statuses is preferable.
Hi. Thanks for posting a copy of your db. If I understood it correctly, you want to convert your Status Combobox/Dropdown into checkboxes, so users can select more than one status, right?

If so, I would suggest the simpler approach is to convert your Combobox into a Multi-Select Listbox instead.

Hope that helps...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 19, 2002
Messages
42,981
The checkboxes are what we call a repeating group. Access is a relational database, it is not a spreadsheet. Therefore, the correct solution is for there to be a second table and each checkbox would be a row in that table. A third table would be used to define the universe of Suggestions. In a normalized schema, you would actually end up with rows only for true values since the absence of a row can be construed to be false.

The second table would include four columns
SuggestionID (autonumber, PK)
RecommendationNumber (FK, uniqueIDX fld1)
SuggestionType (FK, uniqueIDX fld2)
SuggestionYN

tbl3
SuggestionType (autonumber)
SuggestionName

I don't like the names above because it seems like these checkboxes are actually the recommendations and the table you are calling recommendations should be named something else but I can't guess what this is all about.

With this schema, you would use a multi select listbox that lists the contents of tbl3 and that listbox would be used to create an IN() clause that would be used to select suggestions from tbl2 where SuggestionType In(3, 9, 12, 15) which would be the Suggestion types selected in the listbox.

You need a little code to build the In() clause but the nice thing is that if things change in the future, you can add new items without changing anything else in the application.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:31
Joined
Jul 9, 2003
Messages
16,245
First of all I do like the design of your database, it's very nice-looking. Unfortunately as Pat has pointed out, you are following an Excel type of design, and although this works perfectly OK in MS Access, it's just not the way to go because you will very quickly fall foul of some very difficult challenges. I suggest you read my blog on what I call "Excel in Access" HERE:-


I also provide a tool which will transpose your checkbox Fields into their own separate table, to carry out this "Transposition" follow along my video instructions HERE:-


The YouTube Video's show you how to take advantage of the new structure.

The “Transpose Tool” is HERE:-


The “Transpose Tool” is available for free, just contact me for details of how to get it for free...

You don't really have a choice, you must do this if you want to do any serious reporting on your data. If you don't take these steps now, you will suffer serious consequences!
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,175
it's unclear which is the main Record.
see this fix.
 

Attachments

  • AOP Database.zip
    601.9 KB · Views: 104

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:31
Joined
Jul 9, 2003
Messages
16,245
I've done a quick demo of how to use the Transpose Tool to transpose your Excel Data into something more suitable for MS Access. See this YouTube video here:-

Transpose Boolean 1a - Nifty Access​

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 19, 2002
Messages
42,981
I didn't look at the form. Since the checkboxes are grouped, you can group the items when you normalize the table by adding group to the third table where the checkboxes are defined.
 

maff811

Registered User.
Local time
Today, 18:31
Joined
May 1, 2014
Messages
45
Hi everyone, thanks for the great feedback. I'll have to spend some time to read through this and understand it.

@theDBguy - I did play around with your suggestion to use a listbox and got to the point of being able to select multiple statuses. I then started to read up on how to convert the selected values into a string of text in order to use it in a query... not quite there yet.

@Pat Hartman - thanks for that advice. I did seriously consider creating new tables for each checkbox group and link the tables, but felt, at the time, that this would have been a time consuming exercise, as I needed something quick so we could get stuck into data entry.
First of all I do like the design of your database, it's very nice-looking. Unfortunately as Pat has pointed out, you are following an Excel type of design, and although this works perfectly OK in MS Access, it's just not the way to go because you will very quickly fall foul of some very difficult challenges. I suggest you read my blog on what I call "Excel in Access" HERE:-


I also provide a tool which will transpose your checkbox Fields into their own separate table, to carry out this "Transposition" follow along my video instructions HERE:-


The YouTube Video's show you how to take advantage of the new structure.

The “Transpose Tool” is HERE:-


The “Transpose Tool” is available for free, just contact me for details of how to get it for free...

You don't really have a choice, you must do this if you want to do any serious reporting on your data. If you don't take these steps now, you will suffer serious consequences!
Thanks Uncle Gizmo, I'm keen to use the tool you have and give it a shot. Any help is appreciated.
 

maff811

Registered User.
Local time
Today, 18:31
Joined
May 1, 2014
Messages
45
@Pat Hartman and @Uncle Gizmo - I had a go at pulling out the checkboxes from the Recommendations table and put them into their own tables. I separated them into three distinct tables - Change, Jurisdiction and Tags - with each linked to the Jurisdiction table by the primary key.

Does this look any better? I also managed to allow multiple selections... not sure if this will work. But what I need to figure our now is how users select Change, Jurisdiction and Tags fields from within the Recommendation form.
 

Attachments

  • AOP Database.zip
    491.2 KB · Views: 110

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:31
Joined
Jul 9, 2003
Messages
16,245
Thanks Uncle Gizmo, I'm keen to use the tool you have and give it a shot. Any help is appreciated.

Hi Matt, I was waiting for you to respond before I put any more work into this. You can get the transpose tool for free by using the coupon code:- t0cpajo all I ask is that you subscribe to my YouTube channel with the following subscription link...

• Get Uncle Gizmo's Latest MS Access YouTube's HERE:-

In the first video I demonstrated using the tool to extract the boolean Fields from the Excel type table. In the next video I will demonstrate how to extract the repeating values into a lookup table. I will also add an extra field as suggested by Pat to identify the groupings. If I have the time, I will demonstrate how you can integrate this information back into a form using subforms.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 19, 2002
Messages
42,981
You would not create a table for EACH group. You would create ONE table for all groups. Group would be a data field. That allows you to add additional groups if you ever need to without restructuring the application. It also allows you to move items from one group to another if the need to that ever arises.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:31
Joined
Jul 9, 2003
Messages
16,245
In the next video I will demonstrate how to extract the repeating values into a lookup table.

I've done the next video:-

Transpose Boolean 2a - Nifty Access​


I don't have time to do anymore. This 10 minute video took me about an hour and a half to complete with editing etc... I've already pointed you to the necessary information, it's just that you've might have to ferret it out. If you get stuck, I suggest you post a message in this thread.
 

Users who are viewing this thread

Top Bottom