Results 1 to 14 of 14
  1. #1
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17

    Question Sort by time slot?


    I have been trying to figure something out for awhile and figured I would give the forums a shot. What I am trying to do is sort by time, but it's actually a time slot (9-12, 10-1, 1-3, etc.) ... But of course the problem is that if I sort by the time slot field, it always sorts by number (1-3) comes first I believe. What am I missing? I know it can be done, just confused on how to accomplish it. ANY help would be greatly appreciated!!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you need 2 fields,
    1 to show users (9-12) ,but this is text so it wont sort correctly,
    2 to sort by and it must be numeric ( or text with zero fillers)
    09-12
    13-14

  3. #3
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17
    Thanks for your reply! That's what I was thinking, but not sure how to do that. On the Sales form there is a time slot textbox, where you can put anything (9-12, 9-11, 10-1, 10-2, etc.). There are only so many of them, 10 slots I believe throughout the day. But on the report, what do I pull in? I pulled in the textbox values from the form, but how do I link that to another field in order to sort by that field? and where do I put it on the report. I will attach an example of the report. to the left is the time slot. As you can see the 8-11 slot comes after the 12-3, it should be before it. Thanks again for your help, I will admit I am a NOVICE, just trying to learn reports.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe have a hidden control on the report that gets a numeric from the string time range and sort by that in the group's detail section.
    =Val(textField)
    Val(8-12) should return 8. Val(12-3) should return 12. 8 will come before 12
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17
    What about multiples of the same number.. (12-3, 12-4) and what about numbers like 1-4 where 1 is technically before 8 but not in time format..an 8-10 appointment would be before 1-4 but it would return the 1-4 before the 8-10

  6. #6
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17
    could I assign each slot to a letter and then sort that way somehow? ie. a = "8-10" b="8-11" c="1-4" etc? there are only these:

    8-10
    8-11
    9-12
    10-1
    10-2
    11-2
    12-3
    12-4
    1-4
    1-5
    2-5
    2-6
    3-5
    4-6

    That's all the slots there are...

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    where you can put anything
    I guess I figured by that statement that the field was completely free to put in whatever. I guess having a sort order field would be best in your situation. I have done this before, but I count by some other number than one or two in order to leave open the possibility of new items without having to renumber the whole enchilada, e.g. 5, 10, 15, etc. Very easy to add a 13 without any cascading affect.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17
    the field on the sales form is free for anything, but we only use those listed. Again, I am a novice so any detailed explanation would be greatly appreciated. I understand what you mean by a sort order field, but how exactly do I get that field to correspond with the Time Slot field on the report? The field in the report for the time slot is called "SchTime", the field on the actual scheduling form is called "SchTime". I use the reports query to pull in the "SchTime" field and place it on the report, but that's about as far as my current knowledge of access will take me. I do not understand the process of putting a hidden field on the report and sorting that and then making the "SchTime" sort correctly..Excuse me for being a noob.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    how exactly do I get that field to correspond with the Time Slot field on the report?
    First, you add this sort order field to the table, then you add that new field to your report query. You can apply the sort to the query as well, but it is not necessary. It could help from the point of view that when you run this query, and it looks good, you are good to go with making report design changes. Then add a textbox to the report for the sort field. While still in report design view, you apply an ascending sort to the detail records, sorting by your new sort order control. If there is a sort on the time slot now, you will have to remove it. You do not have to make this control visible when you're done making the changes. I can not tell from your tiny sample (picture) if you have any groupings or not, so I will assume you do. If not, it should be easier still.
    Let's say there are 3 records for Appliance: Fridge with slots 1-6, 12-3, 8-10. If these have sort order values in the table of 15, 10, 5 respectively, the records will appear in the order of 5, 10, 15 but you will only see 8-10, 12-3, 1-6, assuming you make the sort order control invisible.

    If you are grouping by appliance type in ascending alphabetical order, this will happen for Fridge before it happens for Stove. This means the last time slot for Fridge can be later than the first record in the next group of records for Stove. This should not be an issue if you're grouping by appliance type (or any other means) as most people are smart enough to realize how things work when you scan/read from group to group. Don't worry about the noob thing, compared to some, I still am and will always be. You can always post a zipped copy of your db for reviewing and suggestions.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17
    There are no groupings, appliances are at random, the time slots are what is important so as I am doing the appts I can go from the top of the list down. I got most of what you said, but don't quite understand some of it. The form I pull the report from is called "frmSchedule", which corresponds to table "tblSchedule" so do I add a field in "tblSchedule" as the sort order field? and if so, how does access know how to handle this empty field? That is where I am lost, what is going to that field when I put the time in on "frmService"? Anything? Now when you say "Sort Order Field" & "Sort Field" are you referring to two different fields? I am assuming that "Sort Field" is the textbox "SchTime" and "Sort Order Field" is the hidden one we are going to use to actually sort the "SchTime" field.. Also, do I sort inside the query or sort in design view details section (I don't think I have ever done that, I always sort in the query)

  11. #11
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17
    also, does the new table field need to go in "tblService" or "tblSchedule"? I enter the time in "frmService", then on "frmSchedule" I sort for whatever day I want, then hit a command button which opens the report, sorted by the day I chose. That's what gets me, if I am sorting via command button by the day, how can I also sort the time on top of that? I know, I'm confusing myself!

  12. #12
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by EchoEleven View Post
    The form I pull the report from is called "frmSchedule", which corresponds to table "tblSchedule" so do I add a field in "tblSchedule" as the sort order field?
    I can't see your tables and what they contain... I don't know where your time slots are kept. My assumption is that you have a table for this. It would not be good if you want to sort by something that you're allowing users to enter on the fly. You're saying there are only so many allowed, and have indicated they are 'set' - so where are they? I presume you mean "the form I OPEN the report from", which is irrelevant.
    and if so, how does access know how to handle this empty field? That is where I am lost,
    It would not be empty - you were going to apply a letter sort per your post #6; I'm just saying use a number. You have to assign a number to each time slot. Whether or not you actually sort this table after doing so doesn't matter, BTW.
    Now when you say "Sort Order Field" & "Sort Field" are you referring to two different fields?
    No.
    I am assuming that "Sort Field" is the textbox "SchTime"
    Again, no.
    and "Sort Order Field" is the hidden one we are going to use to actually sort the "SchTime" field..
    Yes.
    Also, do I sort inside the query or sort in design view details section (I don't think I have ever done that, I always sort in the query)
    Not sure I get that, but I'll say you sort the query in design view to make it permanent. As I said, sorting the query is not important. Maybe re-read the instructions and try to implement them in order as suggested. I thought I broke it down into easy steps, but perhaps not as easy for you as I had hoped.
    Maybe you should zip and upload a copy of your db?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    EchoEleven is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2016
    Posts
    17
    actually I just have a textbox on the service form that users can enter anything they want, but I can see where this would/will cause an issue. Should I just change the textbox to a combo and have the available slots populate into that? I am starting to follow you, my question would be is how do you get the NEW field in the table to change to a number (10,15,20) when the combobox "SchTime" is changed. Is there something I need to do in code to say "If combobox = "9-12" then "New field =?". I dunno. I cannot grasp the simple logic of how this will operate depending on what is chosen on the Service form. I know you must be thinking OH MY GOD, sorry for that!

  14. #14
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by EchoEleven View Post
    actually I just have a textbox on the service form that users can enter anything they want, but I can see where this would/will cause an issue. Should I just change the textbox to a combo and have the available slots populate into that?
    Yes. As ranman256 said, you need a table with 2 fields. Make it tblSort with 2 fields; SortOrder,NUMERIC, (2,4,6... or whatever) and TimeSlot,TEXT (8-10,etc). In a 2 column combo, set the combo rowsource to the name of a query that you create with this sql (or just put it in the combobox rowsource) SELECT [SortOrder], [TimeSlot] FROM tblSort ORDER BY [SortOrder]; The combobox needs 2 columns, make the first column width 0" now or if you want to watch what's going on in it, do it later before you release the db changes. Showing them the sort order numbers will likely confuse. The bound column needs to be 1 (default) for the sql statement given.
    I am starting to follow you, my question would be is how do you get the NEW field in the table to change to a number (10,15,20) when the combobox "SchTime" is changed.
    I don't understand this question. It seems you are not grasping this concept very well, so I'll just say the sort order number is an additional field on the report. It just becomes part of the record, like any other piece of data on the record for a service call, if that's what these are. You are simply using it to SORT by on the report, not change any field value as a result. However you have passed the time slot values to the report, you do so now, except you will pass the value of the combo to the new control on the report and sort the report by it. In order to still get the time slot on to the report, you have to know how to get it from the form used to open it. Simplest for you would be to assign the second combo column to the existing textbox on the form. I presume that's where you're getting it from now. In the AfterUpdate event for the combo you'd use Me.NameOfTextbox = Me.NameOfCombobox.
    I know you must be thinking OH MY GOD
    Getting there. If you are still stuck after this, I'm not sure if I can devote more time if you're not willing to upload the db as suggested.
    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. Replies: 1
    Last Post: 04-07-2014, 02:59 AM
  2. Replies: 5
    Last Post: 03-19-2014, 02:31 PM
  3. time slot availability
    By medioman in forum Queries
    Replies: 5
    Last Post: 07-06-2013, 03:16 AM
  4. sort filter, doesn't sort everything
    By deso in forum Queries
    Replies: 4
    Last Post: 01-25-2013, 05:27 AM
  5. Replies: 1
    Last Post: 01-23-2012, 11:44 AM

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