Report Sort query question: (1 Viewer)

[Bill Harrison]

Hi, we have a database that uses stock numbers. They are 4 digits, for example 5298. They increment every time we add a new vehicle to stock.

I need to print a report, but sort the vehicles by the LAST 2 numbers. In other words, if there are 2 vehicles, for example, 5225, and 5325, i want them sorted, then XX26, if there are more than one, then XX27, so on.

This is how we keep our key boards, and will make finding missing keys MUCH faster for us.

Any help would be appreciated!

Thanks

 

[reclusivemonkey]

You need to take the last two digits of the vehicle and group on these; you should do this in the query you use for your report. Just add another column;

Expr: Right([vehicle_number],2)

You can then group by this field on your report, and still sort the group on the full vehicle number.

 

[Bill Harrison]

Not sure what you mean, if i create a query using that, i get no items

Do i put that in the query? I am not sure what you mean by "make a new column" ?

 

[sportsguy]

Bill, its easier to write the report off of a query than directly off the table.

Are you linking the report to the table or to a query?

your answer sounds as though you are linking it directly to the table. . . which is not the prefered way.

Try writing a query that just returns all the table fields that you have in the report, and then in the next free column in the query, add the expression to the query from above, then run the query to see if that is what you are looking for.

sportsguy

 

[Bill Harrison]

Bill, its easier to write the report off of a query than directly off the table.

Are you linking the report to the table or to a query?

your answer sounds as though you are linking it directly to the table. . . which is not the prefered way.

Try writing a query that just returns all the table fields that you have in the report, and then in the next free column in the query, add the expression to the query from above, then run the query to see if that is what you are looking for.

sportsguy

I am linking the report to a query. The query currently pulls based on the "DATESOLD" field being null, meaning the vehicle is still in stock. This works fine, and pulls all the vehicles properly. Now I just need to sort them by the LAST 2 digits of the 4 digit stock number. I see your expression above, but where do I put this in the query? If I put it in the "Criteria" of the Stock_NO field, i get no results. Any suggestions?

 

[Rich]

Add a new Column to your query and add the expression that was suggested before