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:
Code:
ORDER BY tblSalesTransactions.Date, tblSalesTransactions.SalesTransactionNumber, tblSalesTransactions.AmountGross;
The user want one particular account to sort differently. So I have written a function:
Code:
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
Code:
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????