Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting: Dates and Nulls (A 2002)

    A query UNIONs data from two sources. One source returns NULL on a field named TimeTag, while the other source returns Date variables. Sorting the column yields peculiar results: top of the list is the NULLs (that's what I need and expected), but the date values are sorted in a peculiar order. For example 2/11/2004 appears before 2/5/2004. Here is the SQL of the UNION query:

    select * from qryZonePolygonBoundariesWithBreaksBetween where fileID=91 UNION select * from qryCurrentGPSPos ORDER BY polygonID DESC , segmentno, (timetag);

    It looks as if the sort is based on the formatted view of the date, rather than the actual date. Yet the qryCurrentGPSPos sorts properly when opened independently. I tried modifiying the query to sort by ORDER BY ...., CDate(timetag), but received a syntax error.

    Is there a way to force the dates into the proper order in this UNION query?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Sorting: Dates and Nulls (A 2002)

    In the Union Query, you are ordering by polygonID DESC , segmentno, (timetag);

    Try switching the ordering sequence such that the field for dates is first such as timegag, polygonID desc, segmentno.



    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Sorting: Dates and Nulls (A 2002)

    ... One item to note. Is the date data actually formatted as a date or text field. If text, changing the order by statement will not help any. You will need to convert the data to a date format. Is the date data in text format such as mm/dd/yy?
    Regards,

    Gary
    (It's been a while!)

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting: Dates and Nulls (A 2002)

    Tried changing the order -- no effect. Then I tried explicitly formating the date by adding a dummy field and sorting on that. It sorts correctly now.


    select *, NULL as FormatDate from qryZonePolygonBoundariesWithBreaksBetween where fileID=91 UNION select *, format(timetag,"mm dd, yyyy hh:mm:ss") as FormatDate from qryCurrentGPSPos ORDER BY polygonID DESC , segmentno, FormatDate ;

    It was originally unformatted (default formatted???), but adding the format made it work.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: Sorting: Dates and Nulls (A 2002)

    Dates formatted as strings are not sorted properly. Since you said that the query qryCurrentGPSPos has a valid TimeTag field, just change the order of the Select statements in your UNION query so that qryCurrentGPSPos and its fields come first and see if that makes a difference. If you insert a Null value in the initial occurrence of a field in a union query, you'll wind up with a string in that column instead of the value you expected.
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting: Dates and Nulls (A 2002)

    OK - I can try that to see how it works out. I did not realize that having the initial NULL would cast the field as a string, but that seems to be exactly what its doing. Adding the Format() function *did* work, even if it adds more fields to the query.

    However, for this application, it is (I think!!) important for the NULL values to come first. I am building a recordsource for a chart object which has two distinct types of plot items. The first ones are area boundaries, and there are a small-ish number of them (say less than 100). The GPS points may number in the several thousands. I had worked previously with the chart object from Access 97, and it was limited to 4000 data points. (I don't know whether this limitation also applies to the AXP graph object -- it is a significant improvement over the A97 version in several other areas.)

    If any of the points are to be omitted from the chart, I don't want them to be the boundary points. Thus, I constructed the query such that the NULLs appeared first.

    I am not sure whether my whole approach is too kludgy. I need to graph the boundaries and points in two series, but the graph object is limited to a single X-axis, unlike the Excel graph that can have independent X-axes. The only solution I could think of was to display the boundaries and points as two distinct entities was to UNION them so they have a common X axis, and create two Y-columns that are partially populated with NULLs.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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