break a string into smaller bits

[jmss96]

Hello
I am new to Access and new to this site. I have a problem I cannot find an answer to. Hoping I can get some help. I have a table imported from AS/400. This table has all kinds of information, but I am dealing with corrugated. I got it broke down to what I want except for one field. It is called SUPDS1 and it shows the following: (example 1) 58 x 60 Single wall sheet and (example 2) 18 1/4 X 13 5/8 X 10 5/8 ECT44. I want to break these lines down to show length, width and height. Example 1 would be 58 (length) and 60 (width) with no height. Example 2 would be 18 1/4 (length) , 13 5/8 (width) and 10 5/8 (height). In both examples, I am not worried about the wording, just the sizes. Also the numbers change because of the differences in case sizes. I did find the following online which worked for my length. Length: Left([SUPDS1], InStr([SUPDS1],"x")-1). If anyone could help me get width and height, it would be greatly appreciated.

[June7]

This will be difficult to do by expressions in query because of the inconsistent structure - not every value has height.

I would use VBA procedure to extract the parts and save them into new fields in table. Something like:

Sub GetDims()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT SUPDS1, Length, Width, Height FROM table1;")
Dim aData As Variant, i As Integer
While Not rs.EOF
    rs.Edit
    aData = Split(rs!SUPDS1, "x")
    For i = 0 To UBound(aData)
        If InStr(aData(i), "/") Then
            rs.Fields(i + 1) = Trim(Left(aData(i), InStrRev(aData(i), "/") + 2))
        Else
            rs.Fields(i + 1) = Val(aData(i))
        End If
    Next
    rs.Update
    rs.MoveNext
Wend
End Sub

Do you have to import this data regularly?

[jmss96]

OK. I will try that, but where do I type all of that. Do I have to make a column in my table called Length , Width, Height or will it automatically be in my query table. I am just starting to work on this project so I am not sure how often I will need to import this data.

Thanks

[June7]

Create the fields in table.

The code would go in a VBA general module.

Position cursor somewhere within the procedure and click Run from the VBA menu bar.

Be sure to change the SQL statement for the recordset to use your table name.

Might want to test with a copy of the table.