is this numeric conversion possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i work for a manufacturing company, our procduction facility uses the
following format for their mesurements: I .E . 4.33 , 4.5 .... etc...
however, I would like to have it in actuall inches... I.E. 4.33 = 4 ft 4
inches or 4 feet 6 inches. Is there a way to do this in a query formula?

Thanks,

Brook
 
Brook

I'm not aware of any built-in function that does that, but you could easily
"do the math" in your query, or in a user-defined function. From your
description, the "integer" portion of your number is the number of feet.
You can use the "integer divide" ("\") to get the whole number portion of,
say, "4.33".

If you subtract the integer divide answer (4) from your original value
(4.33), you are left with a decimal number that, from your description,
sounds like the portion of 12 inches. If you multiply that decimal
"fraction" times 12, you'll get the number of inches.

WARNING: the value you gave as a first example (4.33) is not exact. If you
carry out the above operations, you'll end up with 4 feet, 3.99 inches. You
may also need to "round" your inches...
 
Jeff,

Thanks for the tip. One thing I'm not sure of is how to split out the
integer in order to just have the decimal so that I can divide the decimal by
12.

Thanks brook
 
Good point! The integer divide works when you are dividing!!

Try the CInt([YourNumberField]) function to get the integer portion...

Jeff Boyce
<Access MVP>
 
Thanks Duane,

That is exactly what I needed...

Brook

Duane Hookom said:
To get inches, try something like
Inches: ([Measurement]-Int([Measurement]))*12

--
Duane Hookom
MS Access MVP


Brook said:
Jeff,

Thanks for the tip. One thing I'm not sure of is how to split out the
integer in order to just have the decimal so that I can divide the decimal
by
12.

Thanks brook
Click to expand...
Click to expand...
 
One more question... is there a way to add an identifier to the number such
as ' for feet or " for inches?

Brook

Duane Hookom said:
To get inches, try something like
Inches: ([Measurement]-Int([Measurement]))*12

--
Duane Hookom
MS Access MVP


Brook said:
Jeff,

Thanks for the tip. One thing I'm not sure of is how to split out the
integer in order to just have the decimal so that I can divide the decimal
by
12.

Thanks brook
Click to expand...
Click to expand...
 
I would not add it in the query. The best method would probably be to just
add a label in your form or report with the ' or ".

--
Duane Hookom
MS Access MVP


Brook said:
One more question... is there a way to add an identifier to the number
such
as ' for feet or " for inches?

Brook

Duane Hookom said:
To get inches, try something like
Inches: ([Measurement]-Int([Measurement]))*12

--
Duane Hookom
MS Access MVP


Brook said:
Jeff,

Thanks for the tip. One thing I'm not sure of is how to split out the
integer in order to just have the decimal so that I can divide the
decimal
by
12.

Thanks brook

:

Brook

I'm not aware of any built-in function that does that, but you could
easily
"do the math" in your query, or in a user-defined function. From your
description, the "integer" portion of your number is the number of
feet.
You can use the "integer divide" ("\") to get the whole number portion
of,
say, "4.33".

If you subtract the integer divide answer (4) from your original value
(4.33), you are left with a decimal number that, from your
description,
sounds like the portion of 12 inches. If you multiply that decimal
"fraction" times 12, you'll get the number of inches.

WARNING: the value you gave as a first example (4.33) is not exact.
If
you
carry out the above operations, you'll end up with 4 feet, 3.99
inches.
You
may also need to "round" your inches...

--
Good luck

Jeff Boyce
<Access MVP>

i work for a manufacturing company, our procduction facility uses
the
following format for their mesurements: I .E . 4.33 , 4.5 ....
etc...
however, I would like to have it in actuall inches... I.E. 4.33 = 4
ft
4
inches or 4 feet 6 inches. Is there a way to do this in a query
formula?

Thanks,

Brook
Click to expand...
Click to expand...
Click to expand...
 
Why would you not add it to a query ? is it possible? I know how to add the
label to the report for it. But would like to have it directly in my query if
possible...

thanks,

Brook

Duane Hookom said:
I would not add it in the query. The best method would probably be to just
add a label in your form or report with the ' or ".

--
Duane Hookom
MS Access MVP


Brook said:
One more question... is there a way to add an identifier to the number
such
as ' for feet or " for inches?

Brook

Duane Hookom said:
To get inches, try something like
Inches: ([Measurement]-Int([Measurement]))*12

--
Duane Hookom
MS Access MVP


Jeff,

Thanks for the tip. One thing I'm not sure of is how to split out the
integer in order to just have the decimal so that I can divide the
decimal
by
12.

Thanks brook

:

Brook

I'm not aware of any built-in function that does that, but you could
easily
"do the math" in your query, or in a user-defined function. From your
description, the "integer" portion of your number is the number of
feet.
You can use the "integer divide" ("\") to get the whole number portion
of,
say, "4.33".

If you subtract the integer divide answer (4) from your original value
(4.33), you are left with a decimal number that, from your
description,
sounds like the portion of 12 inches. If you multiply that decimal
"fraction" times 12, you'll get the number of inches.

WARNING: the value you gave as a first example (4.33) is not exact.
If
you
carry out the above operations, you'll end up with 4 feet, 3.99
inches.
You
may also need to "round" your inches...

--
Good luck

Jeff Boyce
<Access MVP>

i work for a manufacturing company, our procduction facility uses
the
following format for their mesurements: I .E . 4.33 , 4.5 ....
etc...
however, I would like to have it in actuall inches... I.E. 4.33 = 4
ft
4
inches or 4 feet 6 inches. Is there a way to do this in a query
formula?

Thanks,

Brook
Click to expand...
Click to expand...
Click to expand...
 
I prefer to keep my queries clean. You may want to add or subtract or sum or
otherwise compute some value based on the Inches column. If you have added
an apostrophe, you lose the opportunity. It is very easy to use a control
source in a report like:
=[Inches] & "'"
You don't need the apostrophe in your query so don't put it there.

--
Duane Hookom
MS Access MVP


Brook said:
Why would you not add it to a query ? is it possible? I know how to add
the
label to the report for it. But would like to have it directly in my query
if
possible...

thanks,

Brook

Duane Hookom said:
I would not add it in the query. The best method would probably be to
just
add a label in your form or report with the ' or ".

--
Duane Hookom
MS Access MVP


Brook said:
One more question... is there a way to add an identifier to the number
such
as ' for feet or " for inches?

Brook

:

To get inches, try something like
Inches: ([Measurement]-Int([Measurement]))*12

--
Duane Hookom
MS Access MVP


Jeff,

Thanks for the tip. One thing I'm not sure of is how to split out
the
integer in order to just have the decimal so that I can divide the
decimal
by
12.

Thanks brook

:

Brook

I'm not aware of any built-in function that does that, but you
could
easily
"do the math" in your query, or in a user-defined function. From
your
description, the "integer" portion of your number is the number of
feet.
You can use the "integer divide" ("\") to get the whole number
portion
of,
say, "4.33".

If you subtract the integer divide answer (4) from your original
value
(4.33), you are left with a decimal number that, from your
description,
sounds like the portion of 12 inches. If you multiply that decimal
"fraction" times 12, you'll get the number of inches.

WARNING: the value you gave as a first example (4.33) is not
exact.
If
you
carry out the above operations, you'll end up with 4 feet, 3.99
inches.
You
may also need to "round" your inches...

--
Good luck

Jeff Boyce
<Access MVP>

i work for a manufacturing company, our procduction facility uses
the
following format for their mesurements: I .E . 4.33 , 4.5 ....
etc...
however, I would like to have it in actuall inches... I.E. 4.33 =
4
ft
4
inches or 4 feet 6 inches. Is there a way to do this in a query
formula?

Thanks,

Brook
Click to expand...
Click to expand...
Click to expand...
 
Brook said:
Why would you not add it to a query ? is it possible? I know how to add the
label to the report for it. But would like to have it directly in my query if
possible...
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually, metric indications (' and ", or ml or mm, etc.) are considered
part of the display not part of the data. Also, if you add the metric
indication to the number, that number becomes a string & will not sort
in numeric order, but in character order. E.g.:

As a sorted number w/ a metric indication added by the display process
(a report or form):

1' 6"
2' 3"
2' 4"
10' 5"
11" 6"

As a sorted string:

1' 6"
10' 5"
11' 6"
2' 3"
2' 4"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQhfeaYechKqOuFEgEQK++gCfai0jWs6OvWeUYTtncSsbLA79DkMAoN+x
fSpoWn4hK4BfVaAg2p5guOCH
=KwLF
-----END PGP SIGNATURE-----
 
Back
Top