Dimensional data entry

[Zydeceltico]

Hi All,

I am QC inspector for a fabricating company.

We need to record data for length in feet and inches (eg., 12' 3-7/16"). We record Required Length and Actual Length. We would like to "do math" later at some point. In other words, I am initially thinking I would like to store the data as numbers rather than text.

We are recording data via a form (frmWeldingAssembliesInspections) bound to a table (tblWeldAssembiesInspections).

It appears to me that the only way to keep all data as numbers (instead of 12' 3-7/16" as text) is to have three fields in my table for each measurement (i.e., a field for "feet"; a field for "inches"; and a field for any fractional amount.

I'm wondering if anyone has found a graceful means of doing this differently. I'd love to not have 3 fields for a single measurement - but I get it - if that's the only way.

Thanks,

Tim

 

[June7]

I faced this issue. Required input in decimal feet or decimal inches in one field. If you do use 3 fields will need calcs to convert to one unit type and decimals for aggregate calcs anyway.

 

[isladogs]

I'm wondering if anyone has found a graceful means of doing this differently. I'd love to not have 3 fields for a single measurement - but I get it - if that's the only way.

You could use the metric system!

As I doubt that would be acceptable in USA, suggest you store data in inches and using decimals and convert back for display purposes only
e.g. 12 feet 3 7/16 inch => 147.4375 inch

 

[Zydeceltico]

HA! Yeah - metric would definitely help!

But you're correct in your assumption. Gotta stick feet and inches.

Have you any idea what this structure would like for data entry on a form?

Am I still stuck with 3 fields for data entry? If Americans read tape measures solely in inches it would also make my life a whole lot easier - that's how I've read a tape for decades (I'm a fabricator also) - but my colleagues are holding out with feet and inches (and fractions).

 

[isladogs]

Store as a double number datatype but use 3 text boxes for data entry on the form. The fraction textbox could still be a pain - possibly use an input mask for this.

Using the double number will make any calculations easier to administer

Displaying the decimal part of the result as a fraction will need a lookup table or a function to convert back. For example:
https://www.access-programmers.co.uk/forums/showthread.php?t=16548
https://www.pcreview.co.uk/threads/can-you-format-numbers-in-access-as-a-fraction-like-in-excel.3482337/

 

[Zydeceltico]

I like that. I am already using a lookup table elsewhere for positive and negative fractions for a tolerance measurement. I can do that that way. Easy enough.

I'm an access semi-noob. How do I use 3 text boxes to put data into a single field in the table?

Actually, I would use only 1 text box ("Feet") and 2 combo boxes that point to lookup tables (one for "Inches" and the second for "Fractional Amounts").

 

[June7]

Use code to calculate value from the inputs and save to field. I use only VBA.

 

[isladogs]

Actually, I would use only 1 text box ("Feet") and 2 combo boxes that point to lookup tables (one for "Inches" and the second for "Fractional Amounts").

That's a good idea ... but why not use a combo for the feet as well
Depending on how your form is designed, use VBA code to concatenate the values
So if your controls are called cboFeet, cbInch, cboFraction, and your lookup table is tblFractDec use something like:

RequiredLength: Nz(12*cboFeet,0)) + Nz(cboInch,0) + Nz(DLookup("Dec", "tblFractDec", "Fraction = '" & cboFraction & "'"),0)

The Nz functions allow for any blanks in the combo boxes

 

[Zydeceltico]

Ridders,

That makes some sense to me. Thank you.

I'll play around with it and see if I am as capable as I hope before I post my next question.

Thanks!