Checking checkboxes to produce a SELECT statement

Posted on 2005-04-26
Last Modified: 2010-05-02
I have a list of checkboxes each one refering to a field in a query in an Access database,
I need to make it so that the user can build a query based on the boxes that they check. The only way I can think to do this is to write out about a million if statements to cover all possibilities. i.e.

if(chk1.value = vbchecked and chk2.value = vbunchecked and etc.. )then
do query

there must be an easier way of doing this, I've got 9 check boxes, so doing as above would take a long time.

I would appreciate any suggestions

Question by:liseperu
    LVL 4

    Accepted Solution

    The way I did this was to build the quiery up bit by bit, e.g.

    dim strQuery as string
    dim inQuery as boolean

    strQuery = "SELECT * FROM table"

    if chk1.value then
      strQuery = strQuery & " WHERE (field=true"
      inQuery = TRUE
    end if

    if chk2.value then
      if inQuery then
        strQuery = strQuery & " AND field2=true"
        strQuery = strQuery & " WHERE (field2=true"
        inQuery = TRUE
      end if
    end if

    ... Continue for other check boxes...

    if inQuery then
      strQuery = strQuery & ");"
    end if

    Hope this is what you meant, cheers.


    Author Comment

    thats quite clever, thanks, that'll save me some time!!
    LVL 4

    Expert Comment

    No problem at all :)
    LVL 4

    Expert Comment

    Oh forgot to mention sorry, don't forget that last if statement, basically it closes the brackets off if you using the WHERE clause in the statement. :) Cheers


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now