Discussion Locked

Access Function for Feet & Inches.

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Access Function for coverting Feet & Inches to a number. I have one for Excel (Cinches & Cfeet), but it does not work well in access. How can I get, easily, to add and average Ft. Inches, & Fractions? e.g. from 21' 11 3/4" = 263.75

5 replies

by bill_h

The two functions below will take care of the 'inches to feet & inches' and 'feet & inches to inches' conversions. Note: the 'inches to feet & inches' conversion works for inches expressed with decimals - I'm sure you can figure out (given the codefor 'feet & inches to inches') how to that function to handle fractions as well.

Function cvtInches(ByVal dblInches) As String
cvtInches = Int(dblInches / 12) & "'"
If (dblInches Mod 12) > 0 Then
cvtInches = cvtInches & Chr(32) & _ dblInches - (Int(dblInches / 12) * 12) & Chr(34)
End If
End Function

Function cvtFeet(ByVal sFeet As String) As Double
Dim nAposPos As Integer, nQtPos As Integer
Dim nSlash As Integer, nSpace As Integer
Dim nFeet As Integer, dInches As Double, dFraction As Double

'Check for Feet indicator
nAposPos = InStr(sFeet, "'")
If nAposPos > 0 Then
nFeet = Val(Mid$(sFeet, 1, nAposPos - 1)) * 12
sFeet = Trim$(Mid$(sFeet & " ", nAposPos + 1))
End If
'Check for Inches indicator
nQtPos = InStr(sFeet, Chr(34))
'Check for inches and fractions
nSpace = InStr(sFeet, " ")
If nSpace = 0 Then
'No space - no fraction
dInches = Val(Mid$(sFeet, 1, nQtPos - 1))
Else
'Get whole inches dInches = Val(Mid$(sFeet, 1, InStr(sFeet, " ") - 1))
'Check for Fraction indicator
nSlash = InStr(sFeet, "/")
If nSlash > 0 Then
If nSlash = Len(sFeet) - 1 Or Val(Mid$(sFeet, nSlash + 1, nQtPos - nSlash - 1)) = 0 Then'If denominator is missing or is zero
dFraction = 0
Else
'dFraction= numerator divided by denominator
dFraction = Val(Mid$(sFeet, nSpace + 1, nSlash - nSpace - 1)) / Val(Mid$(sFeet, nSlash + 1, nQtPos - nSlash - 1))
End If
End If
End If
cvtFeet = nFeet + dInches + dFraction
End Function

Hope this helps...

by estebandelatorre

For data entry, I recommend making a function to convert from inches to feet and viceversa (as the previous answer)
If you want to retreive data in inches but it is stored in feet, you can make a custom view with a calculated field (using the previous function) and you may be able to query the same database in feets or inches by only changing the source view.

This discussion is closed to new replies.