Generate Query from Checkbox Selection (1 Viewer)

[OzoneLair]

Hello,

I'm somewhat new to MS Access and have the 2010 version trying to figure out a small detail and was hoping to find some help on this site. I would like to create a query by using a multiple selection of check boxes.

For instance, lets just say I have a table/query with a list of names and three sizes associated, such as "Big", "Medium", and "Small".. I would like to create a checklist on a form where I can click 1, 2, or even all 3 options and return a query that has the records of the options that I've checked on the check boxes.

Can anyone help me? I've been trying to figure this out for a few hours now and think that I could be over thinking things.

Any help would be greatly appreciated.

Thanks,
Ozone

 

[John Big Booty]

Welcome to the forum.

You can use something along the lines of the following in your query Criteria;

[URL="http://www.techonthenet.com/access/functions/advanced/iif.php"]Iif[/URL](Forms!YourFormName!YourOptionGroup = 1, "Big", Iif(Forms!YourFormName!YourOptionGroup = 2, "Medium", "Small"))

Another way, given that you only have the three options, would be to create three separate queries and simply run the one appropriate to the Option Group selection.

 

[OzoneLair]

Thank you for the fast reply.

The truth is that I will be making this much more of a complex search, this is only a dummy database and if I can figure out how to add the functionality I want, I can copy it into the larger, more complex database I am creating.

I would like to have it so that I can also select multiple options, instead of one or the other. I wish I understood VBA a little better, but for now, just looking for some insight into how I can go about this.

Thanks,
Ozone

 

[John Big Booty]

The sample here may be of interest to you.

 

[OzoneLair]

This is what I got so far - I feel like I am closer, but it's not doing what I want it to do.. What am I doing wrong?

FYI - I have 3 checkboxes (Oxide, Hydronium, and Lithium) and 1 button (Enter) to execute the event which opens a report (rptChemicals), hopefully with the filters applied to it via the 3 selected or unselected checkboxes..

____________________________________________________

Option Compare Database

Private Sub Enter_Click()

Dim stDocName As String
Dim strSQLWhere As String

strSQLWhere = ""

If Forms!Checkboxes![Oxide] = -1 Then strSQLWhere = strSQLWhere & "'Oxide'"

If Forms!Checkboxes![Hydronium] = -1 Then strSQLWhere = strSQLWhere & "'Hydronium'"

If Forms!Checkboxes![Lithium] = -1 Then strSQLWhere = strSQLWhere & "'Lithium'"

If Me.Oxide.Value = False Then
If strSQLWhere = "" Then strSQLWhere = strSQLWhere & "'Oxide'"
Else
strSQLWhere = strSQLWhere & " and " & "'Oxide'"
End If

If Me.Hydronium.Value = False Then
If strSQLWhere = "" Then strSQLWhere = strSQLWhere & "'Hydronium'"
Else
strSQLWhere = strSQLWhere & " and " & "'Hydronium'"
End If

If Me.Lithium.Value = False Then
If strSQLWhere = "" Then strSQLWhere = strSQLWhere & "'Lithium'"
Else
strSQLWhere = strSQLWhere & " and " & "'Lithium'"
End If

stDocName = "rptChemicals"
DoCmd.OpenReport stDocName, acPreview, , "wc =" & strSQLWhere
Exit_Enter_Click:
Exit Sub

End Sub

 

[John Big Booty]

I would strongly recommend that you have a look at the sample I linked to in my previous post, specifically the Query that is driving the List Box.