Page 1 of 5 123 ... LastLast
Results 1 to 15 of 71
  1. #1
    Join Date
    Nov 2011
    Location
    Ireland
    Posts
    31

    Unanswered: Multiple Criteria Query

    Hi All,
    First time poster here. I've been told this is the place to learn about Access so here goes.
    I'm trying to set up a query that uses multiple criteria. I've designed a form to enter the criteria to search by eg: Name, Date, Job. I'd like to have it so that a user may enter all three criteria or just one....in other words they'll still get results if one or two of the controls are left blank.
    I've also got a 'View Results' command button on the form which opens a report with the query as it's source. Hope i've explained it ok.

    Appreciate any advice,
    Thanks,
    Pol

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so the end result is some SQL to use as a rowsource in a form or combo

    Code:
    Dim StrSQL as string
    strSQL = "SELECT my, comma, separated, column, list FROM mytable"
    strSQL = strSQL & " WHERE 1=1"  'the 1=1 will return all rows and simplifies the following code
    if len(mycontrolname1)>0 then 'check to see if there is a value in the control
    strSQL = strSQL & " mystringcolumn = '" & mycontrolname1.value & "'"  'sting literals must be encapsualted with either ' or "
    endif
    if isnumeric(mycontrolname2) then
    strSQL = strSQL & " mynumericcolumn = " & mycontrolname2.value 'numeric columns don't need encapsualting
    endif
    if isdate(mycontrolname3) then
    strSQL = strSQL & " mydatecolumn = #" & format(mycontrolname3.value,"MM/DD/YYYY") & "#" 'dates must be encapsualted by # AND be in US or ISO format, sadly not real date format dd/mm/yyyy
    endif
    strSQL = strSQL & " ORDER BY a, column, list"
    'the assign the variable strSQL to wherever you want to use it
    note the leading space on each line after the first, quite often people omit the space and are puzzles by the syntax error
    if you get problems display the strSQL as a msgbox and check the SQL you are sending looks reasonable AND does what you think it does... its very common to look at the VBA code, think it looks OK, but running the VBA doesn't do what you think its doing.

    the 1=1 is a nifty little trick that measn you will always have a valid where clause. it makes the following code simpler
    numeric values can be quoted as is, the SQL interpreter will handle any formatting issues... although its always smarter to be explicit and if needed coerce values to be the right type by using, say Cint, CLng, cDBL and so on
    strings / text should be encapsualted with either ' or ", some people use the CHR$ fucntion for the relevant symbol as the IDE can get upset about using ' or "
    date literal values must be in US format mm/dd/yyyy or ISO date format yyyy/mm/dd and encapsualted with the # symbol
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Location
    Ireland
    Posts
    31
    Thank you for your extremely quick reply. I probably should have mentioned in my original post that i'm no expert with Access and so i honestly haven't a clue how to implement your solution. Could you perhaps simplify it so that even an idiot like me can understand ?

    Thanks again for your help

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this type of query has to be run dynamically. you cannot set up a generalised query that uses parameters in the query designer.

    I was assuming you had a form with your filter categories as controls
    plus a button that writes tjhe query and does something
    as you didn't specify what the something is its tricky to give an implementation

    what you could do is assign the value of strSQL to the rowsource (or record source) of a form / report.

    you can use a modified version as a filter, apply the apply that to open a form or report using the docmd.openreport or docmd.openform macros
    a filter doesn't need the select bit. effectively a filter is the WHERE clause, without using the word WHERE.

    eg
    me.filter 'Name="Pol_IE" and DateColumn = #2011/11/22#'
    me.filteron = true
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Location
    Ireland
    Posts
    31
    Again...thanks for the help. Ok i understand i can't do this in the query designer. I also don't know anything about SQL. At the moment, all i have is a form, a query and a report. The selections i make in the controls on the form are supposed to populate the criteria for the query (i've linked them to each other). The button on my form simply opens a report for preview....this report is based on the query.
    The button isn't designed to do anything other than open the report (there's no macros or anything else firing, or nothing in the events tab).
    That's about the best i can explain it.

    Thanks,
    Pol

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so pass the parameters to the report as part of the button press
    have a the report based on a query. forget parameters in the query.
    instead of building the SQL statement just build the where clause (without the where)

    then call the report using the openreport method of docmd
    ms access docmd openreport - Google Search

    eg:-
    Code:
    strSQL = "1=1"
    if len(mycontrolname1)>0 then
    strSQL = strSQL & " mystringcolumn = '" & mycontrolname1.value & "'"
    endif
    if isnumeric(mycontrolname2) then
    strSQL = strSQL & " mynumericcolumn = " & mycontrolname2.value
    endif
    if isdate(mycontrolname3) then
    strSQL = strSQL & " mydatecolumn = #" & format(mycontrolname3.value,"MM/DD/YYYY") & "#"
    endif
    strSQL = strSQL & " ORDER BY a, column, list"
    docmd.openreport myreport,ACView,,strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2011
    Location
    Ireland
    Posts
    31
    Quote Originally Posted by healdem View Post
    then call the report using the openreport method of docmd
    Right i understand that i should use the button on the form to tell the report what i want, but i dont understand the bit at the top about 'openreport' and 'docmd'. The link you provided is more SQL which i don't understand.

    Where do i place the code you provided to make this work ?

    Thanks

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I've only briefly read through this thread, but I think the bit of info you're missing is this:

    On your form, right click on 'View Results' results button you've made, and click 'Properties'.

    Next, select the 'Event' tab, and on the option labelled, 'On Click', use the dropdown box to change it to '[Event Procedure]'.

    Now that's changed, click the '...' button next to the arrow for the dropdown box, this will take you to the VBA (Visual Basic for Applications) editor (which is what language the code healdem wrote is written in).

    Now you should see something like:

    Code:
    Option Compare Database
    
    Private Sub YOUR-BUTTON-NAME_Click()
    
    End Sub
    Any code you want to happen when that button is clicked will have to go between the 'Private Sub ...()' bit, and the 'End Sub' bit, so..:

    Code:
    Option Compare Database
    
    Private Sub YOUR-BUTTON-NAME_Click()
    
    'A SHEET LOAD OF CODE CAN GO HERE!!
    
    End Sub

    As I said I haven't read the thread properly, but from what I saw you need to generate a report that's based around dynamic variables (i.e., that can change at will, and may or may not need to be included in the query).

    So you'd want to use some code that will look at each of the controls on your form, and using their values (if they have any) to generate your report.

    The code healdem gave you should do this for you.


    Give it a try, and let us know if it works, and if not, what error, or what part of the report is not generating as expected.


    All the best!


    PS: The 'doCmd.' tells access that it has to do something when clicked, in this case it is: 'openreport', which is pretty self explanatory.

    The bits after that: 'myreport,ACView,,strSQL' give further instructions to the command of opening a report, namely...

    myreport = The name of the report to be opened. You'll have to change this to whatever your report is called.
    ACView = This is the way you want the report to be opened (acView is actually just short for acViewNormal). So for example, you can specify 'acViewLayout' or 'acViewDesign', if you want the report to be opened in layout mode, or design mode, respectively.
    ,, = Is used because this command is missing an optional instruction (in this case it's the 'FilterName' parameter, but you'll probably never use that, at least not for a while)
    strSQL = This is the 'WHERE' clause in an SQL statement, which is what healdem wrote effectively. This will tell your report to generate the information on it, whenever it finds records that match these conditions... This is the bit that's probably most difficult to learn/write.

    strSQL would have to look something like:

    Code:
    yourTableName.[yourFieldName] = me.yourControlsName
    Which, strictly speaking should, for this example, look like:

    Code:
    strSQL = "yourTableName.[yourFieldName] = '" & me.yourControlsName & "' "

    So, let's say you wanted to generate a report that listed all your employees who have 'Fred' as their first name.

    You could very well have a table that looks something like:

    Code:
         employee_table
     First Name  |  Surname 
    -------------+----------
        Fred     |  Durkins
        Kurt     |  Enfield
        Fred     |  Nashing
    Where:
    'employee_table' is the name chosen for this particular table.
    'First Name' is a field (or 'column') on the table.
    'Surname' is a field (or 'column') on the table.
    'Fred' is a piece of data stored in the table.
    'Durkins' is a piece of data stored in the table.

    Then, on your form, you have a control that's a combobox (or dropdown box, if you prefer), which we'll call 'fnameCombo'. This combobox uses every distinct First Name held in the employee_table.[First Name] field. So, in this example, opening the combo/dropdown box would reveal two choices, 'Fred' and 'Kurt'.

    Now, also on your form you have a button, that generates a report. We'll call this button, 'reportButton'. The desired response for this button would be to open a pre-made report, that has been called, 'myReport1'.

    The report simply brings up a list of every employee that possesses whatever 'First Name' is selected using the combo/dropdown box on our form. So, for example, if 'Fred' was chosen in our 'fnameCombo' combo/dropdown box, then when the 'reportButton' was clicked, the report would display:

    Fred Durkins
    Fred Nashing
    To achieve this we would have to place a small piece of code behind the button, to dynamically tell the report which 'First Name' we wanted the report to report on.

    This code will look something like:

    Code:
    Private Sub reportButton_Click()
    
       'Define a variable to contain a String value (String being a collection
       'of Characters).
    
    Dim strSQL As String
    
    
       'We then give the currently empty 'strSQL' variable, a String to remember.
       '
       'In this case, that's:
    
    strSQL = "employee_table.[First Name] = '" & Me.fnameCombo.Value & "'"
    
    
       'Now that strSQL is setup correctly (it will check every records [First Name]
       'to see if it matches whatever has been chosen in the 'fnameCombo', 
       'which in this example is going to be 'Fred'), we can proceed to generate
       'the report with this set of results (results being all records that have
       'their [First Name] saved as 'Fred'.
    
    
       'So, we will open the report now (which, if you remember, we called
       ''myReport1'), with the following command:
    
    DoCmd.OpenReport myReport1, , , strSQL
    
    End Sub
    There you have it, clicking the 'reportButton' will open the report 'myReport1' to contain only results matching whatever was selected in the 'fnameCombo' as their first name.


    This kind of stuff is pretty difficult to explain to be honest, and even more tricky to explain well... Hopefully my example has helped you understand some points about how VBA, and with that, Access, works.

    VBA is a great first language though, it's simple and rather limited, but not in a particularly restrictive way.


    I'll do my best to help you out, so feel free to keep the questions coming!
    Last edited by kez1304; 11-23-11 at 11:44.
    Looking for the perfect beer...

  9. #9
    Join Date
    Nov 2011
    Location
    Ireland
    Posts
    31
    Thanks for the help kez. I've wiped the criteria from the query design view and copied healdem's code into the OnClick event of my button. I've put the name of my controls in there but i'm not sure what i've to put in where it says 'mystringcolumn' , 'mynumericcolumn' etc. Here's my code as it stands

    Code:
    Option Compare Database
    
    Private Sub cmd_ViewResults_Click()
    strSQL = "1=1"
    If Len(cbo_Analyst) > 0 Then
    strSQL = strSQL & " mystringcolumn = '" & cbo_Analyst.Value & "'"
    End If
    If IsNumeric(cbo_Product) Then
    strSQL = strSQL & " mynumericcolumn = " & cbo_Product.Value
    End If
    If IsDate(txt_ExactDate) Then
    strSQL = strSQL & " mydatecolumn = #" & Format(txt_ExactDate.Value, "DD/MM/YYYY") & "#"
    End If
    strSQL = strSQL & " ORDER BY a, column, list"
    DoCmd.OpenReport rpt_SearchResults2, AcView, , strSQL
        
    End Sub
    I'm getting an error message which says -

    Compile Error:
    Expected variable or procedure, not module


    Thanks,
    Pol

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where are you getting
    Compile Error:
    Expected variable or procedure, not module
    what line is highlighted in the IDE?
    if you are developing in VBA then its going to pay for you to develop good debugging skills.

    for mystringcolumn read the column in your database that happens to be a column of type text / string
    in this case you are searching for a column called say personsname, so replace mystring column with personsname. note name is a reserved word in MS Access and should not be used

    mystringcolumn, my datecolumn, mynumeric column are placeholders, you replace with the names of the columns actually in your database. I use those names to indicate that this line deals with a string / text column, or numeric or date column
    BTW
    strSQL = strSQL & "
    should read
    strSQL = strSQL & " AND
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Code:
    Private Sub cmd_ViewResults_Click()
    
    strSQL = "1=1"
    
    If Len(cbo_Analyst) > 0 Then
        strSQL = strSQL & " AND >>aTableName.[aFieldName]<< = '" & cbo_Analyst.Value & "'"
    End If
    
    If IsNumeric(cbo_Product) Then
        strSQL = strSQL & " AND >>aTableName.[aFieldName]<< = " & cbo_Product.Value
    End If
    
    If IsDate(txt_ExactDate) Then
        strSQL = strSQL & " AND >>aTableName.[aFieldName]<< = #" & _
                    Format(txt_ExactDate.Value, "DD/MM/YYYY") & "#"
    End If
    
    strSQL = strSQL & " ORDER BY >>REPLACE ME WITH aTableName.[aFieldName], OR REMOVE ME COMPLETELY<<"
    
    DoCmd.OpenReport rpt_SearchResults2, AcView, , strSQL
        
    End Sub
    Is what you're looking for...


    So you want, for a random example:

    Code:
    Private Sub cmd_ViewResults_Click()
    
    strSQL = "1=1"
    
    If Len(cbo_Analyst) > 0 Then
        strSQL = strSQL & " AND main.[Analyst Name] = '" & cbo_Analyst.Value & "'"
    End If
    
    If IsNumeric(cbo_Product) Then
        strSQL = strSQL & " AND main.[Product Code] = " & cbo_Product.Value
    End If
    
    If IsDate(txt_ExactDate) Then
        strSQL = strSQL & " AND main.[Exact Date] = #" & _
                    Format(txt_ExactDate.Value, "DD/MM/YYYY") & "#"
    End If
    
    strSQL = strSQL & " ORDER BY main.[Analyst Name], main.[Product Code]"
    
    DoCmd.OpenReport rpt_SearchResults2, AcView, , strSQL
        
    End Sub
    Where you have a table that looks like:

    Code:
                  main (Table Name)
    
     Analyst Name | Product Code | Exact Date 
    --------------+--------------+------------
         Jonny    |   TT0235GSx  | 10/10/2010
    
    etc, etc...
    Last edited by kez1304; 11-24-11 at 06:16.
    Looking for the perfect beer...

  12. #12
    Join Date
    Nov 2011
    Location
    Ireland
    Posts
    31
    Hi Guys,
    Ok i've changed the code but i'm getting the same error message.


    Code:
    Private Sub cmd_ViewResults_Click()
    
    strSQL = "1=1"
    
    If Len(cbo_Analyst) > 0 Then
        strSQL = strSQL & " AND >>tbl_Main.[TName]<< = '" & cbo_Analyst.Value & "'"
    End If
    
    If IsNumeric(cbo_Product) Then
        strSQL = strSQL & " AND >>tbl_SampleResults.[Product]<< = " & cbo_Product.Value
    End If
    
    If IsDate(txt_ExactDate) Then
        strSQL = strSQL & " AND >>tbl_Main.[Date]<< = #" & _
                    Format(txt_ExactDate.Value, "DD/MM/YYYY") & "#"
    End If
    
    DoCmd.OpenReport rpt_SearchResults2, AcView, , strSQL
        
    End Sub

    Private Sub cmd_ViewResults_Click() is highlighted in yellow

    AcView is highlighted in blue

    Thanks

  13. #13
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Try:

    Code:
    Private Sub cmd_ViewResults_Click()
    
    strSQL = "1=1"
    
    If Len(cbo_Analyst) > 0 Then
        strSQL = strSQL & " AND tbl_Main.[TName] = '" & cbo_Analyst.Value & "'"
    End If
    
    If IsNumeric(cbo_Product) Then
        strSQL = strSQL & " AND tbl_SampleResults.[Product] = " & cbo_Product.Value
    End If
    
    If IsDate(txt_ExactDate) Then
        strSQL = strSQL & " AND tbl_Main.[Date] = #" & _
                    Format(txt_ExactDate.Value, "DD/MM/YYYY") & "#"
    End If
    
    DoCmd.OpenReport rpt_SearchResults2, acViewNormal, , strSQL
        
    End Sub
    The '>>' and '<<' I just put in to make it more noticeable... Probably should of mentioned that. Balls up on my part.

    Also, I'm not sure VBA likes having - 'cmd_ViewResults_Click()' - Not 100% sure, but it might be looking for a control called 'cmd' and a custom defined event called 'ViewResults_Click'...

    Try copying and pasting the following over what you have:

    Code:
    Private Sub cmdViewResults_Click()
    
    strSQL = "1=1"
    
    If Len(cbo_Analyst) > 0 Then
        strSQL = strSQL & " AND tbl_Main.[TName] = '" & cbo_Analyst.Value & "'"
    End If
    
    If IsNumeric(cbo_Product) Then
        strSQL = strSQL & " AND tbl_SampleResults.[Product] = " & cbo_Product.Value
    End If
    
    If IsDate(txt_ExactDate) Then
        strSQL = strSQL & " AND tbl_Main.[Date] = #" & _
                    Format(txt_ExactDate.Value, "DD/MM/YYYY") & "#"
    End If
    
    DoCmd.OpenReport rpt_SearchResults2, acViewNormal, , strSQL
        
    End Sub
    And change the name of your 'cmd_ViewResults' button to, 'cmdViewResults' instead, then give it a whirl.
    Looking for the perfect beer...

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    had you looked at the supplied link, as posted in #6 to the docmd.openreport you would have realised that you should have used ACViewNormal.. not ACView..

    the error message was telling you what the problem was (in its own cack-handed way). it even highlighted the problem. and you would have saved yourself some 90 minutes or so.

    I wouldn't nbother with changing cmd_ to cmd (at oleast not right now. change the acview to acviewnormal and see what happens.

    Id recommend thast you examine the value of strSQL to check that what is being sent to the SQL engine is both valid SQL AND does what you think it does. its very easy to look at such statement, know what you wanted to say, but not actually explicitly told the compiler what you want.


    what may be a smart call is whilst developingthe process is to coerce some know good values to check that the report is workign as expected, then make certain the form can supply the SQL as you want.


    for now it could be as daft as
    version 1:
    strSQL & " AND tbl_Main.[TName] = 'Durkins'"
    Version 2:
    strSQL & " AND tbl_Main.[TName] = 'Durkins'"
    strSQL = strSQL & " AND tbl_SampleResults.[Product] = " & <insert a valid product number here, without the angular braces <> >

    Version 3:
    strSQL & " AND tbl_Main.[TName] = 'Durkins'"
    strSQL = strSQL & " AND tbl_SampleResults.[Product] = " & <insert a valid product number >
    strSQL = strSQL & " AND tbl_Main.[Date] = #<insert a valid date here>#"
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2011
    Location
    Ireland
    Posts
    31
    Code:
    Private Sub cmdViewResults_Click()
    
    strSQL = "1=1"
    
    If Len(cbo_Analyst) > 0 Then
        strSQL = strSQL & " AND tbl_Main.[TName] = '" & cbo_Analyst.Value & "'"
    End If
    
    If IsNumeric(cbo_Product) Then
        strSQL = strSQL & " AND tbl_SampleResults.[Product] = " & cbo_Product.Value
    End If
    
    If IsDate(txt_ExactDate) Then
        strSQL = strSQL & " AND tbl_Main.[Date] = #" & _
                    Format(txt_ExactDate.Value, "DD/MM/YYYY") & "#"
    End If
    
    DoCmd.OpenReport rpt_SearchResults2, acViewNormal, , strSQL
        
    End Sub

    Alright i think we're nearly there. I changed the code and got this error message

    Run-time error '2497'
    The action or method requires a Report Name argument

    DoCmd.OpenReport rpt_SearchResults2, acViewNormal, , strSQL is highlighted in yellow

    healdem - I did look at the link you gave me but as i said, i don't understand SQL so it's all Chinese to me

    Thanks lads

Posting Permissions

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