Converting various measurements to single format?

[orange]

I suggest you create a function. You would need to know the incoming measurement unit,
then an expression(vba code) to calculate the target unit.(inches)

Using a number found via Google

1 mm = 0.0393701 inches

sample:

Function MMToInches(NumMM As Double) As Double

Dim ConvFactor As Double
ConvFactor = 0.0393701 ' 1 mm = 0.0393701 inches
MMToInches = NumMM * ConvFactor

End Function

sample usage: if height is in mm



Select MMtoInches(Height) as HgtInches from tblExport

[natonstan]

I suggest you create a function. You would need to know the incoming measurement unit,
then an expression(vba code) to calculate the target unit.(inches)

Using a number found via Google

1 mm = 0.0393701 inches

sample:

Function MMToInches(NumMM As Double) As Double

Dim ConvFactor As Double
ConvFactor = 0.0393701 ' 1 mm = 0.0393701 inches
MMToInches = NumMM * ConvFactor

End Function

sample usage: if height is in mm

Select MMtoInches(Height) as HgtInches from tblExport

I was trying to do this using SQL, I came up with this but for some reason it just isn't working, I keep getting syntax errors, here's the example code:

SELECT [Part Number], ...,
CAST(CASE WHEN is_in_inches = 1 THEN Dimensions ELSE Dimensions / 25.4 END AS decimal(9, 2)) AS Dimensions, CAST(CASE WHEN is_in_inches = 1 THEN Height ELSE Height / 25.4 END AS decimal(9, 2)) AS Height,
...
FROM
CROSS APPLY (
SELECT CASE WHEN Unit IN ('In', 'Inch') THEN 1 ELSE 0 END AS is_in_inches
) AS ca1

Here's all of the fields in my external data file:

Part Number, Manufacturer Number, Dimensions, Height, Length, Width, Unit

Now Dimensions is simply blank right now, Height, Length, Width all have numbers, the Unit field is what indicates whether it is in 'in' or 'mm'

[CJ_London]

just use the example provided 3 times in a query

[orange]

I reread your post and see you want measurements in Units of feet.
Below is a sample function, a test routine and sample results.

CASE is not an acceptable part of Access SQL.

Function UnitsToFeet(NumUnits As Double, Unit As String) As Double
'
'
    Dim ConvFactor As Double: ConvFactor = 0
    Select Case Unit
    Case "MM", "mm.", "millimeter"
        ConvFactor = 0.00328084   ' 1 mm = 0.0.00328084 feet
    Case "cm", "cm.", "centimeter"
        ConvFactor = 0.0328084 ' 1 cm = 0328084 feet
    Case "m", "meter", "metre"
        ConvFactor = 0.328084  '1 m = 0.328084
    Case "in", "in.", "inch", "inches"
        ConvFactor = 0.083333336 '1 Inch = 0.083333336 feet.
    Case "ft", "ft.", "feet", "foot"
        ConvFactor = 1
    End Select
    UnitsToFeet = NumUnits * ConvFactor


End Function

Sample test routine:

Sub testcnv()
    Dim testValues(3) As Double
    Dim i As Integer, j As Integer
    Dim jUnit(3) As String

    jUnit(0) = "mm"
    jUnit(1) = "inch"
    jUnit(2) = "cm"
    jUnit(3) = "ft"

    testValues(0) = 1
    testValues(1) = 23.25
    testValues(2) = 800.9876
    testValues(3) = 0.5
    For j = 0 To 3
        For i = 0 To UBound(testValues)
            Debug.Print testValues(i) & " " & jUnit(j) & "  equals  " & UnitsToFeet(testValues(i), jUnit(j)) & " feet"
        Next i
        Debug.Print vbCrLf
    Next j
End Sub

Results of test:

1 mm  equals  0.00328084 feet
23.25 mm  equals  0.07627953 feet
800.9876 mm  equals  2.627912157584 feet
0.5 mm  equals  0.00164042 feet


1 inch  equals  0.083333336 feet
23.25 inch  equals  1.937500062 feet
800.9876 inch  equals  66.7489688026336 feet
0.5 inch  equals  0.041666668 feet


1 cm  equals  0.0328084 feet
23.25 cm  equals  0.7627953 feet
800.9876 cm  equals  26.27912157584 feet
0.5 cm  equals  0.0164042 feet


1 ft  equals  1 feet
23.25 ft  equals  23.25 feet
800.9876 ft  equals  800.9876 feet
0.5 ft  equals  0.5 feet