where clause based on check box values

[mlpearce2]

I have a form with a combo box and some check boxes. The user can pick a something from the combo box and also include one or more of the check boxes.

I would like to use the values of the combo box and the check box(es) in a where clause in a select query.

Does anyone know how to do this?

Thanks

[Imdabaum]

Depending on what your query is doing, you would modify the after update event that fires on all of these controls. It would be helpful if you told us what the Select query was for. If it's the recordsource for the form, then a filter statement might be more effective than rewriting the SQL statement. But if it's for a recordsource for a datagrid that will be loaded after some other event that verifies the status of the selections on your combobox and checkboxes, then you may have some other solution.

[mlpearce2]

basically the table contains patient data. the table contains multiple hospitals with accounts in various statuses in the medical assistance application process.

the form i have allows the user to choose a hospital (combo box) and one or many status codes (check box) for accounts that are in the table. Status codes are 3 letters (RBD, RNQ, RNC, RCL...just to name a few)

This is the basic idea:

the data in the table needs to be separated into these groups of charges ($) based on the criteria the user selects:

0<x<10,000
10,000<x<25,000
25,000<x<50,000
50,000<x<100,000
100,000<x

so i am thinking i need to build something like this:

select * from patient_data
where
facility = 'cmbFacility.value' and charges > 0 and charges <= 10000 and (status = 'chkStatus1.value' or status = 'chkStatus2.value'....status = 'chkStatus(n).value')

just not sure how to build that on the fly.

hope this helps

[Imdabaum]

I would suggest you run a select case statement. I right before you execute your SQL statement run this and add the sqlClause to the end of your original SQL statement.

Dim sqlClause as String
'index = Which column of the combobox holds the range displayed.
SELECT Case Me.CmbRanges.Column(index)
Case "0<x<10,000"
sqlClause = "charges BETWEEN 0 AND 10000"
Case "10,000<x<25,000"
sqlClause = "charges BETWEEN 10,000 AND 25,000"
Case "25,000<x<50,000"
sqlClause = "charges BETWEEN 25,000 AND 50,000"
Case "50,000<x<100,000"
sqlClause = "charges BETWEEN 50,000 AND 100,000"
End Select

SQL = SELECT * FROM Patient_data
WHERE " & sqlClause

[mlpearce2]

I would suggest you run a select case statement. I right before you execute your SQL statement run this and add the sqlClause to the end of your original SQL statement.

Dim sqlClause as String
'index = Which column of the combobox holds the range displayed.
SELECT Case Me.CmbRanges.Column(index)
Case "0<x<10,000"
sqlClause = "charges BETWEEN 0 AND 10000"
Case "10,000<x<25,000"
sqlClause = "charges BETWEEN 10,000 AND 25,000"
Case "25,000<x<50,000"
sqlClause = "charges BETWEEN 25,000 AND 50,000"
Case "50,000<x<100,000"
sqlClause = "charges BETWEEN 50,000 AND 100,000"
End Select

SQL = SELECT * FROM Patient_data
WHERE " & sqlClause


that much makes sense. but...

what about the statuses they choose?
or if patient_data contains multiple facilities?

building the query is pretty straight forward but how would i execute the select statement from vba after it is built?

[Imdabaum]

That question is up to you. When do YOU want to run the SQL statement? Is there a button that fires the event, do you want it to automatically fire after a control is changed?

I would suggest putting the status checkbox in a group box. Then you just append that clause at the end as well.


SQL = SELECT * FROM Patient_data
WHERE " & sqlClause

'if the status checkbox is required
SQL = SQL & " AND status = " & groupBox1.value
'if the status checkbox is not required
If Not IsNull(groupBox1.value) Then
SQL = SQL & " AND status = " & groupBox1.value
End if
SQL = SQL & ";"

[mlpearce2]

That question is up to you. When do YOU want to run the SQL statement? Is there a button that fires the event, do you want it to automatically fire after a control is changed?

I would suggest putting the status checkbox in a group box. Then you just append that clause at the end as well.



The option group is a smart idea. Is there a way to make the option group multi select. The reason is because if an account is returned, it could be returned for multiple reasons and each reason has a different status code.

The account could be returned because the person doesn't qualify or they can't be located, or they aren't cooperating with us throughout the process, etc.

any thoughts on that?

[orange]

Further to imdabaum's suggestions, here is a link to a sample with tutorial for Dynamic reports. The whole concept is to use a form with controls and to use the selections to create SQL

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

Hope it's useful to you