[AccessD] Converting Imperial measures

[Gustav Brock]

Hi all

I realised that it takes a little more to do this properly.

So, I took my rounding functions - which are for decimals, of course, thus for Base 10 numbers - and modified it for Base 2 rounding.

The reason is, that you need to convert between decimals (like 0.125) and fractions (like 1/8).
For many values, there will be no exact conversion, thus you need high precision for reliable results. That can be achieved by using Decimal.

You'll find a detailed description here:

 "Round by the power of two"
https://www.experts-exchange.com/articles/31859/Round-by-the-power-of-two.html

So much for the calculation.
What's missing, is to parse and format the imperial expressions.

Again, this is not as simple as one may think, indeed not if you wish to have generic functions that will cover just about any read-world scenario.
The functions can be found here:

"Convert and format imperial distance (feet and inches) with high precision"
https://www.experts-exchange.com/articles/31931/Convert-and-format-imperial-distance-feet-and-inches-with-high-precision.html

All code - which is tested for both Access and Excel - can be found at GitHub:
https://github.com/GustavBrock/VBA.Round.git

Please let me now if you see anything that needs a correction.
 
/gustav

________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Gustav Brock <gustav at cactus.dk>
Sendt: 21. februar 2018 09:59:15
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Converting Imperial measures

Hi all

And yes a small addition ("+0") to also accept Null values and empty strings and return zero for these:

DecimalFeet =
  Eval(Replace(Replace(Replace(Replace("+0" & m, """", ""), "'", "*12 "), " ", "+"), "-", "+")) / 12

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Gustav Brock
Sendt: 16. februar 2018 16:38
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] Converting Imperial measures

Hi all

One more Replace is needed to accept strings with a dash like 8' 5-7/16":

DecimalFeet =
  Eval(Replace(Replace(Replace(Replace(m, """", ""), "'", "*12 "), " ", "+"), "-", "+")) / 12

When DO the US enter modern time and switch to decimals?

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Gustav Brock
Sendt: 16. februar 2018 15:59
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [AccessD] Converting Imperial measures

Hi all

Good examples for usage of Eval are rare, but here is one - a one-liner for converting Imperial feet-inch measures (string) to decimal feet (double):

DecimalFeet = Eval(Replace(Replace(Replace([Measurement], """", ""), "'", "*12 "), " ", "+")) / 12

It will accept expressions like:

6'
7"
7 3/4"
6'7"
6' 7"
6'  5 3/16"

/gustav