Results 1 to 15 of 15
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting records in Ms Access (2000)

    Hi Justin.

    Welcome to the Lounge. You can probably solve your problem by opening the record source of the subform in design mode and specifying Ascending in the Sort: row for the field labeled Issue Date.

    It is not clear from your screenshot where you are trying to use the expression that causes an error, but the expression itself is invalid; it looks like the unfinished result of the expression builder. <<expr>> and <<stringexpr>> are placeholders that should be replaced. If sorting the record source works, you probably won't need to use this expression at all. Otherwise, post back and tell us where and how you want to use this expression.

  2. #2
    New Lounger
    Join Date
    Jun 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting records in Ms Access (2000)

    (Edited by HansV to reduce large picture - it caused horizontal scrolling) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I am trying to sort records in access so that the records with the most recent issue date are show first rather than last. As at the present moment the record with the oldest date is show first.
    See Appendix 1 for details for a further explanantion on the problem and some screen
    shots of the problem l am having in order to rectofy this. I would appreciate it if you could
    assist in this matter.

    Regards
    Justin.
    Attached Images Attached Images

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    (Edited by HansV to reduce large picture in size- it caused horizontal scrolling. Please keep attachments small.) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Hi Hans ,


    I tried to sort the control source in ASC order but l got an error.
    please could you send me an attachment to show me what l am doing wrong.

    I want to use the expression to sort the records so that records are sorted in ASC
    order with the most recent issue entered in the issue date field shown as the first record.
    Again , could you show me an illustration of what you to ensure l understand what you mean.

    See screen shots for example of what l mean.

    Thanks
    Justin.
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting records in Ms Access (2000)

    Justin,

    A. You can't set the sort order for a particular control by changing properties (such as control source) of that control. Sort order is a property of the form as a whole. Moreover, even if you could have done it here, you couldn't have used ASC to specify ascending sort order.

    B. From your attachment, it appears that the record source of the form is named "cdrl subtable".
    1. <LI>Close the form.
      <LI>Activate the Queries section of the database window.
      <LI>Click "Create query in Design view".
      <LI>Double click "cdrl subtable" to add it to the query, then close the Show Table dialog.
      <LI>Add all fields from the table you need to the query grid.
      <LI>Set the Sort order for "Issue Date" to Ascending (select it from the dropdown list)
      <LI>Close the query, and save it as qryCdrl_Subtable (I prefer to use names without spaces)
      <LI>Switch to the Forms section of the database window.
      <LI>Open the form in design view.
      <LI>Change the record source from "cdrl subtable" to qryCdrl_Subtable.
      <LI>Close and save the form.
    I may seem like a lot of steps, but it takes longer to write it down than to do it...
    Attached Images Attached Images
    • File Type: png x.png (2.5 KB, 0 views)

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    Hans ,

    I have tried what you mentioned but it is still not working.

    If you see screen shots of what l have done and what problems are occuring.
    I would appreciate it very much if you could sort out what l am doing wrong.


    Thanks
    Justin.
    Attached Images Attached Images

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    <P ID="edit" class=small>(Edited by D Willett on 27-Jun-03 08:27. )</P>Justin
    You've probably not imported all the fields into the query.

    You need to find the record or control source of the form.

    Open your database holding the shift key:
    You designed the form so you should know what the source is behind it, whether it be a table or a query:
    Go to that section of the database window, (table) or (query) and find which is behind your form.

    When you've found it, highlite it and click design, if it's a query as Hans is trying to point out to you, you will see something like the pic Hans supplied.

    There will be a field or section which says "Sort", click in it, it will show Ascending or Descending, whichever option you want.
    then close it via the x in the top right corner and choose the Yes option to save it.

    Hope this is making things a little clearer.
    If you still struggle with this, try to attach (as you did with your images) a very cut down version of your database, take out most of the records and just leave a couple for demonstration.

    The attachment must be zipped and under 100k

    ************************************************** *****
    Have a look at my attachment, it shows the two ways to sort your records:

    Check the table in design mode, select the index button from the top menu, I created an index to sort the records in Ascending order.

    Check the query in design mode, you will see I addedd Ascending in the "sort" option.

    Thats all there is to it.
    ************************************************** ********

    Good luck.
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    I have tried to do what you explain.

    I cannot see how sending the zipped database will help much as it is just these
    areas that are not working correctly.

    It will also take me a while to go thorugh database and reduce the size of it down.

    If you see what l have illustrated in screenshot , you will see that l have done what you mentioned but appear to be getting an error in the name field.
    Could you possibly explain the cause of this.

    If you require any further screenshots of anything else that let me know ,as l
    would be more than happy to send some more screenshots.

    Thanks for your help in this matter.

    Regards
    Justin.
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting records in Ms Access (2000)

    Justin, uncheck the "Show" check box under Issue Date. Since you included * in your query, Issue Date occurs twice in your query now, and this confuses Access.

    Remarks:

    1. Please reduce your screenshots in size. The once you have posted in this thread were all so large that they caused horizontal scrolling in all resolutions below 1280 wide. Many loungers have 1024 or 800 pixels wide screens. A thread becomes hard to navigate if you have to scroll to the left and right as well as up and down. Up to now, I have reduced them for you, but I can't afford to do that each time.

    2. >> I cannot see how sending the zipped database will help much.
    I think Dave Willett's demo database was helpful - it shows how you can set the sort order in a form by setting the sort order in the query that acts as record source. Since you have trouble to make it work in your own database, it might help to look at a working example.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    Justin
    Hans's advice is correct, I think you have just got muddled along the path.

    It may help if you start from scratch and follow the advice from the start.
    In one of the posts, Hans mentioned your table name had a space in it, and we can see that you've changed this in the properties.

    Have you changed this manually without changing the name of the table first.

    If your table is :-

    tbl Whatever (notice the space) and you edit the property by hand and insert the underscore yourself, when you open the form, it can't recognise the table unless you "ReName" the table in the first place.

    In your query, instead of importing all the table in one go, import each field individually, that way you will not duplicate any fields.

    If you duplicate a field in a query, Access will add the name of the table to the field, so your forms field with the #Name? in it, can't recognise the field because it has been renamed.

    Your brain's probably doing hoola hoops at the moment, but if you start from scratch and try to understand what's going on in the background, you'll do much better.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    Justin

    Look at your picture, you have duplicated issue date.

    While you are in the query open it and see what Access has done to your field name in the header.

    You now have the correct setting "Ascending"

    In my post I tried to explain this to you !!

    if you want to see my database, it's there in my post under "D Willett"

  11. #11
    New Lounger
    Join Date
    Jun 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    Reduced screenshot in size - HansV

    I have removed the "show" from the check box but still it is not working.

    Can you please tell me how l can look at Dave Willett's demo database.
    Can you send me a copy via email of it not can you tell me where it is located
    on the web site.

    As this l think is the only solution as to what l am doing wrong.

    I have amended query and that is now fine.
    See screen shot.

    What could l being doing wrong?
    I have checked and double checked over everything you have told me but still no luck!
    I do not have the problem of #name showing but it still does not sort records correcty.
    I think the only solution if you could send me a small access dabase with an example in.


    Justin.
    Attached Images Attached Images

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting records in Ms Access (2000)

    Justin,

    Dave has already explained the error in your query, which was essentially the same as the error you had before: Issue Date occurred twice. In the previous version this was because * contains ALL fields by definition; this time because you added Issue Date twice to the query grid.

    If you want to download Dave's demo database, look at his <post#=269137>post 269137</post#> (you can click this link if you don't know where it is). Below his user name and title and the date, you see Attachment. Click on it to download the zip file; you will be prompted to open it or save it; select save.
    Attached Images Attached Images
    • File Type: png x.PNG (5.6 KB, 0 views)

  13. #13
    New Lounger
    Join Date
    Jun 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    I have done a small database to try and illustrate the problem l am having with sorting the
    records.
    I have rectified the problems you mentioned but still it does not rectofy the probem.

    If you look at documents for review form and look at record PH1644 you will see in the subtable that the records in in the subtable are not sorted by Issue Date.

    i.e. The first record should be 9-Jun-03 and the last record should be 13-
    Dec-02.


    Is is possible you could find out why this and what l am doing wrong and possibly include a
    screenshot of what l need to do in order to rectify what l am doing wrong.

    I have included a zip file of a small database.

    Thanks for your asistance in this matter.

    Regards
    Justin.
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting records in Ms Access (2000)

    1. If you want the FIRST date to be 9-Jun-03 and the LAST date to be 13-Dec-02, you are sorting in DESCENDING order, not in ASCENDING order as you have been telling us all the time. So, if this is really what you intend, you should set the sort order of "Issue Date" in the query to Descending.

    2. In the database you posted, the subform still has Record Source "cdrl subtable", you set it to "qrycdrl_subtable" as advised in previous replies. If you do that, the records in the subform will be sorted on "Issue Date" in the desired order.

    See attached screenshot.
    Attached Images Attached Images
    • File Type: png x.PNG (16.4 KB, 0 views)

  15. #15
    New Lounger
    Join Date
    Jun 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting records in Ms Access (2000)

    Thats great , it now works. Sorry for any confusion and misunderstanding.
    It was suprising how simple it was!!

    Yet l was thinking it was a lot more difficult than it actually was!!

    I appreciate your help very much it.
    Thank you.

    Justin.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •