Sorting numeric values in text field

[jzacharias]

I am having trouble sorting an expression smallest to largest in a report. I used the Val function in the query and it works, but can't figure out how to make it work inside the report. Example of the expression result is 625 ILCS 5/ 11-601(b) which is the Illinois statute for speeding.

 

[Pat Hartman]

Access rewrites your query when it runs a report. You need to use the report's sorting/grouping options to control the sort order of a report. In order to sort on a calculated field you will need to include it in the original query. So use the Val() function to extract the part you want to sort on and name it SortField:
Select ..., Val(YourField) As SortField,,,,,
Then select SortField in the sorting/grouping options.

 

[jzacharias]

Thank you for the response Pat. I will try your method when I get home.

 

[jzacharias]

I have another problem involving unique numbers. I have a table named tblCitations with Citation_ID set as an auto number and the Key Field. I also have a citation_number field and Citation_Date field. I didn't set the citation_Number field to no duplicates because eventually the numbers will be recycled (probably a few years) but is there a way to force no duplicates only if its on the same date or at a minimum the same year?

 

[jzacharias]

I tried the method above...all it did was sort by the first digits and not the whole expression.

 

[Galaxiom]

I didn't set the citation_Number field to no duplicates because eventually the numbers will be recycled (probably a few years) but is there a way to force no duplicates only if its on the same date or at a minimum the same year?

Use a multifield index (No duplicates) on the citation and date fields together.

 

[jzacharias]

Thank you...The multifield index worked great.

 

[Pat Hartman]

I tried the method above...all it did was sort by the first digits and not the whole expression.

Then add a second or third or fourth .... sort field.