Results 1 to 9 of 9
  1. #1
    Chris@harveynorman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7

    Sort Order problem

    I am having a problem getting my form to order by my selection. In my table I select a specific date and then the report shows the data from this when I refresh it by reselecting the record source in the draft view and then open print preview.



    The sort order shows up in the ORDER BY section of the PROPERTIES table but does not appear in the print view or when printed. (Order by date then order by store). It seems to be ordering by the Invoice number although this is not specified anywhere.

    I have attached some screen shots in a word document to show the properties of both the table and the report.

    Anyone know what might be going on?

    The order by is chosen from using the sort order function in the home tab on the table screen. I even use the advanced sort to order by the date then store. The sort order function does not appear accessible in the home tab of the report draft view.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use a query and set the sort order.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not sure I understand.....

    A table is for storing data. In a table, you should not be worried about filtering or sorting data.


    I use a query as the record source of a form. In the query you can set the sort order and/or filter if you want.


    In a report, I use a query for the record source. You can filter the query, but don't set a sort order. A report has a separate method (in the report design) to sort and group the data.

    Does this help??

  4. #4
    Chris@harveynorman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    Even using the QUERY as the record source the REPORT does not sort by stores. In the property box of the REPORT the order by reads as: [Picking Query].[Date], [Lookup_Stores].[Stores] (this sort order is picked up automatically from the sorting in the QUERY)

    I am wondering if how I have set up my MAIN TABLE is causing me issues. The STORES column is a look up column gathering the relevant info from another TABLE called "ALL STORES" TABLE. I don't actually have a TABLE called "LOOKUP_STORES"...

    Any suggestions?

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Again, A report has a separate method (in the report design) to sort and group the data.
    The sort order of the query the report is based on has NO effect on the report.
    In design view of a report, click on the "DESIGN" menu option, then click on the "Grouping & Sort" option. Add the fields you want to sort on.



    The STORES column is a look up column gathering the relevant info from another TABLE called "ALL STORES" TABLE.
    I NEVER use look up fields in tables.

  6. #6
    Chris@harveynorman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    Thanks ssanfu. That fixed it. I found that the Invoice was selected as the next sort by.

    If you don't use look up fields, how would I have that column used as a select from options only field? That is, I have a separate table that has all my store names and when entering data into the table, I want to chose from this list only to make sure all spelling is the same when sorts and searches are conducted. I am using either a list box or a combo box.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I use forms for data entry/viewing, never editing the table directly. Editing the table directly does not allow for validation of the data.
    If you have a list/combo box on a form, what use is a look up FIELD? In the list box/combo box, you still have to configure the list/combo box. So you are duplicating effort - first setting up the look up FIELD, then setting up the list/combo box.

    That is, I have a separate table that has all my store names
    This is a look up TABLE (different than a look up FIELD). I use look up TABLES all of the time.

    See
    The Ten Commandments of Access
    http://access.mvps.org/access/tencommandments.htm


    The Evils of Lookup Fields in Tables
    http://access.mvps.org/access/lookupfields.htm



    PS: I also NEVER use Multi-value fields (MFV) or calculated fields.

  8. #8
    Chris@harveynorman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    Thanks again. Look up table is what I meant to say.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If not a lookup table, a Select query using the DISTINCT predicate such as SELECT DISTINCT tblStores.Store FROM tblStores

    Editing the table directly does not allow for validation of the data.
    I might be all wet here, but couldn't a lookup field in a table be a form of validation if you set
    Limit To List > yes; Allow Value List Edits > No and provide a combo as the lookup control on a query or list table?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sort by Month Order
    By sk88 in forum Reports
    Replies: 1
    Last Post: 03-17-2015, 05:22 PM
  2. Access Query Sort Order every other one
    By Gina Maylone in forum Access
    Replies: 2
    Last Post: 09-29-2014, 11:26 AM
  3. Combo Box - Custom Sort Order
    By twildt in forum Access
    Replies: 4
    Last Post: 05-22-2014, 01:38 PM
  4. Sort order in a sub form
    By roar58 in forum Forms
    Replies: 1
    Last Post: 03-17-2012, 08:57 PM
  5. Report will order/sort 4 columns but not 5. Why?
    By TomHolden in forum Reports
    Replies: 6
    Last Post: 12-17-2011, 04:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums