SQL Sorting with Variables

[Tatakau]

I am trying to sort entries based off of arguments passed into a form (tours)
by another form (customers) with SQL. When the form opens, it should sort by
matching customer IDs first, with a secondary descending sort by a Last
Edited field in the Tours form.

I found some SQL that I've been trying to tweak. No luck so far:

Private Sub Form_Open(Cancel As Integer)
'Dim strSQL As String
'If Not IsNull(Me.OpenArgs) Then
'strSQL = "SELECT * FROM [tours] ORDER BY " & "IIF([leadnum] = " &
Me.OpenArgs & ", leadnum, 2), tournum"
'Me.RecordSource = strSQL
'End If
End Sub

Thanks,

Nick

 

[JohnFol]

Not sure if you mean it needs to match the customer ID or use it in the
sort. Either way, the select statement you need is either

"SELECT * FROM [tours] ORDER BY customerID, LastEdited desc"

or

"SELECT * FROM [tours] where customerID = " & me.openargs & " order by
LastEdited desc"

 

[Tatakau]

I'm having some sorta issue with running SQL in my form for some reason.
Nothing I try seems to work! Not even a general select statement:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
If Not IsNull(Me.OpenArgs) Then
strSQL = "SELECT * FROM tours"
Me.RecordSource = strSQL
End If
End Sub

I always get this error:

Run-time error '2580': The record source 'SELECT * FROM tours' specified on
this form or report does not exist.

No matter what SQL statement I run, I get an error like that. I'm using all
of the basic libraries (VB for Apps, MS Access 11.0 Obj Lib, OLE Auto, MS DAO
3.6 Obj Lib, and MS ActiveX Data Obj 2.5 Lib), so I don't think I'm missing
anything there... any suggestions?

Thanks!

Nick

Not sure if you mean it needs to match the customer ID or use it in the
sort. Either way, the select statement you need is either

"SELECT * FROM [tours] ORDER BY customerID, LastEdited desc"

or

"SELECT * FROM [tours] where customerID = " & me.openargs & " order by
LastEdited desc"

I am trying to sort entries based off of arguments passed into a form
(tours)
by another form (customers) with SQL. When the form opens, it should sort
by
matching customer IDs first, with a secondary descending sort by a Last
Edited field in the Tours form.

I found some SQL that I've been trying to tweak. No luck so far:

Private Sub Form_Open(Cancel As Integer)
'Dim strSQL As String
'If Not IsNull(Me.OpenArgs) Then
'strSQL = "SELECT * FROM [tours] ORDER BY " & "IIF([leadnum] = " &
Me.OpenArgs & ", leadnum, 2), tournum"
'Me.RecordSource = strSQL
'End If
End Sub

Thanks,

Nick

 

[Tatakau]

Erm... I sorta *coughmisspelledcough* something... wow I feel stupid. It
works now!

Nick

I'm having some sorta issue with running SQL in my form for some reason.
Nothing I try seems to work! Not even a general select statement:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
If Not IsNull(Me.OpenArgs) Then
strSQL = "SELECT * FROM tours"
Me.RecordSource = strSQL
End If
End Sub

I always get this error:

Run-time error '2580': The record source 'SELECT * FROM tours' specified on
this form or report does not exist.

No matter what SQL statement I run, I get an error like that. I'm using all
of the basic libraries (VB for Apps, MS Access 11.0 Obj Lib, OLE Auto, MS DAO
3.6 Obj Lib, and MS ActiveX Data Obj 2.5 Lib), so I don't think I'm missing
anything there... any suggestions?

Thanks!

Nick

Not sure if you mean it needs to match the customer ID or use it in the
sort. Either way, the select statement you need is either

"SELECT * FROM [tours] ORDER BY customerID, LastEdited desc"

or

"SELECT * FROM [tours] where customerID = " & me.openargs & " order by
LastEdited desc"

I am trying to sort entries based off of arguments passed into a form
(tours)
by another form (customers) with SQL. When the form opens, it should sort
by
matching customer IDs first, with a secondary descending sort by a Last
Edited field in the Tours form.

I found some SQL that I've been trying to tweak. No luck so far:

Private Sub Form_Open(Cancel As Integer)
'Dim strSQL As String
'If Not IsNull(Me.OpenArgs) Then
'strSQL = "SELECT * FROM [tours] ORDER BY " & "IIF([leadnum] = " &
Me.OpenArgs & ", leadnum, 2), tournum"
'Me.RecordSource = strSQL
'End If
End Sub

Thanks,

Nick