Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Apr 2009
    Posts
    17

    Unanswered: Combining 2 queries based on a form

    Hi,

    I have a rather complicated problem and I was wondering if anyone could help me. Basically I have a table containing many fields, a form containing 4 combo boxes and 2 text fields, and 2 queries using what is selected in the form to filter the table. I have two queries because I split my task into two to make it easier, I now need to combine these queries into one and I can't work out how to! I am using the query design view to build my queries.

    Query 1 is based on the 4 combo boxes. For each field I wish to filter, and have put

    [Forms]![Filter Table]![Fieldname] OR [Forms]![Filter Table]![Fieldname] is null

    The user can therefore select any combination of the comboboxes and it will filter correctly.

    Query 2 uses the two text fields in the form, which are dates - before and after. In this query, in the Date column I have:

    <[Forms]![Filter Table]![BeforeDate] AND [Forms]![Filter Table]![AfterDate] is null
    >Forms]![Filter Table]![AfterDate] AND [Forms]![Filter Table]![BeforeDate] is null
    Between [Forms]![Filter Table]![BeforeDate] And [Forms]![Filter Table]![AfterDate]

    This allows the user to filter after a date, before a date or between two dates depending on which fields they fill in.

    Both the queries work fine.

    So I now need to combine my two queries! I need the user to be able to fill in any combination of the fields in the form, and the query will filter the table accordingly. I've tried several times, but I can't get it to do this. I wouldn't mind breaking my form into two and using a query based on another query if this would be easier.

    I hope this all makes sense! Please let me know if I can clarify something.

    Thanks a lot,

    Rachel

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    WHERE

    ([YOURFIELDNAME]=[Forms]![Filter Table]![Fieldname] OR [Forms]![Filter Table]![Fieldname] is null)

    OR

    (
    ([YOUROTHERFIELDNAME]<[Forms]![Filter Table]![BeforeDate] AND [Forms]![Filter Table]![AfterDate] is null)

    OR

    ([YOUROTHERFIELDNAME]>Forms]![Filter Table]![AfterDate] AND [Forms]![Filter Table]![BeforeDate] is null)

    OR
    ([YOUROTHERFIELDNAME] Between [Forms]![Filter Table]![BeforeDate] And [Forms]![Filter Table]![AfterDate])
    )

    I believe you just need to have an OR clause between the two possible methods of searching - try changing the WHERE clause to the above, substituting your actual field name where appropriate.

    Let us know how you get on - if this doesn't 'work' please post the actual SQL of the query which will probably be easier for us to help with (In design view, just click 'View - SQL View' and copy / paste the code...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Apr 2009
    Posts
    17
    Thank you for helping me!

    I would have copied and pasted the SQL, but it's quite large so I didn't. However I will now to show what I have in my first query:

    SELECT [Actions Log].[Action Serial], [Actions Log].[Action date], [Actions Log].Description, [Actions Log].RAG, [Actions Log].LOD, [Actions Log].Owner, [Actions Log].Stakeholder, [Actions Log].Status, [Actions Log].Comments, [Actions Log].[Review Date], [Actions Log].[Closed Date]
    FROM [Actions Log]
    WHERE ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo])) OR ((([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo]) AND (([Forms]![Filter Actions Log]![RAG combo]) Is Null And ([Forms]![Filter Actions Log]![RAG combo]) Is Null)) OR ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo]) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null)) OR ((([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo]) AND (([Forms]![Filter Actions Log]![RAG combo]) Is Null) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null)) OR ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo]) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null)) OR ((([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo]) AND (([Forms]![Filter Actions Log]![RAG combo]) Is Null) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null)) OR ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo]) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null)) OR ((([Actions Log].Status)=[Forms]![Filter Actions Log]![Status combo]) AND (([Forms]![Filter Actions Log]![RAG combo]) Is Null) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null)) OR ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null)) OR ((([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Forms]![Filter Actions Log]![RAG combo]) Is Null) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null)) OR ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null)) OR ((([Actions Log].Stakeholder)=[Forms]![Filter Actions Log]![Stakeholder combo]) AND (([Forms]![Filter Actions Log]![RAG combo]) Is Null) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null)) OR ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null)) OR ((([Actions Log].LOD)=[Forms]![Filter Actions Log]![LOD combo]) AND (([Forms]![Filter Actions Log]![RAG combo]) Is Null) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null)) OR ((([Actions Log].RAG)=[Forms]![Filter Actions Log]![RAG combo]) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null)) OR ((([Forms]![Filter Actions Log]![RAG combo]) Is Null) AND (([Forms]![Filter Actions Log]![LOD combo]) Is Null) AND (([Forms]![Filter Actions Log]![Stakeholder combo]) Is Null) AND (([Forms]![Filter Actions Log]![Status combo]) Is Null));

    Sorry! So it's basically every combination of each field being filled in and left empty. Access does this automatically when I put:

    [Forms]![Filter Table]![Fieldname] OR [Forms]![Filter Table]![Fieldname] is null

    in each of the 4 columns of the query deisgn view. When I tried what you suggested I put:

    SELECT [Actions Log].[Action Serial], [Actions Log].[Action date], [Actions Log].Description, [Actions Log].RAG, [Actions Log].LOD, [Actions Log].Owner, [Actions Log].Stakeholder, [Actions Log].Email, [Actions Log].Status, [Actions Log].Comments, [Actions Log].[Review Date], [Actions Log].[Closed Date]
    FROM [Actions Log]
    WHERE ([RAG]=Forms![Filter Actions Log]![RAG combo] Or Forms![Filter Actions Log]![RAG combo] Is Null)
    Or
    ([LOD]=Forms![Filter Actions Log]![LOD combo] Or Forms![Filter Actions Log]![LOD combo] Is Null)
    Or
    ([Stakeholder]=Forms![Filter Actions Log]![Stakeholder combo] Or Forms![Filter Actions Log]![Stakeholder combo] Is Null)
    Or
    ([Status]=Forms![Filter Actions Log]![Status combo] Or Forms![Filter Actions Log]![Status combo] Is Null);

    I left out the date bit for now. However the above does not work - it gives my all the entries in the table.

    Any idea what I should do?

    Thanks for you help,

    Rachel

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Wow! That sir is one hell of a query!

    I think we might be better off going back to basics here...

    I'm guessing you have a form which is bound back to data which you are using to search for records in your database?

    I think it would probably be easier (and certainly easier to work with) if you were to build the SQL statement from the values of the form controls rather than referring to the controls themselves.

    Come the day you want to modify the above, neither you, I, nor the man on the moon would have a cat's chance in hell in undestanding what records that query would return!

    Are you able to post a .zip version of your database?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Apr 2009
    Posts
    17
    Yeah I know it's really complicated!

    I'm afraid I can't post the database, there's some sensitive information in it. Plus the work firewall won't let me upload anything!

    I don't think my form is actually tied to any data. I just created a new form, and typed in values for the combo boxes. I then use what is selected in the form to filter the query. I just followed this How to use the query by form (QBF) technique in Microsoft Access, not exactly but enough to get the query working. It's easier to see what it's doing in query design view.

    I'm afraid I don't understand what you mean by: "I think it would probably be easier (and certainly easier to work with) if you were to build the SQL statement from the values of the form controls rather than referring to the controls themselves." I'm quite new to all this especially SQL!

    Thanks again,

    Rachel

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Ok then - answer the following then please:

    What are the names of the controls on the form which are used as criteria - List each one please...

    Also what are the field names and the tables they are stored in which refer to the above...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Apr 2009
    Posts
    17
    Right...

    Table: Action Log
    Fields in Action Log: Action Serial, Action date, Description, RAG, LOD, Owner, Stakeholder, Email, Status, Comments, Review Date, Closed Date

    Form: Filter Actions Log
    Comboboxes: RAG combo, LOD combo, Stakeholder combo, Status combo
    Text fields: AfterDateReview, BeforeDateReview

    i.e. The fields I want to filter in Actions Log are: RAG, LOD, Stakeholder, Status, and Review Date

    Is that everything you wanted?

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Fields

    Yes - thats fine for now - give me five.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Apr 2009
    Posts
    17
    Thanks very much - I do appreciate your help!

  10. #10
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    During the onclick event of your 'filter' button, try this:

    dim strSQL as string
    strSQL = "SELECT [Actions Log].[Action Serial], [Actions Log].[Action date], [Actions Log].Description, [Actions Log].RAG, [Actions Log].LOD, [Actions Log].Owner, [Actions Log].Stakeholder, [Actions Log].Status, [Actions Log].Comments, [Actions Log].[Review Date], [Actions Log].[Closed Date]
    FROM [Actions Log] WHERE "

    If not isnull(Me.[RAG combo]) THEN

    strsql = strsql & " ( ([Action Log].RAG = '" & me.[RAG combo] & "') AND "

    End If

    If Not isnull(Me.[LOD Combo]) THEN

    strsql = strsql & " ([Action Log].LOD = '" & me.[LOD combo] & "') AND "

    End IF

    If Not isnull(Me.[Stakeholder combo]) THEN

    strSQL = strSQL & " ([Action Log].Stakeholder = '" & me.[Stakeholder combo] & "') AND "

    End If

    If Not isnull(Me.[Status combo]) THEN

    strSQL = strSQL & " ([Action Log].Status = '" & me.[Status combo] & "')"

    End If

    If right(strSQL,6) = "WHERE " THEN
    'No criteria has been specified - remove the WHERE and add a semicolon

    strSQL = replace(strsql, "WHERE",";")


    Else If right(strsql, 4) = "AND " THEN

    strSQL = strSQL & "+"
    strSQL = replace(strsql, "AND +",")"

    End If

    If isnull(Me.AfterDateReview) THEN

    if isnull(Me.BeforeDateReview) THEN
    'No date criteria has been specified

    Else
    'A date (hopefully you have error checking) has been entered into the before date review

    strSQL = strsql & " OR ([Action Log].ReviewDate < " & [Forms]![Filter Table]![BeforeDate] & ");"

    endif

    Else

    If isnull(Me.BeforeDateReview) THEN
    'There is a date in AfterDateReview but not

    strSQL = strsql & " OR ([Action Log].ReviewDate > " & [Forms]![Filter Table]![AfterDate] & ");"

    else

    strSQL = strSQL & " OR ([Action Log].ReviewDate Between " & [Forms]![Filter Table]![BeforeDate] & " And " & [Forms]![Filter Table]![AfterDate] & ");"

    endif

    endif

    Debug.print strsql

    This should build an SQL statement based on the values in your form - At the moment its not doing anything as we need to check it works (Havent got time my end Im afraid so give it a go).

    The last statement will debug.print the SQL so you can copy and past it into a new query - check that it is not mal-formed and that it returns the values you want. (It may take us a few goes - in fact, past the SQL back here please).

    From this, you could use the SQL directly as the record source of a form or report or even change an existing database query to have this SQL.

    Please post results - I did this off the top of my head so please forgive me if there are typos.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  11. #11
    Join Date
    Apr 2009
    Posts
    17
    OK, I will give this a try. I have pasted it into the on click event of a command button. At the moment 3 lines are highlighted red as having errors:


    dim strSQL as string
    strSQL = "SELECT [Actions Log].[Action Serial], [Actions Log].[Action date], [Actions Log].Description, [Actions Log].RAG, [Actions Log].LOD, [Actions Log].Owner, [Actions Log].Stakeholder, [Actions Log].Status, [Actions Log].Comments, [Actions Log].[Review Date], [Actions Log].[Closed Date]
    FROM [Actions Log] WHERE "

    If not isnull(Me.[RAG combo]) THEN

    strsql = strsql & " ( ([Action Log].RAG = '" & me.[RAG combo] & "') AND "

    End If

    If Not isnull(Me.[LOD Combo]) THEN

    strsql = strsql & " ([Action Log].LOD = '" & me.[LOD combo] & "') AND "

    End IF

    If Not isnull(Me.[Stakeholder combo]) THEN

    strSQL = strSQL & " ([Action Log].Stakeholder = '" & me.[Stakeholder combo] & "') AND "

    End If

    If Not isnull(Me.[Status combo]) THEN

    strSQL = strSQL & " ([Action Log].Status = '" & me.[Status combo] & "')"

    End If

    If right(strSQL,6) = "WHERE " THEN
    'No criteria has been specified - remove the WHERE and add a semicolon

    strSQL = replace(strsql, "WHERE",";")


    Else If right(strsql, 4) = "AND " THEN

    strSQL = strSQL & "+"
    strSQL = replace(strsql, "AND +",")"

    End If

    If isnull(Me.AfterDateReview) THEN

    if isnull(Me.BeforeDateReview) THEN
    'No date criteria has been specified

    Else
    'A date (hopefully you have error checking) has been entered into the before date review

    strSQL = strsql & " OR ([Action Log].ReviewDate < " & [Forms]![Filter Table]![BeforeDate] & ");"

    endif

    Else

    If isnull(Me.BeforeDateReview) THEN
    'There is a date in AfterDateReview but not

    strSQL = strsql & " OR ([Action Log].ReviewDate > " & [Forms]![Filter Table]![AfterDate] & ");"

    else

    strSQL = strSQL & " OR ([Action Log].ReviewDate Between " & [Forms]![Filter Table]![BeforeDate] & " And " & [Forms]![Filter Table]![AfterDate] & ");"

    endif

    endif

    Debug.print strsql


    I'm assuming they're just typos somewhere but I'm not confident enough with SQL syntax to know how to correct them - I might make it worse! If you could let me know how to correct these lines I will run it.

    Thanks,

    Rachel

  12. #12
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    2nd and 3rd Problems:
    ElseIf right(strsql, 4) = "AND " THEN
    'Remove space in the Else If as above

    strSQL = strSQL & "+"
    strSQL = replace(strsql, "AND +",")")
    'Extra bracket in the above line

    End If


    First part is because string wont fit on one line - prior to each line break

    have " & _

    so:

    strSQL = "Blah blah, blah, " & _
    "more blah, more blah, " & _
    "end bit of string"
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  13. #13
    Join Date
    Apr 2009
    Posts
    17
    Thanks, I've now solved the 2nd and 3rd problems. The first I couldn't get to work quite as you said but having encountered this in the past I have managed to stop it being red - now the question is does it still say what you want it to! It now reads:

    strSQL = "SELECT [Actions Log].[Action Serial], [Actions Log].[Action date], [Actions Log].Description, [Actions Log].RAG, [Actions Log].LOD, [Actions Log].Owner, [Actions Log].Stakeholder, [Actions Log].Status, [Actions Log].Comments, [Actions Log].[Review Date], [Actions Log].[Closed Date]" _
    & "FROM [Actions Log] WHERE "

    I have run it (by clicking on the button) and at the moment nothing happens - not quite sure about what the debug bit is supposed to do...

    Please let me know what you'd like me to do next!

    Thanks!

  14. #14
    Join Date
    Apr 2009
    Posts
    17
    Thanks for all your help today - I have to go home now but I'll be back tomorrow. Hope we can make it work!

  15. #15
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Nearly - but we need a space before FROM so try this one;

    strSQL = "SELECT [Actions Log].[Action Serial], [Actions Log].[Action date], " & _
    "[Actions Log].Description, [Actions Log].RAG, [Actions Log].LOD, [Actions Log].Owner, " & _
    "[Actions Log].Stakeholder, [Actions Log].Status, [Actions Log].Comments, " & _
    "[Actions Log].[Review Date], [Actions Log].[Closed Date] " & _
    "FROM [Actions Log] WHERE "

    In the VBA window, add a watch to the line at the end of the sub debug.print strSQL - by clicking just to the left (This will highlight the line in red and the code will stop when it reaches this point).

    Then run again, when the code stops, click play button in the VBA window and the SQL should appear in the VBA immediate window - copy and past this string here please...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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