Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    New Hampshire, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Revisiting that old Xtab query (Access 2000)

    Hi folks,
    This is probably an old already answered question, but since I can't search I will pose anyway:

    Need to find a way to get Access to sort column heads in an Xtab qry in chronological rather than ascending order (6/1/03, 6/2/03 ... not 6/1/03, 6/10/03, 6/2/02). The Xtab is used for a summary report to show the numbers of defects found by day. I know I can force the column heads, but would have to do this every month - not an elegant solution by any means. <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

    The tables are fairly simple:

    Main:
    Lot #
    Date
    Shift
    (other descriptor data)

    Defect Found:
    Lot #
    Defect
    Amount Found

    Related by Lot #.

    Any thoughts, ideas, solutions or commiserations would be greatly appreciated! <img src=/S/please.gif border=0 alt=please width=31 height=23>

    Best,

    Franz

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

    Re: Revisiting that old Xtab query (Access 2000)

    This question has been asked many times in the Microsoft newsgroups. One solution is to get Access 2002 - it sorts dates in the column headings chronologically. The following is based on replies by Access MVP Duane Hookom:

    Instead of using the date field as column header, use the difference of the date with a fixed start ate, e.g. MyDiff: DateDiff("d",#1/1/1990#,[Date])
    You'll have to set the column headings in the report in code if you want to display the actual dates.

    Note: using "Date" as a field name is dangerous, because it is also a VBA function and a data type.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    New Hampshire, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Revisiting that old Xtab query (Access 2000)

    Thank you Hans - wish 2002 was an option here, company has been 2-3 years behind the times when it comes to latest and greatest (just finished the Win 2K upgrade recently). <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Also, filed names presented were just for simplification, have proper fields names in place. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Again, thanks for the help!

    Best,

    Franz

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Revisiting that old Xtab query (Access 2000)

    Another option is to format the date as yyyymmdd in the column header expression.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Location
    New Hampshire, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Revisiting that old Xtab query (Access 2000)

    Hi Charlotte,
    The simplicity of your solution belies the many strands of hair that were ripped out to come to it. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Many thanks!
    Best regards,
    Franz

Posting Permissions

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