Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Tulsa, OK
    Posts
    50

    Unanswered: Query Criteria - Parameter Question

    I have a query in which the criteria of two of the query fields is 'OR'ed together, and the query gets these criteria from two ComboBoxes on a form (with OK button). If one of the Combos is left blank, I get a 'Parameter request dialog box' (I assume because of the 'OR' in the 'WHERE' statement).

    How would I get the 'Parameter request dialog box' to not show up (IOW, let the blank Combo value automatically be recognized as the criteria for that query field)?
    Thanks in advance or hope this helps.

  2. #2
    Join Date
    Mar 2004
    Location
    Tulsa, OK
    Posts
    50

    Re: Query Criteria - Parameter Question

    Let's try this in Layman's terms ....


    Available jobs query. I have built a form from which I can do a search by 'location' or by 'job type'. Problem: If I only enter a 'location' and click the OK button, a box pops up requesting 'job type'. Same thing in reverse if I only enter a 'job type'. I do not want to see the box pop up. If I want to see all job types in a certain location, I want to enter that location, click OK, and see all jobs in that location without being prompted for more information.

    PS. This is kind of petty, actually. If i hit enter when the box pops up, the query does what I want. I'm just annoyed by that box with no way to turn it off. Looks kinda unprofessional, ya know?

    Thanks in advance.

    SD
    Thanks in advance or hope this helps.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could build the query dynamically during the on_open event of the report if you want to. It's not to tricky once you get the hang of it. I'm a big aesthetics fan too, so I really prefer this method. It allows you complete control over anything and everything.

    One method that doesn't require much coding is to keep the entire query stored in the reports datasource EXCEPT the where clause. Then in the on_open event of the report, you can build the WHERE clause and it's subsequent criteria any way you would like. Here's a really quick and dirty example of what I mean:

    Code:
    Private Sub Report_OPen(Cancel As Integer)
    Dim OldSrc As String
    Dim frm As Form
    Dim where As String
    
    Set frm = forms!yourParameterForm
    
    if frm!yourComboBox.value = "some value" Then
       where = "another value"
    Else
       where = "that one value"
    End If
    
    where = " WHERE " & where
    
    Me.RecordSource = OldSrc &  where
    
    End Sub
    I almost HAVE to use this method because there are certian reports that may have 6 or 7 different criteria that must be restricted to a certian list of values. This is not always easy to pull of using Access built in report parameters.

  4. #4
    Join Date
    Mar 2004
    Location
    Tulsa, OK
    Posts
    50
    Like the Idea, but there are 223 choices in the first Combo, and over 150 in the other. Need a little different functional code in this case. Any ideas?
    Thanks in advance or hope this helps.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is the value of the combo box the exact value you need to pass to the query? If so, then you can set the where clause to the actual value of the combobox, something like:

    Me.RecordSource = OldSrc & " WHERE someField = '" & frm!cmbParameter.value & "'"

    That is of course a much abridged version. What is the relationship of the data in the combo box to that which you actually need to pass to the query? that will work if it's 1:1. If it's not 1:1, say you have a combo box populated with job type descriptions, but you need to get the job type id, you could add another column to the combo box, set it's width to zero, pull in the id, and bind the combo box to the hidden column. When you reference cmbYourcombo.Value, you will return whatever the value of the bound column is.

    Are we getting closer?


  6. #6
    Join Date
    Mar 2004
    Location
    Tulsa, OK
    Posts
    50
    "Is the value of the combo box the exact value you need to pass to the query?..."

    Yes.

    Combo1 => Location (Recordsource is a SELECT DISTINCT from a location field)
    Combo2 => Job Type (Recordsource is a SELECT DISTINCT from a job type field)

    Query options:
    1. Select a location only - gives all jobs from specific location (all types)
    2. Select a Job Type only - gives jobs of that type (all locations)
    3. Select a location and Job Type - gives jobs of that type at that location
    Thanks in advance or hope this helps.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I assume by "Combo1 => Location (Recordsource is a SELECT DISTINCT from a location field)" you mean it would be more like

    "SELECT DISTINCT * FROM t1 WHERE locationfield = YourParameter"

    So basically you have a direct relationship between the combo boxes and there are three possible scenarios. Therefore you have to code an action for each of the possible scenarios.

    You could do this with an if statement similar to this:
    Code:
    Dim OldSrc As String
    Dim  frm As Form
    Dim Where As String
    
    OldSrc = Me.RecordSource
    Set frm = forms!YourParameterForm
    
    If IsNull(frm!Combo1) Then 
       Where = "job_type = " & frm!Combo2.Value
    ElseIf IsNull(frm!Combo2) Then
       Where = "location = " & frm!Combo1.Value
    Else
       Where = "job_type = " & frm!Combo2.Value & " AND location = " & frm!Combo1.Value
    End If
    
    Where = " WHERE " & Where
    
    Me.RecordSource = OldSrc & Where

  8. #8
    Join Date
    Mar 2004
    Location
    Tulsa, OK
    Posts
    50
    Nice.

    I'll let you know what happens.
    Thanks in advance or hope this helps.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I hope it works out for you. Just a side note, I forgot to add quotes around the parameters in the Where string, make sure you do that if they're strings or it will bomb out.

Posting Permissions

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