Storing legacy values - feet, inches, fractions (1 Viewer)

matt beamish · Sep 5, 2009

Hi. I am designing a database for the compilation of UK boat registration, gauging and carrying information from the 19th and 20th centuries. In the journals to be transcribed, boat dimensions are recorded in feet, inches and fractions of an inch. I need the data input to also be in this form even if they are stored as binaries. Has anyone a function that can cope with this - any other advice appreciated.ThanksMatt

Uncle Gizmo · Sep 5, 2009

Would a simple text string be suitable?

3ft 12" 3/8"

Galaxiom · Sep 6, 2009

Entering strings with quote marks (for feet' and inches") is messy. It is much better to limit the characters to those on the numeric pad. A good data entry operator will be much faster this way.

Use three fields. Feet, Inches. FractInches. The feet and whole inches field are straightforward. Make the tab sequence through these three fields. Once again fast for keyboard entry. Design it for keyboarding and you will be amazed with the potential speed.

Store the fractional inches as a quantity of some suitable measure such as 1/8s (or as much as 1/128s in some industries).

Use a combobox storing the fractional quantity but showing the rationalised fraction. For entry use autocompletion.

Enter 7/ and the denominator defaults to 8 because that is the most likely. Enter 9/ and the denominator defaults to 16. etc. Keep typing. The second character of the denominator. 11/3 immediately defaults 11/32.

This save a keystroke and prevents the entry of 3/34 etc.

This will take quite a bit of VBA but much better for a entry operator with less errors. It is very efficient for extracting real dimensions.

In the drop down for the combo use plain alphanumeric sorting on the text. 1/8 - 1/16 - 1/32 not by magnitude as in 1/8 - 5/32 - 3/16
Edit: Actually alphanumeric sorting won't work. Use a SortOrder field in the lookup table.

Uncle Gizmo · Sep 6, 2009

I was thinking along slightly different lines, if there will be the necessity to make calculations based on these measurements then my guess is that the value should be stored as the lowest common denominator, let’s take eighths as your example, and something 5/8 long would be “5” something a foot long would be 12 times eight = 96.

Galaxiom · Sep 6, 2009

The important part of the problem is simplicity and validation of the data entry.

Uncle Gismo's method of storage may be a valid alternative to the three field system as storage. It dempends on the nature of the records.

The important facet of the problem is the presentation on the form. If you have a singe control bound to a field in the structure N/8, the complexity of parsing any entry will be significant. It would also require a vast number of lookup records to convert the 1/8s into a presentable format including feet inches and fractions of an inch.

The feet inches and fractions are all going to need separate handling in the VBA of any conversion or autocomplete procedure. There are advantages to keeping them apart from from right through from form to the table.

The three field system also has a subtle presentation advantage in the entry of 13 inches where the context is in the order of magnitude of inches rather than having a single entry of 1' 1" when all others are only recorded in inches.

gemma-the-husky · Sep 6, 2009

galaxiom

thats a slick idea - i suppose it only works if you know what fractions you will enter, and limit the input to the list. Or would yoiu have a lookup table, and let users add their own fractions via not in list?

matt beamish · Sep 6, 2009

Thanks for replies folks. It seems to me that the most important element is whether or not we want to do any calcs on these dimensions in the future. Stored as 3 separate fields they could be concatenated for smoother presentation and the feet and inches fields would sort easily enough, but I guess not the fractions without a function to decimalise them at the time.

Will consult data compilers to see what they think.

Thanks again

Matt

NigelShaw · Sep 6, 2009

Hi,

here is a simple db to convert imperial ( feet , inches ) to metric

it is based on 4 boxes

feet
inches
numerator / nominator ( fraction )

im sure it could be manipulated to break a single measurement down
i.e 3' 2 3/4" ( 3 foot 2 & 3/4 inches )

Code:

Public Sub ImpToDec()
    Dim Foot As Single
    Dim Inch As Single
    Dim Numerator As Single
    Dim Nominator As Single

    Dim Fract As Single

    Dim ImpVal As Double
    Dim MetricVal As Double
    Dim MetricConv As Double
    
    'set imperial to metric conversion 1" = 25.4mm
    MetricConv = 25.4
    
    'get imperial sizes
    Foot = Nz(Me.txtfeet, 0)
    Inch = Nz(Me.txtinch, 0)
    Numerator = Nz(Me.txtnumerator, 0)
    Nominator = Nz(Me.txtNominator, 0)

    'handle the non fraction
    If Numerator = 0 Then
        Nominator = 0
        ImpVal = ((Foot * 12) + Inch)
        
        MetricVal = ImpVal * MetricConv
        
        Me.txtMetric = MetricVal
    Else
    'make sure the fraction is correct 16/15 is incorrect 15/16 is correct
        If Numerator > Nominator Then
            MsgBox ("your fraction is incorrect")
            Me.txtnumerator.SetFocus
        Else
            'calculate the fraction to a decimal
            Fract = Numerator / Nominator
            
            'mutiply feet by 12 to get inches plus inches plus decimal
            ImpVal = ((Foot * 12) + Inch + Fract)

            'convert to metric
            MetricVal = ImpVal * MetricConv

            Me.txtMetric = MetricVal

        End If
    End If

anyway, it only took about 15 mins to set up as its basic math.

good luck

nidge

NigelShaw · Sep 6, 2009

Hi,

you can also split the fraction with the following-

Code:

Public strFullFraction As String

Public Function SplitFractionRight(strFullFraction As String) As String
    SplitFractionRight = Right(strFullFraction, Len(strFullFraction) _
    - InStrRev(strFullFraction, "/"))
End Function

Public Function SplitFractionLeft(strFullFraction As String) As String
    SplitFractionLeft = Left(strFullFraction, Len(strFullFraction) _
    - InStrRev(strFullFraction, "/"))
End Function

Public Sub Fraction()
Dim FracLeft As single
Dim FracRight As Single

strFullFraction = "3/4" ' or your forms textbox

FracLeft =  SplitFractionLeft(strFullFraction)
FracRight = SplitFractionRight(strFullFraction)

'use FracLeft & FracRight as your spilt fraction

End Sub

Nidge

Galaxiom · Sep 7, 2009

gemma-the-husky said:
galaxiom

thats a slick idea - i suppose it only works if you know what fractions you will enter, and limit the input to the list. Or would yoiu have a lookup table, and let users add their own fractions via not in list?

I would probably have a lookup table of allowed fractions unless I was sure the data would never go beyond 1/8s. I generally prefer lookups to list row sources anyway and I don't really like normalising combos with list type row sources, especially big lists.

In most cases the largest expected denominator could be anticipated. I don't expect boat dimensions would need to be precise beyond 1/16 inches.

However to maintain generality I would probably consider working in 1/128 inches and have a user setting to choose the required precision. Beyond 1/128s the old British system abandons the binary progression and moves into thousandths of an inch so that would require a new approach. (Unless the database recording the crankshaft journal diameter this should not be necessary.

)

If too little precision was opted for at the start this setting could be raised without affecting the data.

NigelShaw · Sep 7, 2009

Hi

in the case of boat building, I doubt if the measurements stretched any further than 1/32 inch as this fraction alone is less than 1mm

being from a Joinery trade and originally working imperial, I never went past 1/32. Infact, I never went past 1/16 but I was never that accurate!

You could have a combo box with your fractions and spit the fraction with my above code then it's just a case of arithematic to decimalize your imperial value

Nidge

Storing legacy values - feet, inches, fractions (1 Viewer)

matt beamish

Registered User.

Uncle Gizmo

Nifty Access Guy

Galaxiom

Super Moderator

Uncle Gizmo

Nifty Access Guy

Galaxiom

Super Moderator

gemma-the-husky

Super Moderator

matt beamish

Registered User.

NigelShaw

Registered User.

Attachments

NigelShaw

Registered User.

Galaxiom

Super Moderator

NigelShaw

Registered User.

Similar threads

Users who are viewing this thread