query criteria

[Guest]

I'm not sure how I would set the sort criteria for this situation; any help
you can give me is appreciated.

My contract numbers in my table are always 13 characters in the following
format: NNNNNNNNN-NNN-N. I want to sort based on the numbers in spaces 9,
10, 11 that are located between the dashes -NNN-. Is this possible.

Thanks for your help.

 

[Guest]

Hi,

I would create a new field in the query, called something like
srtfld:mid([contract number], 9, 3), and sort on it. Or you could use substr
function.

 

[Guest]

Do you want to Sort or filter (criteria)

If you want to filter the records, one option will be

Where FieldName Like "*-" & [Please select a string] & "-*"

**************************
Or, to continue Chris idea

Where mid([contract number], 9, 3) = [Please select a string]
**************************
Note: in this examples the user will be prompt with the message [Please
select a string] where he/she enter 3 chr string