Query: Too complex to be evaluated

[mbazdell]

So it appears that this query can't resolve the last variable of [TempVars]![SortOrderBy] but if I were to change that to just WorkOrder for example, it works fine.

SELECT ticketqueue.ID, ticketqueue.WorkOrder, ticketqueue.Details, ticketstatus.StatusName, ticketpriority.PriorityName, ticketcategory.CategoryName, clients.Company, Format(ticketqueue.OpenDate,"mm/dd/yy hh:nn") AS OpenDate, Format(ticketqueue.UpdateDate,"mm/dd/yy hh:nn") AS UpdateDate, IIf(IsNull(U1.Alias),U1.FirstName,U1.Alias) AS OpenedBy, IIf(IsNull(U3.Alias),U3.FirstName,U3.Alias) AS UpdatedBy, IIf(IsNull(U2.Alias),U2.FirstName,U2.Alias) AS AssignedTo, ticketsource.SourceName, ticketqueue.Lot
FROM (((((((ticketqueue LEFT JOIN ticketsource ON ticketqueue.Source = ticketsource.ID) LEFT JOIN ticketstatus ON ticketqueue.Status = ticketstatus.ID) LEFT JOIN clients ON ticketqueue.Client = clients.ID) LEFT JOIN ticketcategory ON ticketqueue.Category = ticketcategory.ID) LEFT JOIN users AS U1 ON ticketqueue.OpenedBy = U1.ID) LEFT JOIN users AS U2 ON ticketqueue.AssignedTo = U2.ID) LEFT JOIN ticketpriority ON ticketqueue.Priority = ticketpriority.ID) LEFT JOIN users AS U3 ON ticketqueue.UpdatedBy = U3.ID
WHERE (((ticketstatus.ID)=1) AND ((ticketqueue.Details) Is Not Null))
ORDER BY IIf([TempVars]![SortOpenBy]="ticketqueue.ID",ticketqueue.ID,[TempVars]![SortOpenBy]);

I tried doing

Forms![OpenFormName]![UnboundTextBoxName]

And that doesn't work either. What I'm trying to do is have it do the ORDER BY dynamically based on a variable....

 

[mbazdell]

Okay I did a bit more messing around and figured out that if I do

ORDER BY [TempVars]![SortOpenBy];

It doesn't give the error, but it's also not sorting at all. In the debug I do

print [TempVars]![SortOpenBy]

Which returns a perfectly valid field. If I type it in manually instead of using the variable it works properly. Just to be clear I am doing a Requery after the TempVar is set. Is there a better way to do this?

 

[jdraw]

Show us your tables and relationships as a jpg.

What exactly are you trying to do?

 

[mbazdell]

I'd post the table layout but the query is too complicated for it to display graphically. It's not that complicated though. Most of it is just tables replacing ID's from the main table. As I said in the first, I'm trying to do is use ORDER BY with a variable. I can get the variable to fill out properly, but when I use the variable in the query it doesn't seem to actually work. But if I change the variable with the value of the variable in the query it works fine.