Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Grand Junction, Colorado
    Posts
    12

    Unanswered: Order by problem

    I know this should be a simple problem but I'm stuck.

    I need the following code to change the order to display by "[DrawingID] DESC" I can't seem to find where or how to put the command?

    Any suggestions are appreciated.

    Private Sub cmbFilter_Click()
    On Error Resume Next

    Dim sSql As String
    Dim sCriteria As String
    sCriteria = "WHERE 1=1 "


    'This code is for a specific search where you will need to enter the exact string
    'The source for this code can either be from a table or query
    If Me![DWGIDSearch] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.DrawingID Like """ & DWGIDSearch & """"
    End If


    'This code is for a Like search where can enter part of a string
    'The source for this code can either be from a table or query
    If Me![DesignerFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.DesignerID Like """ & DesignerFilter & """"
    End If
    If Me![NotStarted] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.DateStarted is null "
    End If

    If Me![NotComplete] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.DateFinished is null "
    End If
    If Me![NotSold] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.DateSold is null "
    End If

    If Me![LastNameFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.LastName Like """ & LastNameFilter & "*"""
    End If
    If Me![FirstNameFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.FirstName Like """ & FirstNameFilter & "*"""
    End If
    If Me![ProjectNameFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.ProjectName Like ""*" & ProjectNameFilter & "*"""
    End If
    If Me![DivisionFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.DivisionID like """ & DivisionFilter & """"
    End If

    If Me![SubdivisionFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.SubdivisionID like """ & SubdivisionFilter & """"
    End If

    If Me![ContractorFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.ContractorID like """ & ContractorFilter & """"
    End If

    If Me![StartDate] <> "" And [EndDate] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.DateIn between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
    End If

    If Me![TypeFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.TypeID like """ & TypeFilter & """"
    End If

    If Me![HoldFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.Hold like """ & HoldFilter & """"
    End If

    If Me![CanceledFilter] <> "" Then
    sCriteria = sCriteria & " AND qryDWGIDList.Canceled like """ & CanceledFilter & """"
    End If

    If Nz(DCount("*", "qryDWGIDList", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
    sSql = "SELECT DISTINCT [DrawingID], [DesignerID], [DateIn], [DateStarted], [DateFinished], [DateSold], [Hold], [Canceled], [DivisionID], [TypeID], [ProjectName], [LastName], [FirstName], [ContractorID], [SubdivisionID] from qryDWGIDList " & sCriteria
    Forms![frmdwgid]![fsubDWGIDList].Form.RecordSource = sSql
    Forms![frmdwgid]![fsubDWGIDList].Form.Requery
    Else
    MsgBox "The search failed find any records" & vbCr & vbCr & _
    "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
    End If


    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It looks like you are using the query for the RecordSource of a form, if that is true then try using the form's Order By property.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Alternatively, after your last condition statement, add your ORDER BY to the criteria string ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Oct 2004
    Location
    Grand Junction, Colorado
    Posts
    12
    Thanks I think that got it.

    I apprecate the suggestions!

Posting Permissions

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