Sorting: Dates and Nulls (A 2002)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

… 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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

… 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?

Reply | Quote