Question SQL ORDER BY Difficulty with syntax

byTimber

Registered User.
Local time
Today, 03:13
Joined
Apr 14, 2012
Messages
97
Can someone rescue me! - 4 days of trying everything and everywhere but defeated at last.

Me.RecordSource = "select * from [tblLVRStatements] where [tblLVRStatements]![clientID] = " & clientIDVar & "ORDER BY [tblLVRStatements]![NumberStatement]"

Above is just one of my many attempts! not to work....

I have a table; tblLVRStatements.

I have an ID for the client; clientID and have a variable;clientIDVar as an identifier.

I have Statements which are in order in the table and wish to display these in the order (from NumberStatement field) in which they appear in the table BUT they display in random order with the following code;

< Me.RecordSource = "select * from [tblLVRStatements] where [tblLVRStatements]![clientID] = " & clientIDVar

Me.OrderBy = Me.[NumberStatement]
Me.OrderByOn = True >

I thought to try and order them with the SQL ORDER BY but my syntax is 'up the spout'! Please!!!!!:confused:
 
Last edited:
Why do you use a bang(!) operator? should you not use the dot(.)?? what do you get if you use the following?
Code:
Forms![COLOR=Blue]yourFormName[/COLOR].Form.RecordSource = "SELECT * FROM [tblLVRStatements] WHERE [tblLVRStatements].[clientID] = " & clientIDVar & " ORDER BY  [tblLVRWrittenStatements].[NumberStatement];"
 
pr2 - I'll tell you what I get:- SUCCESS!!!!!
Thank you so much, and such a fast response. I have wracked my brains for the past 4 days on this and as much as I dislike seeking help, I'm glad you were there...

Many, many thanks,
Roger ...
 
Ha ha.. You are most welcome Roger.. Glad to help.. :)
 
I have a bunch of text/combo boxes on a form that when information is entered into them and the "Ok" button is pressed runs a query:

Code:
Private Sub ok_Click()
'Run query
Dim argCount As Integer

On Error GoTo Err_Handler

If IsNull(creatorcb.Value) And IsNull(assigncb.Value) And IsNull(Combo31.Value) And IsNull(Text10.Value) And IsNull(Text8.Value) And IsNull(Text14.Value) And IsNull(Text16.Value) And IsNull(Text12.Value) And IsNull(toDate.Value) And IsNull(frmDate.Value) Then
    MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
    Exit Sub
End If

If Not IsNull(frmDate.Value) And IsNull(toDate.Value) Then
    MsgBox "Please Specify a Date Range", vbCritical, "Lead Tracking"
    Exit Sub
End If

If Not IsNull(toDate.Value) And IsNull(frmDate.Value) Then
    MsgBox "Please Specify a Date Range", vbCritical, "Lead Tracking"
    Exit Sub
End If

strQuery = "SELECT * From Lead Where "
If Not IsNull(creatorcb.Value) Then
    strQuery = strQuery & "Creator Like '" & creatorcb.Value & "'"
    argCount = argCount + 1
End If

If Not IsNull(assigncb.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "AssignedTo Like '" & assigncb.Value & "'"
    argCount = argCount + 1
End If

If Not IsNull(Text8.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "BorrowerFirstName Like '" & "*" & Text8.Value & "*" & "'"
    argCount = argCount + 1
End If

If Not IsNull(Text10.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "BorrowerLastName Like '" & "*" & Text10.Value & "*" & "'"
    argCount = argCount + 1
End If

If Not IsNull(Text12.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "Company Like '" & "*" & Text12.Value & "*" & "'"
    argCount = argCount + 1
End If

If Not IsNull(Text16.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "PropertyCity Like '" & "*" & Text16.Value & "*" & "'"
    argCount = argCount + 1
End If

If Not IsNull(Text14.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "PropertyState Like '" & Text14.Value & "'"
    argCount = argCount + 1
End If

If Not IsNull(Combo31.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "Status Like '" & Combo31.Value & "'"
    argCount = argCount + 1
End If

If Not IsNull(frmDate.Value) And Not IsNull(toDate.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "InputDate" & " Between " & "#" & frmDate.Value & "#" & " AND " & "#" & toDate.Value & "#"
    argCount = argCount + 1
End If

DoCmd.OpenForm "GridDisplay1", acNormal, , , acFormReadOnly, acDialog
  
Exit_Handler:
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case 2105
                MsgBox "Please Modify Your Search And Try Again", vbOKOnly, "Lead Tracking"
        Case 2501
        Case Else
                MsgBox "Error " & Err.Number & " " & Err.Description
        End Select
        Resume Exit_Handler
    
End Sub

Notice the code opens up a form, "GridDisplay1" and in the form's OnOpen event the recordsource is set:

Code:
Private Sub Form_Open(cancel As Integer)
'---- SQL comes from Form_GUI.strQuery ----
On Error GoTo Err_Handler

Me.RecordSource = Forms!GUI.strQuery

Me.cursorlock.SetFocus

Exit_Handler:
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case 2105
                MsgBox "No Records Match Your Search", vbOKOnly, "Lead Tracking"
                cancel = True
        Case Else
                MsgBox "Error " & Err.Number & " " & Err.Description
        End Select
        Resume Exit_Handler

End Sub

I would like to order these results by "InputDate" and group them by "PriorityLevel" which are fields also from the [Lead] table. But no matter where I put the language to do so (either after the recordsource is set or after the "where" phrase in the query) it doesn't work.
 
Hello matt164, Why not pass the Query as an OpenArgs,
Code:
DoCmd.OpenForm "GridDisplay1", acNormal, , , acFormReadOnly, acDialog, OpenArgs:= strQuery
and assign that in the Open event?
Code:
Me.RecordSource = Me.OpenArgs

PS: It would be best to start a New thread, next time?
 
I'm sorry I thought my question was relevant enough to the topic thread..next time I will start new. Even if I use the openArgs where would I insert the OrderBy / GroupBy ? In the code for "strQuery" ? Thanks
 
Oh okay, I thought the OrderBy and GroupBy is already applied in the strQuery.. Just add that as the last line.. Before Opening the Form..
Code:
strQuery = strQuery & " ORDER BY InputDateFieldName"
Just a word of caution.. Order by is not a problem, but when you use Group by the Form will not be updatable..
 

Users who are viewing this thread

Back
Top Bottom