Concatenation/Displayed vs. "Natural" Order

[Guest]

I have a query that produces a table of Part-Date-Quantity&Date, telling me
how many of that part has been ordered for date x, with the likelihood of
several records for each part.

With a second query, I have to concatenate the Quantity&Date values for each
part, up to a maximum of 50 characters, into a single Comments field to give
the planners a look at the near-term delivery schedule for a given part.

PROBLEM: Although the table produced by the first query is sorted by Part
and Date, and appears dandy on screen, the second query does not perform the
concatenation in chronological order, and update queries don't appear to
offer any kind of sort option. The query is apparently using some kind of
"natural" order for that data behind the curtains, so my final-output
concatenation does not come out in date order, earliest to latest.

Is there an easy solution, or am I going to have to write some esoteric code
to get what I need?

 

[Guest]

When you concatenate you are building a string - text. It would be sorted in
text order - 1 before 2 even if it was 12 then 2.

Pull just the date for sorting and make it not visible.

 

[Guest]

That's not quite the problem.

WHAT I HAVE IN THE SOURCE TABLE (WHICH IS CREATED BY A MAKE-TABLE QUERY SET
TO SORT ON PART AND DATE):

Part1 11/15/04 35:111504;
Part1 1/9/05 10:010905;
Part1 3/3/05 10:030305;
Part1 5/12/05 15:051205;
Part1 7/7/05 25:070705;
Part1 10/20/05 50:102005;
Part2 12/16/04 15:121604;
etc.

WHAT I WANT OUT OF QUERY 2:

Part1 35:111504; 10:010905; 10:030305; 15:051205; {concatenated in date
order, cut off when next value would cause the string to exceed 50 characters}

WHAT I'M GETTING OUT OF QUERY 2 NOW:

Part1 15:051205; 10:010905; 50:102005; 10:030305 {concatenated values
are not in date order, even though they are displayed that way in the source
table}