order by dlookup not working

[broof]

Hi,


I have a qry with multiple columns. The first column contains the name of one of the columns in my query. I need to sort the results on the column (name) contained in the first column.

my query looks something like this:
SELECT qry1.qry_sort_col, qry2.qry_location, qry2.qry_num_part, qry3.qry_score, qry3.qry_count
FROM "multiple qrys joined here"
WHERE qry_location is not null
Order By (dlookup("qry_sort_col","qry1"))

the first column contains the column name qry_location which means that I am trying to sort the result set on qry2.qry_location in asc order

If I type Order By qry2.qry_location - everything works great

Problem - I don't know ahead of time what will be in the first column (the user gets to select the field they want the reslts sorted by) so I need to pull the column name out of the first column in my qry. I thought I could use DLookup to do this. I can show in the result set that I am returning the column name with the dlookup call but the sort will not work.

Thanks for any ideas!

[pbaldy]

Try

Order By 1

to sort on the first field.

[broof]

Thanks, but I don't want to sort by column 1, I want to sort by the column name stored in column 1. Which is why I am trying to get that value out in the Order by clause. I also tried an iif statement, if not null, return the value. I can see that both the dlookup and iif are returning the name of the column I want to sort by, but the sort is not working.

Column 1 contains the name "column 4" and I want to sort the results by column 4. If I type in Column 4, works great but if I try to get the name from column 1, it won't work.

Can you think of anything else I could try?

[broof]

It seems to work if I do this:
ORDER BY IIf(qry1.qry_sort_col = "qry2.qry_location", qry2.qry_location,iif(qry1.qry_sort_col = "qry2.qry_num_part",qry2.qry_num_part,iif(qry1.qry _sort_col = "qry3.qry_Score",qry3.qry_score,0)));

But how many iif's can I embedd in an iif before access will not work?

Would a case statement be better?