Microsoft Access string parsing

Question

In MS Access 2003, I have the following values in a column

0-0-60
20-0-0-24S
20.5-0-0-24S
32-0-0

How can I write my query such that I can pull the 3rd item from the column, example the "60" from "0-0-60". Keep in mind that I've optionally got the 4th column also.

Answer 1

I believe an inline expression like this on the query builder tool would do the job (untested).

right(yourColumnNam, inStrRev(yourColumnName, '-'))

Answer 2

Will you ever have less than 3 items in each column ?

Really if you've got a very specific format of either 3 decimals or 4 decimals as values, I'd consider changing your schema to

Value1 decimal
Value2 decimal
Value3 decimal
Value4 decimal (NULLABLE)

but that's a little beside the point...

If you really are stuck with this, then its probably quicker to parse in your code, than it is to do some nasty left/right/instr work in the Access SQL Query

Answer 3

You will want to generalize this function for the delimiter and what position, but:

Public Function SplitString(inputString As String) As String
    Dim TestArray() As String
    TestArray = Split(inputString, "-")
    Debug.Print TestArray(2)
    SplitString = TestArray(2)
End Function

You can call in in your query like this:

SELECT SplitString([MyField]) AS Expr1
FROM MyTable;

Good Luck!

Answer 4

The fact you are finding it non-trivial to query you data column is what we call a 'smell'.

This looks exactly like a First Normal Form (1NF) violation because you have non-scalar data in a single column. I trust you are splitting these values for a one-time data scrubbing exercise to fix the problem once and for all (in which case I'd personally use Excel's text to columns feature if the resultset was small).

If you are proposing to do this in a query for you application then I suggest you research normalization and change you schema accordingly.