Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    non related table holds criteria (Access97)

    i have a query in which i want to pull the parameter criteria from a combo box on a form. the combo box references a table that has no relationship to the other tables in the db... it is simply a criteria list... that means i want the query to pull the data from the table literally. the values in this table are things like <=62 or >254. when this criteria is put directly into the criteria row of the query it works fine... but trying to pull it from the combo box doesn't work.... any suggestions on where i might look for the culprit? i apologize if i am unclear... ask and i will try and exlain... thanks... in advance

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: non related table holds criteria (Access97)

    When you build you sql string the combobox has to be out of the quotes.
    sample
    strSQL = "SELECT * FROM MyTable WHERE MyField " & Me.MyCombo

    This suppose that the values always will be a number and you always supply the operator in the combo box.
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: non related table holds criteria (Access97)

    This is the sql statement for the query that the combo box should select the criteria for...

    SELECT [State Legislators & Comm].LegTitle, [State Legislators & Comm].LegDist, [State Legislators & Comm].Party, [State Legislators & Comm].CAdd1, [State Legislators & Comm].MSSNYDist, [State Legislators & Comm].FName, [State Legislators & Comm].LName, [Current Event].[Proposed$], [Current Event].CkAmt, [Current Event].[Ck#], [Current Event].EvntDt, [State Legislators & Comm].PrevCycle
    FROM [State Legislators & Comm] INNER JOIN [Current Event] ON [State Legislators & Comm].ID = [Current Event].ID
    WHERE ((([State Legislators & Comm].ID)=[Forms]![Frm Operations]![Combo61]));

    Providing the sql above is correct, there may be an problem with what the combo box is selecting... i have the bound colum of the combo box equal to the field in the table that holds the actual criteria... Is this a possible avenue for my inability to generate a recordset with this query?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: non related table holds criteria (Access97)

    If combo61 contains the operators (=<, > ...) like you say in your first mail you will have to set the row source of the other combo by code.
    Dim strSQL As String
    <pre>strSQL = "SELECT [State Legislators & Comm].LegTitle, [State Legislators & Comm].LegDist, " & _
    "[State Legislators & Comm].Party, [State Legislators & Comm].CAdd1, " & _
    "[State Legislators & Comm].MSSNYDist, [State Legislators & Comm].FName, " & _
    "[State Legislators & Comm].LName, [Current Event].[Proposed$], [Current Event].CkAmt, " & _
    "[Current Event].[Ck#], [Current Event].EvntDt, [State Legislators & Comm].PrevCycle " & _
    "FROM [State Legislators & Comm] INNER JOIN [Current Event] ON [State Legislators & Comm].ID = "
    "[Current Event].ID WHERE ((([State Legislators & Comm].ID) " & [Forms]![Frm Operations]![Combo61] & _
    "));"
    Me.comboxx.Rowsource = strSQL</pre>

    Pay attention to the where condition : there is no = as the operator will be provided by combo61.
    Francois

Posting Permissions

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