Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Question Unanswered: Multiple Criteria in a Query

    Currently, I have created a Simple Query with the following Fields:-
    Property ID
    Swimming Pool
    Gymnasium
    Sauna

    I have a Form (Report 2 Form) where the customer should click one or more CheckBoxes to choose which features of a property are important to him. Eg. Does the property have a swimming pool, gymnasium, sauna etc (I actually have about 30 different criteria).

    Lets say the User clicks swimming pool and gymnasium only.
    Then how do I format my Query?

    I have tried this in the Criteria of the Query:-
    [Forms]![Report 2 Form]![Check Swimming Pool]
    and this returns all properties which have a Swimming Pool. That is OK.

    But how do I format my Query to show all properties with a gymnasium, but NOT a swimming pool??
    Then how do I show properties with both a swimming pool AND a gymnasium??

    Obviously, this problem will increase as I add all the 30 possible categories?

    Any guidance would be appreciated.

    Thanks,

    Red Devil

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the report uses a permanent query (i.e. a query that is stored into the database, you can dynamically modify the SQL property of this query before opening the report. You assemble a SQL statement (or at leats the WHERE part of it), using the contents of the different checkboxes.

    Let's suppose that the query name is QueryForReport2 and that its original SQL statement looks like:
    Code:
    SELECT * FROM SomeTable;
    You can create the new SQL statement for the query before opening the report, like this:
    Code:
    Dim dbs as DAO.Database
    Dim qdf as DAO.QueryDef
    Dim strSQL as String
    
    strSQL = "1 = 1"
    strSQL = strSQL & " AND Sauna = " & me.CheckSauna.Value
    strSQL = strSQL & " AND gymnasium = " & me.CheckGymnasium.Value
    
    '
    ' Continue for every checkbox...
    ' (Ideally this should be done in a loop.)
    '
    strSQL = strSQL & " AND SwimmingPool = " & me.CheckSwimmingPool.Value
    '
    ' Stop here if you want to use strSQL as a filter passed to the Report.
    '
    strSQL = "SELECT * FROM SomeTable WHERE " & strSQL & ";"
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("QueryForReport2 ")
    qdf.SQL = strSQL
    Set QDF = Nothing
    Set dbs = Nothing
    '
    ' Report2 can now be open.
    '
    Docmd.OpenReport "Report2"
    This will create a set of strict conditions (i.e. for every checked checkbox the property must have the corresponding feature while for every unchecked checkbox the property cannot have the feature). If you don't want to exclude features because the corresponding checkboxes are left unchecked, replace the lines assembling the SQL statement, like this:
    Code:
    If me.CheckSauna.Value = True Then strSQL = strSQL & " AND Sauna = " & me.CheckSauna.Value
    (replace True with False to revert the logic).

    You could also pass strSQL directly to the report (as a filter) and don't alter the query:
    Code:
    DoCmd.OpenReport "Report2", , , strSQL
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    You obviously have a tremendous amount of knowledge regarding Access.

    Unfortunately, I only klnow 1% of SQL and i do not understand how to do what you mentioned above.

    I suppose I was hoping there was some way to do the Query by using Criteria?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Unfortunately, values for parameters in an Access query cannot simply be passed using some VBA code when executing or opening the query. You can declare parameters queries in Access but you have to type the values for them (that's the "Enter parameter value" inputbox that Access displays in such a case), or you can use a QueryDef object initialized on the query to supply the parameter values. Queries in Access cannnot be called as stored procedures in T-SQL can be.

    It would be better to understand how the code I posted works and what it does, but you can still try it. Create a command button on the form [Report 2 Form] and paste the code I posted in its OnClick event handler, replacing the names of the controls (CheckSauna, CheckGymnasium etc.) and the names of the fields in the query (Sauna, gymnasium, etc.) by the real names used in your database.

    Briefly explained, the code creates a SQL SELECT statement, adding one condition for each checkbox and yield a result (in strSQL) such as:
    Code:
    SELECT * 
    FROM SomeTable 
    WHERE 1 = 1 AND Sauna = True AND Swimmingpool = True AND Gymnasium = False AND ... etc.
    The alternative code (the one with If Me.Check... only add ANDs conditions for the checkboxes that are checked and add nothing for the checkboxes that are unchecked.

    With the other option (passing the strSQL string as parameter when opening the report), you do not complete que SQL statement (the beginning SELECT ... FROM... WHERE part is missing and the string strSQL looks like:
    Code:
    1 = 1 AND Sauna = True AND Swimmingpool = True AND Gymnasium = False AND ... etc.
    This string will be used as a filter by the report (see DoCmd.OpenReport in Access help for the full explanation, or here: MS Access: Filter report results using the OpenReport VBA command in Access 2003/XP/2000/97.
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    I have been crushing my brain trying to think of a solution for the search form (with check boxes). You have to remember that I am a non-tech guy and I love working with the automatic wizards, or just inserting Ctiteria into a Query. SQL or VBA is just another language to me

    Here is a brief summary of the problem again:-
    a. I have a Form where the client will “tick” checkboxes stating whether a property has a pool, gym, sauna etc (there will be approx 30 choices).
    b. I have 2,000 properties to search through.
    c. I only want a list of those properties that have ALL the items which have been ‘ticked” by the client.


    Please would somebody comment on whether any of the following methods are feasible before I spend hours going down the wrong path?

    1. Use 30 Queries to filter the 30 items?
    2. Use some kind of filter?
    3. Add the items to a new table?

    Any guidance would be appreciated.

    Thanks,

    Paul

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by reddevil1 View Post
    You have to remember that I am a non-tech guy and I love working with the automatic wizards, or just inserting Ctiteria into a Query. SQL or VBA is just another language to me
    Unfortunately, there are seldom simple solutions to complex problems (though it can happen) and you cannot repair a TV set with a kitchen knife

    Could you please post a sample database with the search form as it is now (at least the checkboxes and the fields they are related to) and the table(s) where the query is supposed to select rows (table(s) can be empty: only the definition matters). I would probably be able to set up something from there.
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Thank you for offering to look at a sample database and maybe work out how to resolve the issue?

    I will post my database later - my thoughts are that I can create Query 1 which filters all the properties which have a swimming pool.

    Then can I create Query 2 (only using the properties from Query 1) which further filters the properties which have a gymnasium?

    Then create Query 3 (only using the properties from Query 2) which even further filters the properties which also have a sauna)

    etc etc?? Is it possible to nestle 30 queries like that? Maybe add a new table?

    You are very helpful - thanks.

    database to follow if I can zip it.

  8. #8
    Join Date
    Nov 2009
    Posts
    223
    Here is the Sample database.

    Hopefully it will open OK?

    Cheers,

    Red Devil

  9. #9
    Join Date
    Nov 2009
    Posts
    223

    Sample database

    Trying again.
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The sample database you posted is not in a recognized format (I tried to open it with Acc. 2003, Acc. 2007 and Acc. 2010 and received the same error message). Can you check?
    Have a nice day!

  11. #11
    Join Date
    Nov 2009
    Posts
    223
    Greetings,

    I have uploaded a zipped file coz i thought that was etiquette??

    I will try to upload an .mdb file when i get back to work on sunday.

    thanks

    Red Devil

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Zipping the database was the proper thing to do. Unfortunately, when I open the .zip file (Sample Database 1.zip) it contains a file named Sample Database 1 (no extension). When I try to open this file with Access (2003, 2007 or 2010) either directly or after adding a .mdb or a .accdb extention to the file, an error message pops up saying that the file has not a recognized database format. I also tried to extract the file using Winrar or Winzip, but to no avail.
    Have a nice day!

  13. #13
    Join Date
    Nov 2009
    Posts
    223

    Try again

    I have tried to upload the sample database again.

    It is originally a mdb file, zipped by using WinZip. I am able to open it on my computer?

    Fingers crossed - please let me know if there is still a problem?

    Red Devil
    Attached Files Attached Files

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Private Sub Command77_Click()
    
        On Error GoTo Err_Command77_Click
    
        Dim strDocName As String
        Dim ctl As Control
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        ' Basic SQL statement for the query (no conditions).
        '
        strSQL = "SELECT * FROM [Property Table] WHERE 1 = 1"
        
        ' Loop through the Controls collection of the form (i.e. every control in the form).
        '
        For Each ctl In Me.Controls
        
            ' If the current control is a checkbox, assert that it could be involved in the conditions.
            '
            If ctl.ControlType = acCheckBox Then
                
                ' Assert that the name of the checkbox is also the name of a corresponding column into the table.
                ' Add the condition to the SQL statement only if the checkbox is checked (the condition must be True).
                '
                If ctl.Value = True Then strSQL = strSQL & " AND [" & ctl.Name & "] = True"
            End If
        Next ctl
        
        ' Name of the query to modify, then to open.
        '
        strDocName = "Test Search Property Choice Query 1"
        
        ' Get a handle to that query (QueryDef object).
        '
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs(strDocName)
        
        ' Change the SQL property of the query through the QueryDef object.
        '
        qdf.SQL = strSQL
        
        ' Open the query with its new (modified) SQL statement.
        '
        DoCmd.OpenQuery strDocName, acNormal, acEdit
    
    Exit_Command77_Click:
        Exit Sub
    
    Err_Command77_Click:
        MsgBox Err.Description
        Resume Exit_Command77_Click
        
    End Sub
    Possible problems and solutions.

    1. The SQL statement is built by scanning every control in the form. We assert that among these controls every checkbox can be used to build the WHERE part of the SQL statement and also that every checkbox has the same name as a corresponding column of the table on which the query will be run.
    -If there are checkboxes that do not correspond to a search condition, this method will fail (we shall build a SQL statement with invalid column names).
    -The method will also fail if the name of every checkbox is not strictly identical to the name of its related column in the table. This is the case of [Swimming Pool 2] in [Report 3 Form].
    A possible solution would consist in using the Tag property of every checkbox that must be used for building the SQL statement.
    That way the loop will only process the control that have a non-empty Tag property. This would also allow the checkboxes to have a different name that the name of their related column into the table. It would also make sense to enclose the name in square brackets directly into the Tag property. The loop would then become:
    Code:
        For Each ctl In Me.Controls
        
            ' If the current control is a checkbox, it could be involved in the conditions if its Tag
            ' property contains the name inclosed in square brackets of a related column of the table
            '
            If ctl.ControlType = acCheckBox And Left(Nz(ctl.Tag, ""), 1) = "[" Then
                If ctl.Value = True Then strSQL = strSQL & " AND " & ctl.Tag & " = True"
            End If
        Next ctl
    This method is used in the [Report 3 Form 2] of the attached database.

    2. Examining each control of the form every time the SQL statement must be built is not very efficient if there are many controls on the form, while there are only a relative number of checkboxes. In such a situation, it would be convenient to have a collection composed of the relevant checkboxes only. We can build such a collection when the form is open, then loop through it every time we need to rebuild the SQL statement:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim ctl As Control
        
        ' Initialize the collection.
        '
        Set colCtl = New Collection
        
        ' Loop through the Controls collection of the form (i.e. every control in the form).
        '
        For Each ctl In Me.Controls
        
            ' If the current control is a checkbox, it could be involved in the conditions if its Tag
            ' property contains the name inclosed in square brackets of a related column of the table.
            ' In such a case the control becomes a member of the colCtl collection.
            '
            If ctl.ControlType = acCheckBox And Left(Nz(ctl.Tag, ""), 1) = "[" Then
                colCtl.Add ctl
            End If
        Next ctl
        
    End Sub
    Now we can loop through the colCtl collection without needing to test if the control is a checkbox and if its Tag property contains the name of a colum in the table: this was done when the form was open. The loop then becomes:
    Code:
        ' Loop through the colCtl collection populated when the form was open.
        '
        For Each ctl In colCtl
            If ctl.Value = True Then strSQL = strSQL & " AND " & ctl.Tag & " = True"
        Next ctl
    This method is used in the [Report 3 Form 3] of the attached database.

    3. Let's suppose now that we want to build an object capable of creating the SQL statement and that could be re-used. This could be done with a subform containing the necessary checkboxes plus a property or a function that would return a string containing the WHERE part of the SQL statement statement. Such a technique could be used in several forms for different purposes: Apply a filter to the form, open a report with a criteria, etc. This is done in [Frm_Property], its [SF_Criteria] subform and the associated report [Rpt_Property].
    Attached Files Attached Files
    Have a nice day!

  15. #15
    Join Date
    Nov 2009
    Posts
    223
    Dear Sinndho,

    I just LOVE the report you have produced in your attached Sample database. That works just fantastic - I would love to have that report included in my database.

    Thanks also for your very detailed message which I am still very much trying to understand because my knowledge of VBA is very limited. I do not underatnd it so far, but will try to learn.

    It is important to me that I need to know how to resolve any eroors or problems that may arise in my business. therefore, I need to understand how the database works so i can fix it if it goes wrong in the future.

    I wish I could understand how that perfect report works and then input that into my database.

    Thank you again for the huge help you have given me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •