Report Sorting Issue

[cbgroves]

Hey everyone,

I am relatively new to Access and have been setting up a pretty simple database to track some things at work. I have been able to figure most everything out except a sorting issue in a report that I need. I am sorting by semester, ie spring 2011, summer 2011, fall 2011, spring 2012 ect. My problem is I need them to go in that order, by the academic calendar. I cannot figure out how to sort by anything other than alphabetically and I do not want to add random characters to make it sort correctly. Can what I want to do be done?

Thanks!



Calvin

[hertfordkc]

Hey everyone,

I am relatively new to Access and have been setting up a pretty simple database to track some things at work. I have been able to figure most everything out except a sorting issue in a report that I need. I am sorting by semester, ie spring 2011, summer 2011, fall 2011, spring 2012 ect. My problem is I need them to go in that order, by the academic calendar. I cannot figure out how to sort by anything other than alphabetically and I do not want to add random characters to make it sort correctly. Can what I want to do be done?

Thanks!

Calvin

Add a calculated field to the query on which your report is based.
Assuming that you have the semester already present, use an iif statement to enumerate the semester information in the desired order, i.e. 20111, 20112, 20113 or whatever scheme makes sense to you. Then sort on it. You don't need to print it on the report, although it may have to be hidden in the detail somewhere.

[cbgroves]

Would it be possible for you to write me an example of how this would look? I would greatly appreciate it!

[hertfordkc]

Assuming that the query already contains a column 'Semester', (I'm abbreviating as SM to make this more readable), go to a blank column and insert the following:

SortFld:iif( INSTR(0,[SM],"Winter",),Right([SM],4)&"1", iif(INSTR(0,[SM],"Spring",),Right([SM],4)&"2",iif( INSTR(0,[SM],"Summer",),Right([SM],4)&"3",Right([SM],4)&"4" ) ) )

All on one line.
The above coding could be shortened to
SortFld:iif( INSTR(0,[SM],"W",),Right([SM],4)&"1", iif(INSTR(0,[SM],"Sp",),Right([SM],4)&"2",iif( INSTR(0,[SM],"Su",),Right([SM],4)&"3",Right([SM],4)&"4" ) ) )

Since this hasn't been checked by Access yet, you may expect problems with unbalanced parens, or commas in the wrong spot. Just try to get the gist of the code.
When you run the query, you should get a column of calculated values similar to
20111
20112
20113
20114
20121

If the order of the semesters is something other than winter = 1, spring = 2... then you will need to switch either the test string or the appended string around.
Note that if you have trailing blanks in the list of semesters, you may have to TRIM the variable.

[cbgroves]

Thanks, I think I'm getting close! I am pretty sure I understand the syntax here, but I am getting #Func! as the output for all semesters. I don't know if it makes a difference, but the four semesters a year I am using are: Spring, Summer 4 Week, Summer 8 Week, and Fall. I appreciate the help!

[cbgroves]

Here is the code I have now:

SortFld: IIf(InStr(0,[Semester to Deliver],"Spring"),Right([Semester to Deliver],4) & "1",IIf(InStr(0,[Semester to Deliver],"Summer 4 Week"),Right([Semester to Deliver],4) & "2",IIf(InStr(0,[Semester to Deliver],"Summer 8 Week"),Right([Semester to Deliver],4) & "3",Right([Semester to Deliver],4) & "4")))

Semesters are formatted as (with no spaces before or after): Spring 2011, Summer 8 Week 2011 ect.

[cbgroves]

Evidently I lied. I just linked it to my table and now I get a syntax error (comma) Can you spot my error, because I cant :/

[hertfordkc]

Some of the descriptions show examples with the last parameter and comma omitted, but I think it is necessary.

[cbgroves]

I'm not sure I know how to correct that...

[hertfordkc]

SortFld: IIf(InStr(0,[Semester to Deliver],"Spring",),Right([Semester to Deliver],4) & "1",IIf(InStr(0,[Semester to Deliver],"Summer 4 Week",),Right([Semester to Deliver],4) & "2",IIf(InStr(0,[Semester to Deliver],"Summer 8 Week",),Right([Semester to Deliver],4) & "3",Right([Semester to Deliver],4) & "4")))

The commas are just before the closing parenthesis of each INSTR.

[cbgroves]

I added the three commas you added and I get the same comma error.

[cbgroves]

Actually when I add those commas and try and run the report I get the error, so when I go back to look at the formula again the commas are gone.

[hertfordkc]

guess:

SortFld: IIf(InStr(0,[Semester to Deliver],"Spring",1),(Right([Semester to Deliver],4) & "1"),IIf(InStr(0,[Semester to Deliver],"Summer 4 Week",1),(Right([Semester to Deliver],4) & "2"),IIf(InStr(0,[Semester to Deliver],"Summer 8 Week",1),(Right([Semester to Deliver],4) & "3"),(Right([Semester to Deliver],4) & "4"))))

BTW, I urge you to avoid using spaces and special characters in your names.

If this fails, fall back to seeing if the first test works,i.e. take out the second and third iifs, and narrow done what is failing.

I'll get on my Access box and try this.

[hertfordkc]

SortFld: IIf(InStr(1,[Sem],"Spring",1),(Right([Sem],4) & "1"),IIf(InStr(1,[Sem],"Summer 4 Week",1),(Right([Sem],4) & "2"),IIf(InStr(1,[Sem],"Summer 8 Week",1),(Right([Sem],4) & "3"),(Right([Sem],4) & "4"))))

The start comparison position is '1', not 0.
Apologies.

[cbgroves]

That worked! Thanks so much!