Union/Crosstab Query with User Parameter Entry

Question

Hello,

So from some previous assistance I now have a Union/Crosstab Query where I can enter a parameter. This does work for the query. Can this also be utilized to run an associated report? When I attempt to run the report based on the query, I receive the following...

Attached is a text file with the SQL.
180254-select.txt

Answer 1

The IN operator does not accept a parameter as its argument. It must be a literal expression. However, many years ago Microsoft published a couple of functions which simulate the use of the IN operator with a parameter:

Function GetToken(stLn, stDelim)

Dim iDelim As Integer, stToken As String

iDelim = InStr(1, stLn, stDelim)

If (iDelim <> 0) Then
    stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
    stLn = Mid$(stLn, iDelim + 1)
Else
    stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
    stLn = ""
End If

GetToken = stToken

End Function

'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'============================================================
Function InParam(Fld, Param)

Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)

If IsNull(Fld) Then Fld = ""
    Do While (Len(Param) > 0)
    stToken = GetToken(Param, ",")
    If stToken = LTrim$(RTrim$(Fld)) Then
        InParam = -1
        Exit Function
    Else
        InParam = 0
    End If
Loop

End Function

The syntax for calling the InParam function in a query is:

WHERE InParam([ColumnName],[Parameter]) = TRUE

Answer 2

You cannot include an aggregate function in a query's ORDER BY clause, but a query used as a report's RecordSource property should not be ordered in any case. It should be done by means of the report's internal sorting and grouping mechanism, so you can sort the report in descending order by the Total Of TotalAmount column in the normal way.

Answer 3

Thanks Ken! This report looks great.

As a second option, is it possible to do an ORDER BY Total Of TotalAmount DESC?

Answer 4

PS: I notice you say it works in the query. The only way I can think of that it would is that only a single value is entered as the parameter, which makes the use of the IN operator pointless as the equality operator, =, could be used more efficiently.