OrderBy Property

[Jerry McNabb]

I have a union query in access 2002. The first two fields are SortKey amd
RecordType. I specified OrderBy = SortKey, RecordType. The data comes out
just as I expected it to. I use this query as the basis of a Report. In the
report, I specified GroupBy = SortKey, OrderBy = SortKey, RecordType, and
OrderByOn = True. It sorts the records by ascending SortKey and descending
RecordType. Help says that it sorts both ascending unless I follow RecordType
by "Desc." How can I correct this?

Thank you.

 

[Duane Hookom]

I never use the Sort By property of a report. I find the Sorting and Grouping
levels are more reliable. You can set these to ascending or descending.

 

[Ken Snell \(MVP\)]

OrderBy = "SortKey, RecordType DESC"

 

[Jerry McNabb]

"I don't want it in descending order that's the whole problem. It is coming
out in SortKey ascending and RecordType descending. I want SortKey ascending
and RecordType ascending.

 

[Duane Hookom]

Are you expecting your fields to be numeric? Do they appear right or left
aligned in the record source's datasheet view? If they are left aligned and
you want them treated as numbers, you may need to wrap the field names in
Val().

 

[Jerry McNabb]

The SortKey field is alphabetic such as "SmithJohnDoe." RecordType is text
such as '1 " or "1RN." Of Course, all are left justified. I checked to see
if I had put any leading spaces in them and I had not.

 

[Ken Snell \(MVP\)]

Because the RecordType field is text, it will not sort in numeric order
because the values are not numbers. And because the values contain both
numbers and letters, simply wrapping the field with Val function may not
produce the desired sort order.

Show us examples of data values for this field, and how you want them to
sort. Show examples with just numbers, and with both numbers and letters.

 

[Jerry McNabb]

I just realized that I only need to sort on the first character of
RecordType which is always a digit. so my OrderBy should be = SortKey, Val
(Left (RecordType)). Is that right.

 

[Ken Snell \(MVP\)]

Val(RecordType) should work. No need for the Left function.

 

[Jerry McNabb]

With or without the Left function, I still get the same result. RecordType is
still sorting in descending order.

 

[Ken Snell \(MVP\)]

Try this as the OrderBy property:

="SortKey, Val(RecordType) DESC"

 

[Ken Snell \(MVP\)]

Sorry - wrong info in "just sent" post -- try this:

="SortKey, Val(RecordType)"

 

[Jerry McNabb]

That gives me the same result as before.

 

[Ken Snell \(MVP\)]

OK -- you'll need to add a calculated field in your query:
SortRecordType: Val([RecordType])

Then use this in the OrderBy property:

SortKey, SortRecordType

 

[Jerry McNabb]

Thanks, Ken.

I added the field to each of my select queries and then to the union query
and changed the Order By Property of the report, and if worked.

Thanks to all who tried to help