ODBC Order by question

F

Fysh

I created a database a while back for various sections within our facility.
Several of the forms recordsources are generated from selections of a couple
combo boxes. The users can then set the order of the records by selecting
from several option boxes and it requeries the form. I use a case select to
set the Order By for the select statement. This all worked fine until I
migrated the backend to SQL Server 2000. For some reason when ever I select
the option box I get a 3146 error. Everything else works fine except this
one item.

The option
Is there a way through ODBC to do an ORDER BY if field equals a certain
value? here is my code. ANy help would be appreciated. The problem is in
the FRAME1 section.

Private Sub UpdateForm()
On Error GoTo Err_UpdateForm
Dim dbs As Database
Dim strSQL As String
Dim qdfNew As QueryDef
Dim strOrderBy As String

If IsNull(Me.SquadID) = False And IsNull(Me.DeployID) = False Then
Set dbs = CurrentDb()

With dbs
' Delete QueryDef
.QueryDefs.Delete "qPCOClearance"
End With

If Me.SquadID > 0 Then
strSQL = "SELECT qryPCOClearance.* FROM [qryPCOClearance]"
strSQL = strSQL & "WHERE (qryPCOClearance.DeployID =" &
Me!DeployID & " and qryPCOClearance.Squadron =" & Me!SquadID.Column(1) & ")"
strSQL = strSQL & "ORDER BY Last4, SSAN"
ElseIf Me.SquadID = 0 Then
strSQL = "SELECT qryPCOClearance.* FROM [qryPCOClearance]"
strSQL = strSQL & "WHERE (qryPCOClearance.DeployID =" &
Me!DeployID & ")"
strSQL = strSQL & "ORDER BY Last4, SSAN"
End If

Me.cmdSSAN.Visible = True
Me.cmdRank.Visible = True
Me.cmdFName.Visible = True
Me.cmdLName.Visible = True
Me.cmdCleared.Visible = True
Me.cmdPHA.Visible = True
Me.cmdProfile.Visible = True

With dbs
' Create QueryDef.
Set qdfNew = .CreateQueryDef("qPCOClearance", strSQL)
Me.Text10.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 0)
Me.Text12.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 1)
Me.Text14.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 2)
Me.Text16.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 3)
Me.Text20.Value = DCount("*", "qPCOClearance")
End With


If Frame1 > 0 Then
Select Case Frame1 ' Evaluate Number.
Case 1
strOrderBy = "PCOCrit=0, Last4, SSAN"
Case 2
strOrderBy = "PCOCrit=1, Last4, SSAN"
Case 3
strOrderBy = "PCOCrit=2, Last4, SSAN"
Case 4
strOrderBy = "PCOCrit=3, Last4, SSAN"
Case Else ' Other values.
End Select
Form.RecordSource = "Select * from qPCOClearance ORDER BY " & strOrderBy
Else
Form.RecordSource = strSQL
End If
Me.txtTime = Now()
Form.Requery
Form.Refresh
Else
End If

Exit_UpdateForm:
Exit Sub
Err_UpdateForm:
If Err = 3265 Then
Err = 0
Resume Next
Else
MsgBox Err.Description
Resume Exit_UpdateForm
End If

End Sub
 
F

Fysh

I was hoping to get a reply on this. Anyway I have tried several things to
get this to work, without any luck. Is there a way to accomplish this as is
or something similar to MySQL FIELD()? Any help on this matter would be
greatly apprecitiated. I am using Access 2003 with SQL Server 2000.



Fysh said:
I created a database a while back for various sections within our facility.
Several of the forms recordsources are generated from selections of a couple
combo boxes. The users can then set the order of the records by selecting
from several option boxes and it requeries the form. I use a case select to
set the Order By for the select statement. This all worked fine until I
migrated the backend to SQL Server 2000. For some reason when ever I select
the option box I get a 3146 error. Everything else works fine except this
one item.

The option
Is there a way through ODBC to do an ORDER BY if field equals a certain
value? here is my code. ANy help would be appreciated. The problem is in
the FRAME1 section.

Private Sub UpdateForm()
On Error GoTo Err_UpdateForm
Dim dbs As Database
Dim strSQL As String
Dim qdfNew As QueryDef
Dim strOrderBy As String

If IsNull(Me.SquadID) = False And IsNull(Me.DeployID) = False Then
Set dbs = CurrentDb()

With dbs
' Delete QueryDef
.QueryDefs.Delete "qPCOClearance"
End With

If Me.SquadID > 0 Then
strSQL = "SELECT qryPCOClearance.* FROM [qryPCOClearance]"
strSQL = strSQL & "WHERE (qryPCOClearance.DeployID =" &
Me!DeployID & " and qryPCOClearance.Squadron =" & Me!SquadID.Column(1) & ")"
strSQL = strSQL & "ORDER BY Last4, SSAN"
ElseIf Me.SquadID = 0 Then
strSQL = "SELECT qryPCOClearance.* FROM [qryPCOClearance]"
strSQL = strSQL & "WHERE (qryPCOClearance.DeployID =" &
Me!DeployID & ")"
strSQL = strSQL & "ORDER BY Last4, SSAN"
End If

