Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005

    Question Unanswered: Access 03: Listbox requery method not working with custom SQL

    Hello, I am having some issues getting the listbox on the form to display the results of the following custom query:

    Dim i As Integer
    Dim typeFilter As String
    Dim rqStr As String
    For i = 0 To lstCompTypeCd.ListCount - 1
    'Need to build this parameter based on user input
    If lstCompTypeCd.Selected(i) = True Then _
        typeFilter = typeFilter & "PriceTypeId='" & lstCompTypeCd.Column(0, i) & "' OR "
    Next i
    rqStr = "SELECT CompId, Name, PriceTypeId, CompanyId, ShopStore, " & _
                " FROM [Competition Table] WHERE (LastStatusId=3 AND DeletedDt=null AND (" & _
                Left$(typeFilter, Len(typeFilter) - 4) & "));"
    lstCompetitors.RowSource = rqStr
    Edit: BTW, [Competition Table] is a SQL-pass thru driven query

    Listbox will not display the results on the form, when in form view.
    Query will work correctly, and pull in the correct data, if I run the query from the design view (i.e., click on the list, choose the Rowsource combo, and press '...' to edit/view)

    A bit puzzled here... anything I'm missing?

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    The only thing I see is 'DeletedDt=null'. I think it needs to be 'DeleteDt Is Null'.

  3. #3
    Join Date
    May 2005
    Quote Originally Posted by DCKunkle
    The only thing I see is 'DeletedDt=null'. I think it needs to be 'DeleteDt Is Null'.
    You win

    Damn MSASQL... >_<

Posting Permissions

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