HELP – How do I use Feet and Inches in my table

Topic

New Reply

Please Help,
I need to store feet and inches as “NUMBERS” not “TEXT” in a table because I need to add, subtract, and/or muliply certain measurements, Access2K will not let me do it. Any help would be greatly appreciated. THANX.

Reply | Quote

Replies

Store feet in one field and inches in another, both as numeric fields, OR convert all measurements to inches and store in just one numeric field.

Reply | Quote

If you are going to have to do arithmetic with the feet & inches, then you are probably going to have to store either inches or feet and decimal part of a foot in a data base field. Then when you display the value you will have to convert it back to feet and inches and either convert it to text formatted the way you want or displayed in to controls.

Reply | Quote

Thanks GUYS, but what I need is a little more involved, here is exactly what I need :

Users need to type in measurements as shown:
5/8″ or 3/4″ or 7/8″ or even 2′-3 5/8″

and since there are THOUSANDS of records ,each dimension different, it would be tedious to get him to convert all of those inches to decimal, I have to find a way to let him input it in foot and inch format and then take those values and run some math equations on them (so they have to stay numeric) I can convert them to decimal after he has typed them in, does anyone know how to take a text value of 1-3/8″ and convert it to numeric decimal then I can manipulate it however I see fit and then convert it back to feet and inches.

Any more ideas?

Reply | Quote

I would recommend using three textboxes on the form, one for feet, one for inches, and one for fractional inches. Then use the beforeupdate event procedure to convert those into inches.

Reply | Quote

If you decide to use this technique, then a VBA statement like the following will convert the three textbox values into inches:

Dim dInches As Double
    dInches = Forms("Form1")!Feet * 12 + Forms("Form1")!Inches + Eval(Forms("Form1")!Fraction)

Reply | Quote

If you decide to do some thing different from what Legare suggested (3 textboxes), you will need to acquire an “arithmetic parser”, a piece of code that will read 1ft3-5/8in (with or without embedded spaces) and convert it properly. Perhaps make the third textbox 16ths?

Reply | Quote

Can you agree a standard entry format with the user?

eg. feet ‘space’ inches ‘space’ fraction (as n/m without spaces), the feet and inches being required.

If you could then you could write a simple parser to convert to a numeric value. Also if this was a way to go I would suggest you consider storing the original entry as text in the table and as the numeric value so you could track down inconsistancies.

Reply | Quote

I think that storing the same value in two fields in the DB is a very bad idea, and is asking for future errors. Someone else may modify the system, and see the text field with the data in it and use it in a form or report. Then someone comes along and modifies one of the two fields in spreadsheet view, but not the other, then you have a difference in the displayed values and calculated values. If you want to keep it, then write it to a separate log file.

Reply | Quote

Generally it is not recommended at all to store the same piece of information in two fields. I would consider this as a special case in that the text entry is being parsed to provide a numeric value which is then used in calculations. I recommend storing the original entry so that it is what has been entered that is stored for cross checking if calculation based on the parsed value is incorrect. I don’t know what the further calculation involves. Storing of the numeric value is for use in these calculations, maybe it is not required to be static, maybe it could be dynamically calculated each time. Maybe the end result of the calculation is a candidate for storage.
One way to help stop a problem such as you describe could be to store this original text entry in a separate table, or give it the field a name such as ‘DO_NOT_USE__DimensionCrosscheckOnly’

I don’t like using a separate log file – the likelyhood that someone might modify/delete it I think is greater than if it is in a table.
re Spreadsheet view do you let your users operate directly in your tables! That is asking for trouble.

Reply | Quote

No, users are not allowed to use spreadsheet view. However, support people frequently use spreadsheet view when solving problems, and if that person is not the one that wrote the original, or even if it is and it has been a long time, one field could get changed and not the other.

Who do you allow access to your log files that might delete them? I prefer the log file since it would keep a running record of all changes instead of just the last change that would normally be kept in the DB. I also prefer keeping this kind of stuff out of the DB where it can affect performance, particularly if there are a number of such fields.

Reply | Quote

Separate logging file or field/table/database. Another person, not the original developer, can make changes by accident or design. It’s a matter of how one uses and creates the logging information. Someone has to access the information and manipulate it else why bother generating it.
As with everything there are various ways to achieve ones goals and quite often no obvious ‘best’ way as long the end result is achieved. What is ‘best’ for one situation is ‘wrong’ for another as the conditions and requirements vary from instance to instance.

The point I was trying to make is that IF the method of having a standard entry format and a DIY parser was chosen THEN it should be considered storing the users original entry. The example I gave was for cross checking if the parsing routine failed under certain circumstances. There could be other reasons for keeping the original entry. For example. Import into another country requires the documentation and packging description to match exactly, similarly for insurance of goods. Another is for stock-taking when comparing 24.375 on a printout with 2 Feet 3/8 inch on the box. I have no doubt you could think of other theoretical examples yourself.

Reply | Quote

To answer this question correctly more info is required.

The correct way of adding these together will depend on the accuracy required. The first question is what tolerances are allowed? For example if you convert a third (1/3) in to a decimal you get 0.333 recurring. If you now add three

Reply | Quote

First of all I must apologise for posting such incomplete work. The attached db converts feet, inches and fractions to a decimal and back again. However it will not work with odd numbers as denominators. I don

Reply | Quote