Help on Sorting a Report using VBA

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ARC
    #1

    Help on Sorting a Report using VBA

    I have a ranking report where I want to sort it different ways depending on
    the option the user picks. On the On_Open event, I've tried everything I can
    think of and keep getting error 3071 "This expression is typed incorrectly
    or is too complex..."

    Here's the 2 things I've tried, both return the error 3071. If I comment out
    the code, the report opens normally. The fields SumOfInvTot, Margin,
    MarginPCT, and Comp all exist both in the report and in the query.


    OrderBy:
    ----------
    Select Case Forms!fReports. Form!lstReports (this is the reportID in a table
    of reports)
    Case 56 'sales ranking by total invoices $
    Me.OrderBy = "SumOfInvTo t DESC, Margin DESC, MarginPct DESC, Comp"
    Case 110 'sales ranking by profit $
    Me.OrderBy = "Margin DESC, MarginPct DESC, SumOfInvTot DESC, Comp"
    end select

    (Note: OrderByOnLoad is set to true)

    Group Level approach:
    ------------------------
    I added 4 groups to the report, and set all 4 to "without a header section"
    and "without a footer section", so really there are 4 sort levels.

    Select Case Forms!fReports. Form!lstReports
    Case 56 'sales ranking by total invoices $
    Me.GroupLevel(0 ).ControlSource = "SumOfInvTo t"
    Me.GroupLevel(0 ).SortOrder = True 'Set to Descending
    Me.GroupLevel(1 ).ControlSource = "Margin"
    Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
    Me.GroupLevel(2 ).ControlSource = "MarginPct"
    Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
    Me.GroupLevel(3 ).ControlSource = "Comp"
    Me.GroupLevel(3 ).SortOrder = False 'Set to Acending
    Case 110 'sales ranking by profit $
    Me.GroupLevel(0 ).ControlSource = "Margin"
    Me.GroupLevel(0 ).SortOrder = True 'Set to Decending
    Me.GroupLevel(1 ).ControlSource = "MarginPct"
    Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
    Me.GroupLevel(2 ).ControlSource = "SumOfInvTo t"
    Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
    Me.GroupLevel(3 ).ControlSource = "Comp"
    Me.GroupLevel(3 ).SortOrder = False 'Set to Accending
    end select


    Any ideas? This one's driving me batty....

    Tags: None
    • Salad
      #2
      Re: Help on Sorting a Report using VBA

      ARC wrote:
      I have a ranking report where I want to sort it different ways depending
      on the option the user picks. On the On_Open event, I've tried
      everything I can think of and keep getting error 3071 "This expression
      is typed incorrectly or is too complex..."
      >
      Here's the 2 things I've tried, both return the error 3071. If I comment
      out the code, the report opens normally. The fields SumOfInvTot, Margin,
      MarginPCT, and Comp all exist both in the report and in the query.
      >
      >
      OrderBy:
      ----------
      Select Case Forms!fReports. Form!lstReports (this is the reportID in a
      table of reports)
      Case 56 'sales ranking by total invoices $
      Me.OrderBy = "SumOfInvTo t DESC, Margin DESC, MarginPct DESC, Comp"
      Case 110 'sales ranking by profit $
      Me.OrderBy = "Margin DESC, MarginPct DESC, SumOfInvTot DESC, Comp"
      end select
      >
      (Note: OrderByOnLoad is set to true)
      That must be a 2007 thang. Still, I'd put an
      Me.OrderByOn = True
      after setting the OrderBy value.
      >
      Group Level approach:
      ------------------------
      I added 4 groups to the report, and set all 4 to "without a header
      section" and "without a footer section", so really there are 4 sort levels.
      >
      Select Case Forms!fReports. Form!lstReports
      Case 56 'sales ranking by total invoices $
      Me.GroupLevel(0 ).ControlSource = "SumOfInvTo t"
      Me.GroupLevel(0 ).SortOrder = True 'Set to Descending
      Me.GroupLevel(1 ).ControlSource = "Margin"
      Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
      Me.GroupLevel(2 ).ControlSource = "MarginPct"
      Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
      Me.GroupLevel(3 ).ControlSource = "Comp"
      Me.GroupLevel(3 ).SortOrder = False 'Set to Acending
      Case 110 'sales ranking by profit $
      Me.GroupLevel(0 ).ControlSource = "Margin"
      Me.GroupLevel(0 ).SortOrder = True 'Set to Decending
      Me.GroupLevel(1 ).ControlSource = "MarginPct"
      Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
      Me.GroupLevel(2 ).ControlSource = "SumOfInvTo t"
      Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
      Me.GroupLevel(3 ).ControlSource = "Comp"
      Me.GroupLevel(3 ).SortOrder = False 'Set to Accending
      end select
      >
      >
      Any ideas? This one's driving me batty....
      If you step thru the code does it break on the
      Me.GroupLevel(0 ).ControlSource =...
      or the
      Me.GroupLevel(0 ).SortOrder = ...
      or some other line? Maybe put the word
      STOP
      just before the
      Select Case Forms!fReports. Form!lstReports
      line. It might help if we knew where it was blowing up.



        Comment

        • ARC
          #3
          Re: Help on Sorting a Report using VBA

          Ok, now I feel silly... I was only taking a quick look at the query results,
          and there were many records. Well, if I scrolled to the bottom, I had a
          #Error in the Margin % field, so the one bad query result was causing the
          error 3071...


          "ARC" <PCESoft@PCESof t.invalidwrote in message
          news:1gINk.4544 $x%.1501@nlpi07 0.nbdc.sbc.com. ..
          >I have a ranking report where I want to sort it different ways depending on
          >the option the user picks. On the On_Open event, I've tried everything I
          >can think of and keep getting error 3071 "This expression is typed
          >incorrectly or is too complex..."
          >
          Here's the 2 things I've tried, both return the error 3071. If I comment
          out the code, the report opens normally. The fields SumOfInvTot, Margin,
          MarginPCT, and Comp all exist both in the report and in the query.
          >
          >
          OrderBy:
          ----------
          Select Case Forms!fReports. Form!lstReports (this is the reportID in a
          table of reports)
          Case 56 'sales ranking by total invoices $
          Me.OrderBy = "SumOfInvTo t DESC, Margin DESC, MarginPct DESC, Comp"
          Case 110 'sales ranking by profit $
          Me.OrderBy = "Margin DESC, MarginPct DESC, SumOfInvTot DESC, Comp"
          end select
          >
          (Note: OrderByOnLoad is set to true)
          >
          Group Level approach:
          ------------------------
          I added 4 groups to the report, and set all 4 to "without a header
          section" and "without a footer section", so really there are 4 sort
          levels.
          >
          Select Case Forms!fReports. Form!lstReports
          Case 56 'sales ranking by total invoices $
          Me.GroupLevel(0 ).ControlSource = "SumOfInvTo t"
          Me.GroupLevel(0 ).SortOrder = True 'Set to Descending
          Me.GroupLevel(1 ).ControlSource = "Margin"
          Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
          Me.GroupLevel(2 ).ControlSource = "MarginPct"
          Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
          Me.GroupLevel(3 ).ControlSource = "Comp"
          Me.GroupLevel(3 ).SortOrder = False 'Set to Acending
          Case 110 'sales ranking by profit $
          Me.GroupLevel(0 ).ControlSource = "Margin"
          Me.GroupLevel(0 ).SortOrder = True 'Set to Decending
          Me.GroupLevel(1 ).ControlSource = "MarginPct"
          Me.GroupLevel(1 ).SortOrder = True 'Set to Decending
          Me.GroupLevel(2 ).ControlSource = "SumOfInvTo t"
          Me.GroupLevel(2 ).SortOrder = True 'Set to Decending
          Me.GroupLevel(3 ).ControlSource = "Comp"
          Me.GroupLevel(3 ).SortOrder = False 'Set to Accending
          end select
          >
          >
          Any ideas? This one's driving me batty....

            Comment

            Previous template Next
            Working...