syntax error missing operator in query expression

[TOPSie]

My apologies for posting this it is probably trivial but I just can't see what is wrong here:



I have a form with Record Source with the following clause:

ORDER BY tblSalesTransactions.Date, tblSalesTransactions.SalesTransactionNumber, tblSalesTransactions.AmountGross;

The user want one particular account to sort differently. So I have written a function:

Public Function IsSuspenceAccount(ID As Long) As Boolean
If ID = 2840 Then
    IsSuspenceAccount = True
Else
    IsSuspenceAccount = False
End If
End Function

and changed the ORDER BY clause

ORDER BY IIf(IsSuspenceAccount([tblSalesTransactions.CustomerID])=TRUE, [tblSalesTransactions.Date] DESC, [tblSalesTransactions.Date]), 
tblSalesTransactions.SalesTransactionNumber, tblSalesTransactions.AmountGross;

But it fails "syntax error" quoting the IIf statement.
Probably missing a comma or bracket = but I have tried various combinations.
So wondering is you can have an IIF in the ORDER BY Clause at all????

[Welshgasman]

I think you are going to have to build that SQL string using that logic first, then run that SQL.

[TOPSie]

Thank you. Yes updating the form RecordSource in code did the trick :-)