Problems with Sorting a query with subqueries

[Karja]

Goodmorning,

I've got a query where some fields are the result of a subquery. The query that causes me trouble is:

SQLs = "SELECT SecurityCode, Holding, iif([TypeSubCode] Like '161*', 0, 1) As TypeOption, " & _
       "(SELECT Value FROM G_MarketData_BB WHERE G_MarketData_GP.Bloomberg = G_MarketData_BB.SecurityCode AND TypeOfData = 'OptExcer') As OptExcer,  " & _
       "(SELECT Value FROM G_MarketData_BB WHERE G_MarketData_GP.Bloomberg = G_MarketData_BB.SecurityCode AND TypeOfData = 'Moeder') As Moeder  " & _
       "FROM G_MarketData_GP " & _
       "WHERE [FundCode] = " & codeFonds & " AND [G_MarketData_GP.Date] = #" & Format(dateHisinv, "yyyy-mm-dd") & "# AND " & _
       "[Type] = 'OPTI' AND [Holding] < 0 " & _
       "ORDER BY TypeOption;"
Set rsShort = CurrentDb.OpenRecordset(SQLs)

The problem resides in the ORDER BY clause. It seems impossible to sort on the field [TypeOption], or [Moeder]. The system gives the following error:

runtime error 3061: Two few parameters. Expected 1

When I replace [TypeOption] with [Holding] the sorting clause works fine. Perhaps someone can tell me what I am doing wrong or answer one of my questions:
Q: is it impossible to sort on a field that is the result of a subquery (i.e. Moeder, OptExcer)?
Q: is it impossible to sort on a field that is the result of a VBA function (i.e. TypeOption)?

Any help appreciated!
Regards, Karja

 

[dhookom]

Try set your ORDER BY to something like:
ORDER BY 5

I think you could also repeat the subquery in the ORDER BY clause.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

In fact you may sort on the ordinal position of the column.

 

[Remou]

As far as I recall, you need to sort by iif([TypeSubCode] Like '161*', 0, 1) rather than the alias.

 

[Karja]

All thanks for your answer.
When I sort on the subquery or iif([TypeSubCode] Like '161*', 0, 1) I get a syntax error.

PHV, what do you mean by "In fact you may sort on the ordinal position of the column". I am afraid I do not understand it.

Regards,
Karja

 

[dhookom]

Karja,
The ordinal position is my first suggestion. Did you try it?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

TypeOption is the 3rd column in the resultset, so:
ORDER BY 3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Karja]

dhookom, yes now I've tried your initial suggestion. First I was not completely sure what you meant. It WORKS! Thank you all!