Using the IIF statement to sort

[bella]

Hi I have very basic SQL knowledge and would appreciate any help.

I have a query which asks users to enter an ID - based on that ID, a Report is generated that gathers student information and produces a subject report for that student. The ID refers to a subject that a particular student is enrolled in. The table that is returned contains a list of every student currently enrolled in that subject (which is what I want), however it is sorted alphabetically.

I want the result set to be sorted alphabetically, but within classes, where a class is a set of about 20 or 30 kids enrolled in a particular subject. So for example - If the ID for Maths 101 is 50. The user types in 50 at the message box promt, and the query returns 150 kids enrolled in Maths 101. But there are 5 class groups in Maths 101. So I need it to be sorted into Maths A, Maths B, Maths C, Maths D and Maths E, and then Alphabetically within each group.

The original student data is stored in a table that has about ten "lines" where a maths class may fall on the school time table. So it searches through Line A, and if ID=50 is not in Line A it searches Line B and so on.

How can I say in SQL "If the ID is in Line A, sort alphabetically by Line A, else If the ID is in Line B, sort alphabetically by Line B else...for 10 possible Lines"

I'd appreciate any help. I also need actual Sytax for how to put the IFF into my SQL window as I am not sure how to do this...

thanks heaps!!!!

Bella

 

[Lister]

I am assuming that you have a button control to run the preview report.
If we mod the code in this button it should sort your problem. I think
The code is for the OnClick event for the Preview Report Control (most likely a button).

Dim stDocName, stLinkCriteria As String
    Dim lnID As Integer
    
    lnID = InputBox("Enter Class ID please")

    stDocName = "Your_Report_Name"
    stLinkCriteria = "[ID_Number_In_The_Report] =" & lnID
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Hope this helps

 

[Lister]

Sorry I should read all of what you wrote before replying. I will get back to you and see if we can sort the sort as well.

 

[namliam]

Iif(Condition,True,False)

Condition would be
[LineA]=50

True would then be the [Field] that contains Math A

False would be another IIF structure to 'catch' LineB etc....

Hope it helps if even just a little