If Else in ms access query

[WickidWe]

I have a query that is part reliant on a Selected item in a combobox
I need to add to the query if [forms]![form1]![cboFunction] = 1 sort FGrp(Function group) equal to 1 in desending order


else sort FGroup in assending order.

[June7]

Never seen this so just tried a dynamic ORDER BY clause in a query object. Doesn't work.

[Dal Jeanis]

1) If your function group is a numeric field, then you can create a sortgroup field that is 1*functiongroup if you want it ascending, or is -1*sortgroup if you want it descending.

2) If you build your SQL in VBA, then you can add the DESC if the control says to do so.

[June7]

Okay, that works. Can't conditionally select the DESC or ASC parameter but can do the math. Example:

SELECT FAAID FROM Airports ORDER BY ID * [enter value 1 for ascending or -1 for descending] ASC;

This just clarified for me that SQL keywords cannot be conditional in Access query object.

[WickidWe]

Sorry i Gave you slightly wrong information. Instead of the function group = 1
the details are as follows.
If the Function ID (from the combobox) = 1
order by function group count where the group function =1 (which is in a query) Assending
and where the Function ID is not 1 order by the same group count desending

does this make any sense?

[June7]

Not really but doesn't matter if I understand. Apparently the only way to get this to work in a query object referencing combobox is if there is a numeric field that can be multiplied by 1/-1 to affect the sort order.

ORDER BY [some field name] * IIf([Forms!formname!comboboxname]=1,1,-1) ASC

[WickidWe]

I tried to input into my query as you have above but was coming up with syntax error below is my original query without your code.

SELECT qryAvailable.*, qryPickCount.pick AS Pick, qryFctnCount.Fctn AS Fctn, qryGrpCount.FGrp AS FGrp
FROM ((qryAvailable LEFT JOIN qryPickCount ON qryAvailable.Name = qryPickCount.name) LEFT JOIN qryGrpCount ON qryAvailable.Name = qryGrpCount.Name) LEFT JOIN qryFctnCount ON qryAvailable.Name = qryFctnCount.name
ORDER BY qryPickCount.pick, qryAvailable.Name;

can you please let me know where to input your code.

sorry if it seems silly but I am just a newbie to Access

[June7]

I was neglecting some [].

This is the only thing that makes sense to me.

ORDER BY qryPickCount.pick * IIf([Forms]![form1]![cboFunction]=1,1,-1), qryAvailable.Name

[WickidWe]

Great! that worked!