I'd convert the strings to numerics. Maybe something like [Item] = "9' Nails" becomes [ItemTypeId] = {reference to lookup table where id of item type = "Nail"} and [ItemLength] = 9.0
I wrote a vba regex function a few years ago that will convert different feet and inch notations to decimal, if that would help I'll see if I can dig it up when I get back to my computer. End user input can be ft/in or '/" or whatever and then just process it to a decimal before recording to the db.
[EDIT] Here you go
Code:
'/*---------------------------------------------------------------------
'| Method FtIn2Dec
'|
'| Purpose: To convert user input strings in various feet & inch
'| notations to decimal inches. Uses a regular expression
'| to parse the input into it's numeric components then
'| performes appropriate arithmitic on the components to return a
'| single decimal value in inches. Optionally return value can be
'| feet
'|
'| Pre-condition:
'| The regular expression engine must be referenced:
'| Tools -> References -> Microsoft VBScript Regular Expressions 5.5
'|
'| 1. If the first non space character in the string is a hyphen or
'| a open parentheses then the result is interpretted as negative.
'| A closing parentheses at the end of the input is optional and
'| no effect on the result. Only leading parentheses will return
'| a negative number
'| - 12' <= valid
'| (12') <= valid
'| (12' <= valid
'| 12') <= valid but NOT interpreted as negative
'|
'| 2. Input may have up to two values, one feet value and one inch
'| value each.
'| 3. Input may ommit a feet value or an inch value
'| 4. An input of an empty string will return 0
'| 5. Feet unit symbols are ft and '
'| Inch unit symbols are in, ", and ''
'| 6. Values without a unit symbol following it will be interpreted
'| as inches
'| 6 1/2 <= valid interpreted as inches
'| 12' 6.5 <= valid, same as 12' 6.5"
'|
'| 7. Feet values must always be followed by either ft or ' symbols
'| 12ft <= valid
'| 12' <= valid
'| 12 <= valid but interpreted as inches
'| 12 6.5" <= invalid
'|
'| 8. A feet value must always be before the inch value
'| 12 ft 6 1/2 in <= valid
'| 6 1/2 in 12 ft <= invalid
'|
'| 9. If input has both a feet and inch value then the feet value
'| must be a whole number
'| 12' 6 1/2" <= valid
'| 12.5' 1/2" <= invalid
'|
'| 10. If input is only a feet value then it may be entered as a whole
'| number or a decimal number. Never as a fraction
'| 12' <= valid
'| 12.75' <= valid
'| 12 3/4' <= invalid
'|
'| 11. Inches may be entered as a whole number, a whole number and a
'| fraction, just a fraction, or as decimal number
'| 6" <= valid
'| 6 1/2" <= valid
'| 6.5" <= valid
'| 13/2" <= valid
'|
'| Note: spaces are the only valid character to seperate whole number
'| from a fraction's numerator
'| 12' 6 1/2" <=valid
'| 12' 6-1/2" <=invalid
'|
'| 12. Components of a fraction must all be whole numbers
'| 6 1/2" <= valid
'| 6 1.5/2" <= invalid
'|
'| 13. Optionally may seperate feet and inches with a hyphen
'| 12'-6 1/2" <= valid
'|
'| Note: If ommiting a feet value and there is still a hyphen
'| before the inch value the result will be returned as negative
'| 12'-6" <= valid equal to 150 (inches)
'| -12'-6" <= valid equal to -150 (inches)
'| -6" <= valid equal to -6 (inches)
'| --6" <= invalid
'|
'|
'| 14. Spacing between negative symbol, unit symbols, and divisors
'| are all optional
'| ( 12 ' - 6 1 /2 " ) <= valid and equals (12'-6 1/2")
'|
'| 15. The following examples are valid and will all return
'| 150.5 (inches):
'| 12'-6 1/2"
'| 12' 6 1/2"
'| 12 ft 6.5 in
'| 12 ft 13/2 "
'| 150 1/2"
'| 150 1/2
'| 150.5
'| 12ft 6.5
'| 12.541666666'
'|
'| 16. An input that doesn't meet the conditions above will return
'| an error
'|
'| 17. Note there are no range limits on the numerical components
'| 12' 43 13/4" <= valid equal to 190.25 (inches)
'|
'| Post-condition: returns a double in inches unless specified
'| to return feet
'|
'| Parameters:
'| str -- String -- The input string to be parsed
'| returnInches -- Optional boolean -- If true (default) the function
'| will return a decimal value in inches. If false it will return
'| a value in feet.
'|
'| Returns: A double in inch units unless specified to be feet. Returns
'| an error code if the input string was invalid.
'*-------------------------------------------------------------------*/
Public Function FtIn2Dec(ByVal str As String, Optional returnInches As Boolean = True) As Variant
On Error GoTo ErrHandler:
Dim regEx As New RegExp 'Tools -> References -> Microsoft VBScript Regular Expressions 5.5
Dim matches As MatchCollection
If Trim(str & vbNullString) = vbNullString Then
FtIn2Dec = 0#
GoTo ExitHandler
ElseIf IsNumeric(str) Then
FtIn2Dec = CDbl(str)
GoTo ExitHandler
End If
Dim pattern As String
pattern = "^\s*(-|\()?\s*(?:(\d+\.\d*|\d*\.\d+)\s*(?:ft|')|(?:(\d+)\s*(?:ft|'))?\s*-?\s*(?:(\d*?)(?:\s*(\d+)\s*\/\s*(\d+))?|(\d*\.\d+|\d+\.\d*))\s*(?:in|""|'')?)\s*\)?\s*$"
Set regEx = New RegExp
With regEx
.Global = True
.MultiLine = False
.IgnoreCase = True
.pattern = pattern
Set matches = .Execute(str)
End With
If matches.Count = 0 Then GoTo ErrHandler
Dim feet_part As Double
Dim inch_part As Double
Dim inch_total As Double
Dim denominator As Integer
With matches(0)
denominator = CNum(.SubMatches(5))
If denominator = 0 Then denominator = 1
feet_part = CNum(.SubMatches(1)) + CNum(.SubMatches(2))
inch_part = CNum(.SubMatches(3)) + (CNum(.SubMatches(4)) / denominator) + CNum(.SubMatches(6))
End With
inch_total = feet_part * 12# + inch_part
If Not IsEmpty(matches(0).SubMatches(0)) Then inch_total = -1 * inch_total
If returnInches Then FtIn2Dec = inch_total Else FtIn2Dec = inch_total / 12#
ExitHandler:
Set regEx = Nothing
Set matches = Nothing
Exit Function
ErrHandler:
Debug.Print "Error converting feet & inches string to a decimal value."
FtIn2Dec = CVErr(2015)
Resume ExitHandler
End Function
'CDbl( ) on a blank string will throw an error. Needed this little helper
' function to quickly convert empty and blank string/variants to zero for
' the FtIn2Dec function.
Private Function CNum(ByVal str As Variant) As Double
If IsNumeric(str) Then CNum = CDbl(str) Else CNum = 0#
End Function