Need Help with a trim type query

[cedar72]

I am trying to figure out how to get a set of account numbers (ex. 100.1250.51220) that are already sorted by department (middle four digits 1250) to then sort by a range of numbers (.51200 - 51299). Any ideas of how to strip to find those middle numbers in Access?

 

[rac2]

Mid(account_number, 5, 4) is the middle four digits and
Mid(account_number, 10, 5) is the last five digits assuming account_number is a Text column and always has the same number of digits.

 

[cedar72]

thast a big help thanks! They are the same size now but may change...so what if I need to bring out the numbers between the decimals and then after the decimals? And yes it is a text data type.

 

[cheerio]

Left([account_number],InStr(1,[account_number],".&quot-1)

will give you the 100 by looking for the period and taking characters up to it.

Mid([account_number],InStr(1,[account_number],".&quot+1)
will give you the 1250.51220

You could apply the first construction to the second answer to strip off the 1250.

To be honest this is vulnerable to errors - for example if there are fewer than two periods.

Personally, I would prefer to write VBA functions which can have appropriate error handling and would be more universal. For example, in Excel you have to use SEARCH rather than INSTR and the MID function requires 3 arguments so the Excel solution is totally different. In contrast the same VBA functions should work in both.

Ken

 

[cedar72]

Thanks so much guys....big big help