querying for dimensions

[btappan]

I have a field containing dimensions stored like this as Length, Width, Depth:
6 x 6 x 6
17 x 14 x 11
10.25 x 4 x 6.75

I need 3 different queries so I can return them in separate columns for Length, Width, Depth. Some fields have no dimensions at all, so I need a null/blank value returned for those. I've tried a few things but am having trouble with flexibility with the decimals and without.

[ranman256]

You can make a form with 3 boxes for users to enter.
The vb code can filter the continuous form on the values given ,
but ,how strict do you want to be?
if user enters W=2.25. ,but the value in the db is 2.24 ,the query will fail.
would you want to add tolerance? +-N ?

[btappan]

Thanks, the data I'm referring to is in a spreadsheet from a vendor. I'm trying to use access to get it into a different format.

[aytee111]

One method:

Query:
SELECT Table1.Dimensions, GetWidth([Dimensions]) AS Width, GetLength([Dimensions]) AS Length, GetHeight([Dimensions]) AS Height
FROM Table1;

In a new module in VBA:

Public Function GetWidth(Dimensions)

    If IsNull(Dimensions) Or Dimensions = "" Then Exit Function
    
    Dim x As Integer
    
    x = InStr(Dimensions, "x")
    GetWidth = Left(Dimensions, x - 2)
End Function
Public Function GetLength(Dimensions)
    If IsNull(Dimensions) Or Dimensions = "" Then Exit Function
    
    Dim x As Integer, y As Integer
    
    x = InStr(Dimensions, "x")
    y = InStr(x + 1, Dimensions, "x")
    GetLength = Mid(Dimensions, x + 2, y - 2 - x)
End Function

Public Function GetHeight(Dimensions)
    If IsNull(Dimensions) Or Dimensions = "" Then Exit Function
    
    Dim x As Integer, y As Integer
    
    x = InStr(Dimensions, "x")
    y = InStr(x + 1, Dimensions, "x")
    GetHeight = Right(Dimensions, Len(Dimensions) - y - 1)
End Function

[jwhite]

If the vendor is consistent in the format of the entries, then:

strLength = Trim(Split("10.25 x 4 x 6.75","x")(0))
strWidth = Trim(Split("10.25 x 4 x 6.75","x")(1))
strDepth = Trim(Split("10.25 x 4 x 6.75","x")(2))

Replace the literal string with your variable or field name as applicable.

[aytee111]

Nice! Doesn't work in a query - how to make it work?

[jwhite]

I happened to see that syntax (the index at the end) format in another post somewhere last week. Didn't try in a query. Perhaps could create a function passing the fieldname and the element desired:

Public Function GetElementString(strIn As String, strDelimiter As String, intElement As Integer) As String
    ' Return value as String
    ' ? GetElementString("1 x 2 x 3","x",1)
    If strIn & vbNullString <> vbNullString Then
        GetElementString = Trim(Split(strIn, strDelimiter)(intElement))
    Else
        GetElementString = ""
    End If
End Function


Public Function GetElementNumber(strIn As String, strDelimiter As String, intElement As Integer) As Variant
    'Return as Variant so result can be set to any numeric data type
    ' ? GetElementNumber("1 x 2 x 3","x",1)
    If strIn & vbNullString <> vbNullString Then
        GetElementNumber = Val(Split(strIn, strDelimiter)(intElement))
    Else
        GetElementNumber = 0
    End If
End Function

Note that intElement is 0-based.

[ssanfu]

SELECT Table1.Dimensions, GetWidth([Dimensions]) AS Width, GetLength([Dimensions]) AS Length, GetHeight([Dimensions]) AS Height

Just wanted to point out that both WIDTH and HEIGHT are reserved words in Access.......