Query Sort Order for 2 fields

[EddyLLC]

I have a query that contains both an Invoice Number and Line Number field. I would like to display them Invoice Number Ascending meaning the first record is Invoice number 1, the second is 2 etc. I would like to sort the line number the same. The results of the query would thus read Invoice number 1 Line 1, the next record would be Invoice number 1 Line 2. When all lines are displayed for Invoice 1 the go to Invoice number 2 Line 1 and so on.

The problem is if I set both fields sort order to Ascending it displays Invoice 2 line 1 first then invoice 1 lines 1 then 2. In other words the invoice number is not ascending. I have tried all combinations of sort orders with no luck. What am I missing?

Thanks.

 

[lupins46]

if your Order By clause says :
Order By [Invoice number], [Line Number]
then you will get the sequence you want.
(Provided the fields are numbers).

 

[EddyLLC]

Thanks for the reply. Is the order I'm looking for possible if one of the fields is a text field?

 

[lupins46]

Not directly. If the invoice number is text you can do:

Order By Val([Invoice number]) , [Line Number]

 

[EddyLLC]

Brilliant! Thanks so much! Works perfectly.

 

[EddyLLC]

Oops, spoke to soon. The query runs great. When I look at the report which the query feeds it still lists the invoices out of order, ie invoice 2 before invoice 1.

Is this possible? I am stumped! Any thoughts?

 

[lupins46]

The sort sequence in the query has no effect on your report.
You will have to put a sort into the report.

You will need to create an extra column in your query so that it can be sorted in the report.
myInvNo:Val([invoice number])