Limit Fields in Query to Ascending

[anoble1]

I have made a query with a custom field of:
LocName: ["LOCATION_D ESC] & " ~ " & [LOCATION_CODE]

How do I make the [LOCATION_CODE]; ascending? and not make the LOCATION_DESC ascending. While keeping the field the same? When I run this it doesn't know how to arrange it because there are 2 categories.
Thanks

[beacon]

Are both fields actually on the query?

I don't know if this is the best way to handle what you're trying to do, but I would add them both as fields on the query, set the ascending/descending for each, then create the custom field you mentioned at the end. Then you could hide the columns for the fields you don't want to look at.

[anoble1]

Are both fields actually on the query?

I don't know if this is the best way to handle what you're trying to do, but I would add them both as fields on the query, set the ascending/descending for each, then create the custom field you mentioned at the end. Then you could hide the columns for the fields you don't want to look at.

Yeah, they are one field. I wanted to leave them on one field, so it wont mess up the form.

[NeoPa]

Include [LOCATION_CODE] in the query as a field which is not displayed, but set the sorting to Ascending.

[beacon]

Yeah, they are one field. I wanted to leave them on one field, so it wont mess up the form.

If you include the additional field, it shouldn't mess up the form. If you have column counts for a combo box that you're worried about, just move the field to the end.

You can leave your expression as one field...just add the other one as well, like Neo and I suggested, and it should work out without compromising anything that's dependent on the query.

[anoble1]

I think I see what you are saying here. But the way my form is set up it, it is a title. There are 3 titles on top. The LocName field, has the DESC in there. These descriptions can be all different lengths, like long names etc, or small names. When I add the new field and tie it to the form, it cuts stuff off and on small stuff it just isn't even and space out.
Is there another approach?
Thanks!!!

[ChipR]

You don't need to add the new field, just include it in the recordset or SELECT statement that the form is based on. Fields don't have to be present on your form to be used in calculations.

[anoble1]

Well, the thing is, these 2 have to be together. I got to thinking and, I need an idea of how to write in the criteria area, how to make the first selection ascending (LOCATION_DESC) and NOT make the (LOCATION_CODE) ascend. So key off the LOCATION_DESC. If that makes any since.

[ChipR]

I'm not sure at this point whether you are trying to make a query, a form, or a report. Regardless, the first step will be to include LOCATION_DESC and LOCATION_CODE in your base query, but that has already been pointed out.

[NeoPa]

I think there's some confusion creeping in here.

The SQL for the form needs to include the fields to display in the SELECT clause, and the fields to sort by in the ORDER BY clause.

Is there anything you feel will not be handled by this?