Me.cmdSSAN.Visible = True
Me.cmdRank.Visible = True
Me.cmdFName.Visible = True
Me.cmdLName.Visible = True
Me.cmdCleared.Visible = True
Me.cmdPHA.Visible = True
Me.cmdProfile.Visible = True

With dbs
' Create QueryDef.
Set qdfNew = .CreateQueryDef("qPCOClearance", strSQL)
Me.Text10.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 0)
Me.Text12.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 1)
Me.Text14.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 2)
Me.Text16.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 3)
Me.Text20.Value = DCount("*", "qPCOClearance")
End With


If Frame1 > 0 Then
Select Case Frame1 ' Evaluate Number.
Case 1
strOrderBy = "PCOCrit=0, Last4, SSAN"
Case 2
strOrderBy = "PCOCrit=1, Last4, SSAN"
Case 3
strOrderBy = "PCOCrit=2, Last4, SSAN"
Case 4
strOrderBy = "PCOCrit=3, Last4, SSAN"
Case Else ' Other values.
End Select
Form.RecordSource = "Select * from qPCOClearance ORDER BY " & strOrderBy
Else
Form.RecordSource = strSQL
End If
Me.txtTime = Now()
Form.Requery
Form.Refresh
Else
End If

Exit_UpdateForm:
Exit Sub
Err_UpdateForm:
If Err = 3265 Then
Err = 0
Resume Next
Else
MsgBox Err.Description
Resume Exit_UpdateForm
End If

End Sub
Click to expand...
 
F

Fysh

Never mind just figured it out. Use Switch! Ye

Sometimes somethings can make you just scratch your head..

Fysh said:
I was hoping to get a reply on this. Anyway I have tried several things to
get this to work, without any luck. Is there a way to accomplish this as is
or something similar to MySQL FIELD()? Any help on this matter would be
greatly apprecitiated. I am using Access 2003 with SQL Server 2000.



Fysh said:
I created a database a while back for various sections within our facility.
Several of the forms recordsources are generated from selections of a couple
combo boxes. The users can then set the order of the records by selecting
from several option boxes and it requeries the form. I use a case select to
set the Order By for the select statement. This all worked fine until I
migrated the backend to SQL Server 2000. For some reason when ever I select
the option box I get a 3146 error. Everything else works fine except this
one item.

The option
Is there a way through ODBC to do an ORDER BY if field equals a certain
value? here is my code. ANy help would be appreciated. The problem is in
the FRAME1 section.

Private Sub UpdateForm()
On Error GoTo Err_UpdateForm
Dim dbs As Database
Dim strSQL As String
Dim qdfNew As QueryDef
Dim strOrderBy As String

If IsNull(Me.SquadID) = False And IsNull(Me.DeployID) = False Then
Set dbs = CurrentDb()

With dbs
' Delete QueryDef
.QueryDefs.Delete "qPCOClearance"
End With

If Me.SquadID > 0 Then
strSQL = "SELECT qryPCOClearance.* FROM [qryPCOClearance]"
strSQL = strSQL & "WHERE (qryPCOClearance.DeployID =" &
Me!DeployID & " and qryPCOClearance.Squadron =" & Me!SquadID.Column(1) & ")"
strSQL = strSQL & "ORDER BY Last4, SSAN"
ElseIf Me.SquadID = 0 Then
strSQL = "SELECT qryPCOClearance.* FROM [qryPCOClearance]"
strSQL = strSQL & "WHERE (qryPCOClearance.DeployID =" &
Me!DeployID & ")"
strSQL = strSQL & "ORDER BY Last4, SSAN"
End If

Me.cmdSSAN.Visible = True
Me.cmdRank.Visible = True
Me.cmdFName.Visible = True
Me.cmdLName.Visible = True
Me.cmdCleared.Visible = True
Me.cmdPHA.Visible = True
Me.cmdProfile.Visible = True

With dbs
' Create QueryDef.
Set qdfNew = .CreateQueryDef("qPCOClearance", strSQL)
Me.Text10.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 0)
Me.Text12.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 1)
Me.Text14.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 2)
Me.Text16.Value = DCount("*", "qPCOClearance", "PCOCrit=" & 3)
Me.Text20.Value = DCount("*", "qPCOClearance")
End With


If Frame1 > 0 Then
Select Case Frame1 ' Evaluate Number.
Case 1
strOrderBy = "PCOCrit=0, Last4, SSAN"
Case 2
strOrderBy = "PCOCrit=1, Last4, SSAN"
Case 3
strOrderBy = "PCOCrit=2, Last4, SSAN"
Case 4
strOrderBy = "PCOCrit=3, Last4, SSAN"
Case Else ' Other values.
End Select
Form.RecordSource = "Select * from qPCOClearance ORDER BY " & strOrderBy
Else
Form.RecordSource = strSQL
End If
Me.txtTime = Now()
Form.Requery
Form.Refresh
Else
End If

Exit_UpdateForm:
Exit Sub
Err_UpdateForm:
If Err = 3265 Then
Err = 0
Resume Next
Else
MsgBox Err.Description
Resume Exit_UpdateForm
End If

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid Operation on a querydef 2
Calculations slowing forms down - SQL server backend 0
Second Report not printing - whats wrong with the VBA I have?????? 1
Insert not working 2
Wildcard question 2
Top n query using Allen Browne's "ConcatRelated" 5
form keeps calculating 0
Mail Merge into MS Word 1

Top