Query to read checkbox

[LB79]

Hello,

I have a form which contains a number of ChkBox’s.
I want to create a query that filters based on those check boxes.
For example, each checkbox represents a country. If the country is checked, then the query will pick it up and filter the data. If the country is not checked then the query excludes it.

Is there a way to do this?

Thanks for any advice

 

[honda882000]

I'm not 100% sure if this would work, but have you tried creating a dynamic string and use it in your In() within the SQL in VBA?

Example:

Sub country_qry()

    Dim in_str As String
    Dim sql As String
    
    'Initiate in_str with some country which does not exist, just so for other contries, we can use comma:
    in_str = "'ABCDE'"
    
    'Check box for USA:
    If Me.ck_usa Then
        in_str = in_str & ",'USA'"
    End If
    
    'Check box for UK
    If Me.ck_uk Then
        in_str = in_str & ",'UK'"
    End If
    
    '...
    
    'Use in_str in an In():
    sql = "SELECT * FROM country_table " & _
        "WHERE country IN(" & in_str & ");" 'in_str is concatenated into SQL
    
    'Run query:
    DoCmd.OpenQuery (sql)
    
End Sub

 

[LB79]

Thanks - I see what you are getting at here, but how would I represent this in the query?

 

[honda882000]

You would use it in your In() statement. Just as you would say IN('USA', 'UK', 'BRAZIL') to include these countries, you can essentially do the same thing here, but by embedding and running the dynamic string into SQL in VBA (last portion in code above).

 

[Rich]

Are you saying that you have numerous checkboxes for many countries?

 

[LB79]

Hi again - Sorry but im not really getting what you mean.

In the code "DoCmd.OpenQuery (sql)" am I to alter this to refer to my qry? As in "DoCmd.OpenQuery ("MyQuery")", then refer to the (sql) in that statement?

Thanks for all your advice on this...

 

[LB79]

And yes I have numerous checkboxes for many countries

 

[honda882000]

I don't really see a way of using an Access Query to do this. In the example I gave you, you would be taking the SQL code from your query and making it dynamic in VBA. If you open existing query in design mode and switch to SQL mode, you should be able to copy this and paste onto VBA (as in the example), then adding the additional WHERE contries IN(...) condition.

 

[Rich]

Then you're actually using a spreadsheet method, a simple lookup table and a combo box entry method is much easier to maintain and the correct method for a relational db

 

[LB79]

Honda - I will look into placing the sql code into the vba (this may take me a while to do).

Rich - Thanks also for your suggestion. Could you expand on this some more? Bye lookup table do you mean a select query? Also what is a combobox entry method?

Thanks guys

 

[Rich]

You're actually trying to re-create a spreadsheet in access, I'll try and knock up a quick example but it'll be later this evening

 

[LB79]

Thanks - Ill keep an eye out