SQL prompts for parameter on calculated field

[gamisens]

I am trying to program a button to sort a listbox by changing the SQL rowsource. I saw a post by you suggesting this technique. My initial SQL is:

SELECT AssayID, AssayName, DLookUp('[Value]','ValueList_Items','[ItemID] =' & SeqTypeID) AS Type, GeneSymbol, GenomicLocation AS Location FROM Assays Where [SeqTypeID] =3

Notice the Dlookup calculated field in the SELECT statement. My code adds the ORDERBY part to the SQL and reassigns it to the listbox rowsource:

*********************************************************************************************************************
Function SortBy(I As Integer) ‘ I specifies the button clicked
On Error GoTo Err_SortBy

Dim strFieldName As String, strSortOrder As String, strSQL As String
Dim J As Integer

strOriginalSQL = Me.lstSelect.Tag ' holds the SQL without any orderby. strOriginalSQL is a private ‘ module variable
strFieldName = Me.lstSelect.Column(I, 0) ' Column Head holds the field name to sort by

For J = 1 To 6 ' There are 6 sort buttons
If J = I Then ' I identifies the button clicked
Select Case Me("cmdOrder" & I).Caption
Case "DESC", vbNullString
strSortOrder = "ASC"
Me("cmdOrder" & J).Caption = "ASC"
Case "ASC"
strSortOrder = "DESC"
Me("cmdOrder" & J).Caption = "DESC"
End Select
Else
Me("cmdOrder" & J).Caption = vbNullString
End If
Next J

strSQL = strOriginalSQL & " ORDER BY " & strFieldName & " " & strSortOrder

With Me!lstSelect
.RowSource = strSQL
.Requery
.SetFocus
End With

Exit_SortBy:
Exit Function

Err_SortBy:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in SortBy"
Resume Exit_SortBy

End Function
**********************************************************************************************************************

The initial query works fine and sorting on all the fields works fine but when I try to sort on the calculated field, my form prompts me for a parameter for “Type” even though it is a calculated field in my query. My question is, why is this happening and how can it be avoided?

Thank you very much, any help would be appreciated.

Tamar.

 

[jebry]

Hi Tamar!

You can't sort on an alias such as Type. You need to repeat the DLookUp statement in the Order By clause.

hth Jeff Bridgham
bridgham@purdue.edu

 

[gamisens]

Hello Jeff,

Thank you for your reply. My listbox is unbound and can accept several queries with different calculated alias fields in different columns. Do you think there is a way I can still have this sort button functionality implemented somehow? I need the function to be able to recognize if it is a calculated field and build it into the ORDERBY clause.

Thank you Tamar.