RE: ODBC Order by question

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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
.

Relevant Pages

  • Re: Is it possible...
    ... Dim dbs As DAO.Database ... Dim strSQL As String ... EXCEL file in the same order as the fields in the permanent table. ...
    (microsoft.public.access.externaldata)
  • Re: Parsing out SQL statement
    ... > variable called strSQL. ... Dim strTemp as String ... ' prepare to save the new QueryDef ...
    (comp.databases.ms-access)
  • RE: ODBC Order by question
    ... "Fysh" wrote: ... Dim dbs As Database ... Dim strSQL As String ... Dim qdfNew As QueryDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: Different BE database uniformity.
    ... Probably refine it to have all the info for all the DBs in TblCurrent with a pointer to which DB they come from. ... BEs are all different Clubs e.g. Sailing Club, Yacht Club, Bridge Club, Family etc. ... Dim dbsExternal As Database ... Dim strSQL As String ...
    (comp.databases.ms-access)
  • Re: Crosstab Parameters
    ... Dim dbs As DAO.Database ... Dim rst As DAO.Recordset ... Dim strSql As String ... Set dbs = CurrentDb ...
    (microsoft.public.access.modulesdaovba)