Sort a report ascending or descending, based on a control

[TOTCOM11]

Alrighty,

Basically what I have is a database that I am using to keep track of an offline Fantasy Football draft. I have a form that sorts the teams ascending when the number of the draft round is odd (such as Round 1, Round 3, etc...) and it sorts descending when the round is even. My reasoning is whoever ends the first round, starts the second round. Like I said, my form works perfectly, but when I try to run a report, the same logic doesn't work quite so well. Here is the code that I have on my form:

Private Sub Form_Current()
Me.OrderByOn = True
If Me.MOD = 1 Then
Me.OrderBy = "DraftOrder"
Else
Me.OrderBy = "DraftOrder DESC"
End If
End Sub

What's happening here is the control [MOD] is basically the draft round Mod 2. For those of you that don't know, Mod is a function that stores the remainder. Since my variable is an integer, if a 0 gets returned, that means the round is even and if it returns a 1, the round is odd.

I would like to know if I can sort the detail section of my form this same way.

Thanks,
TOTCOM11

 

[dhookom]

Couldn't you set up your sorting and grouping expression to be
[Round]
=IIf([Round] Mod 2=1,[DraftOrder],-[DraftOrder])

Duane
MS Access MVP

 

[TOTCOM11]

That doesn't seem to work. My grouping and sorting properties are as follows.

Round Ascending
DraftOrder Ascending
=IIF([Round] Mod 2=1,[DraftOrder],-[DraftOrder]) Ascending

This displays no changes. Round 1 is in ascending order, but round 2 is also in ascending order. I would like all even rounds to be in descending order. Am I doing something wrong?

 

[dhookom]

Remove DraftOrder as per my earlier posting:
[Round]
=IIf([Round] Mod 2=1,[DraftOrder],-[DraftOrder])

Duane
MS Access MVP

 

[TOTCOM11]

If I do that, I get the following error:

"You tried to execute a query that does not include the specified expression 'DraftOrder' as part of an aggregate function."

What does this mean?

 

[dhookom]

This sounds like a query issue. Are you modifying the Sorting and Grouping dialog or doing something with the report's record source?

Duane
MS Access MVP

 

[TOTCOM11]

Not that I am aware of. The record source is just a query that is taken directly from a couple of tables.

 

[TOTCOM11]

Wait, I just checked my query and I found out that I had some calculated fields that really were not important. So I got rid the calculation and it works! Thanks so much for your help!