Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Unanswered: Trying to show a "Time" lookup table in correct order

    I'm trying to get the time field to display correctly.

    Time is currently set as memo because there are just the odd times of the day listed (1am,3am,5am.......11pm) for selection in a combo box.
    No Calculations will be done with Time.

    In a query even if I have it set to date/time it is coming up 1am,1pm,11am,11pm,3am,3pm... when it's set to memo it's coming up the same way... see image
    http://www.geocities.com/voa1255/sort.jpg

    I'd like to make it come up in the correct order of the day without using military time, can someone help me out?
    Thanks.

    I should say I haven't touched Access in about 5 years...
    My boss wanted me to make this database for ease of keeping statistics.
    The database is complete save for this hangup, I'd appreciate a hand.
    Attached Thumbnails Attached Thumbnails sort.jpg  

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Why don't you just make that field a datetime field ?

    Access has this datetime capability, and it can do exactly what you want.

    Why a memo field ?

    Memo is essentially the same as a text field, but for data greater than 255 characters in length. You've typed this as a Memo field even though your data is only 5, 6, 7 characters. Why ?

  3. #3
    Join Date
    Mar 2004
    Posts
    55
    I could make it a text field I guess,
    I don't want to really treat it like time.
    Users are supposed to get stats every other hour on odd hours.
    Sometimes they are off by a little
    The lookup table is:

    1AM
    3AM
    5AM
    7AM
    9AM
    11AM
    1PM
    3PM
    5PM
    7PM
    9PM
    11pm
    The dropdown box in the form looks fine, but I'd like to keep the order when running a query, as you can see above they are being sorted as characters.
    Even if I set it to time, I want to have users pick form it in a combo box, and you can't set a combo box in date/time format.

    As far as why a memo box, I didn't think it reserved characters so I figured it wouldn't matter if I used text or memo, they were only taking up at most 8 characters per entry. Just want it to display in the order of the lookup table it's pulling from.
    Last edited by purpendicular; 04-01-04 at 19:14.

  4. #4
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    assign an Id number in the order you want. When you run the query sort in that order. Actually , you can remove the check box of the ID number so it does not show with your data.

    Hope this helps

    Surf
    Gotta to do some code

  5. #5
    Join Date
    Mar 2004
    Posts
    55
    Thanks, I'll try that,
    actually I've tried it already, but I ended up having the numbers pop up in the drop down box instead, I'll work on that premise.

  6. #6
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Smile

    Try a couple of things.
    Make sure under properties:
    "Format tab"
    You have Column count as 2
    and Column widths as 0";3"

    "Data Tab"
    Bound Column is 1

    Second thing, go to the table and drag the 1st field to the 2nd fields location.

    One or both or these should do it!
    Gotta to do some code

Posting Permissions

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