Checkbox as query criteria

[lisagippe]

I need help with checkboxes and based on everything I have searched I have not found out if this is actually possible.

I have a simple form with 15 checkboxes, 2 text boxes for dates and a button to run the query. Each checkbox represents a different skill listed in a data table, ie DPU would be a value of 1, DRQ would be 2 and so on in the datatable. I would like to be able to check as many or as few options as I can and make those the criteria the query uses.

DPU 'checked'
DRQ 'checked'
DTR 'checked'
DGN
IPU
IRQ 'checked'

My question being, how do I assign a value to a checkbox so that in my query I can say [Forms]![Form1]![checkbox1] And [Forms]![Forms1]![checkbox2]etc so that it will only query based on the values of the checkbox and the dates in the textboxes?

 

[fneily]

Could you post the structures of your tables? Tablename, fields and designate the Primary Key for each table, like
tblClient
ClientID PK
SkillID Fk
Firstname
etc.

It seems you want to turn checkboxes(true/false) into another value. You can use the Dlookup function. But something just doesn't feel right to me. Basically, why does a skill need it's own number?

 

[MajP]

Each checkbox represents a different skill listed in a data table, ie DPU would be a value of 1, DRQ would be 2 and so on in the datatable.

I am first going to tackle the checkboxes. The way I read above is that you have a field called something like "skill" and it has values in it like "1,2,3...15"
where 1 represents "DPU" and 2 represents "DRQ" ...

This is a little tricky if this is correct, because you have checkboxes that have values of true and false but you want to return some integer value. This is how I would handle the checks.

build a function

Public Function isChecked(skillValue As String) As Boolean
  Const frmName = "frmWhere"
  Const fldName = "tbl1.Skill"
  Const fldName2 = "tbl1.someDate"
  Dim frm As Access.Form
  If IsLoaded(frmName) Then
     Set frm = Forms(frmName)
     Select Case skillValue
       Case 1
         If frm.Controls("chkDPU") Then
           isChecked = True
         End If
       Case 2
         If frm.Controls("chkDRQ") Then
           isChecked = True
         End If
       Case 3
         If frm.Controls("chkDTR") Then
           isChecked = True
         End If
       Case 15
      End Select
    End If
  End Function

now you can use this function in your query passing the skill value to the function and seeing if the skill is selected on the form

SELECT tblWhere.ID, tblWhere.skill, isChecked([skill]) AS isChecked
FROM tblWhere
WHERE isChecked([skill]))=True

If this works for the checks then we can talk about the next step.

 

[MajP]

disregard the following:
Const fldName = "tbl1.Skill"
Const fldName2 = "tbl1.someDate"
that was leftover.