pounds & ounces (noob alert)

[Guest]

When calculating weight totals (in pounds) in Access, the resulting number is
always a decimal (i.e., 6.5 pounds).

How can I get Access to convert "6.5 pounds" to "6 pounds, 8 ounces"?

TIA!

~esa

 

[Dirk Goldgar]

When calculating weight totals (in pounds) in Access, the resulting
number is always a decimal (i.e., 6.5 pounds).

How can I get Access to convert "6.5 pounds" to "6 pounds, 8 ounces"?

Here's a quick, practically untested function to do it.

'----- start of code -----
Function fncPoundsAndOunces(pdblWeight As Double) As String

Dim lngPounds As Long
Dim sngOunces As Single
Dim strResult As String

lngPounds = Fix(pdblWeight)
sngOunces = (pdblWeight - lngPounds) * 16

strResult = lngPounds & " pound"
If lngPounds <> 1 Then
strResult = strResult & "s"
End If

strResult = strResult & ", " & sngOunces & " ounce"
If sngOunces <> 1 Then
strResult = strResult & "s"
End If

fncPoundsAndOunces = strResult

End Function
'----- end of code -----

 

[Guest]

If this is in a form, then you could use the form's OnCurrent( ) and the text
box's OnAfterUpdate( ) events to call a function that makes the calculation.
Try:

Private Sub Form_Current()
Me!txtWeight.Value = calcLbsAndOz(Me!txtLbs.Value)
End Sub

Private Sub txtLbs_AfterUpdate()
Me!txtWeight.Value = calcLbsAndOz(Me!txtLbs.Value)
End Sub

Public Function calcLbsAndOz(dLbs As Double) As String

On Error GoTo ErrHandler

Dim nLbs As Long
Dim nOz As Long
Dim dFraction As Double

nLbs = Fix(dLbs)
dFraction = dLbs - nLbs
nOz = Round(dFraction * 16)

calcLbsAndOz = nLbs & " pounds, " & nOz & " ounces"

Exit Function

ErrHandler:

MsgBox "Error in calcLbsAndOz( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Function ' calcLbsAndOz( )

.. . . where txtLbs is the name of the text box displaying the decimal number
(6.5), and txtWeight is the name of the text box displaying the "pounds and
ounces" text string.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[fredg]

When calculating weight totals (in pounds) in Access, the resulting number is
always a decimal (i.e., 6.5 pounds).

How can I get Access to convert "6.5 pounds" to "6 pounds, 8 ounces"?

TIA!

~esa

IIf(Int([Weight])/16 >=2,Int([Weight]/16) & " pounds " & [Weight] Mod
16 & " ounces",Int([Weight]/16) & " pound " & [Weight] Mod 16 & "
ounces")

 

[Guest]

Wow! Thanks for all the quick replies!
The codes you suggested work great!

I just need a small modification...

I'd like to convert the remaining ounces (i.e., the ounces right of the
decimal point) into grams, so that

"6.64 pounds"
displays something like
"6 pounds, 10 ounces, and 4.95 grams"

Thanks again for all the help!

~esa

 

[Guest]

Try:

Public Function calcLbsAndOz(sglLbs As Single) As String

On Error GoTo ErrHandler

Dim nLbs As Long
Dim nOz As Long
Dim sglLbsFraction As Single
Dim sglOzFraction As Single
Dim sglGrams As Single

Const OZ_PER_LB As Long = 16
Const G_PER_OZ As Single = 28.375

nLbs = Fix(sglLbs)
sglLbsFraction = sglLbs - nLbs
nOz = Fix(sglLbsFraction * OZ_PER_LB)
sglOzFraction = (sglLbsFraction * OZ_PER_LB) - nOz
sglGrams = sglOzFraction * G_PER_OZ

calcLbsAndOz = nLbs & " pounds, " & nOz & " ounces, " & _
Format(sglGrams, "0.00") & " grams"

Exit Function

ErrHandler:

MsgBox "Error in calcLbsAndOz( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Function ' calcLbsAndOz( )

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks you so much! It works great!

:-D


~esa

Try:

Public Function calcLbsAndOz(sglLbs As Single) As String

On Error GoTo ErrHandler

Dim nLbs As Long
Dim nOz As Long
Dim sglLbsFraction As Single
Dim sglOzFraction As Single
Dim sglGrams As Single

Const OZ_PER_LB As Long = 16
Const G_PER_OZ As Single = 28.375

nLbs = Fix(sglLbs)
sglLbsFraction = sglLbs - nLbs
nOz = Fix(sglLbsFraction * OZ_PER_LB)
sglOzFraction = (sglLbsFraction * OZ_PER_LB) - nOz
sglGrams = sglOzFraction * G_PER_OZ

calcLbsAndOz = nLbs & " pounds, " & nOz & " ounces, " & _
Format(sglGrams, "0.00") & " grams"

Exit Function

ErrHandler:

MsgBox "Error in calcLbsAndOz( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Function ' calcLbsAndOz( )

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

['69 Camaro]

You're very welcome. It appears that you've marked my post above as an
answer to your question. Unfortunately, the Web portal is still rather
buggy and it didn't recognize you as the original poster of the question.
Would you please do me a favor and sign in again to the Microsoft Online
Community with your .Net Passport and try to mark the "Did this post answer
your question?" question on my previous post until a green check mark shows
up? (Refresh the page about a minute later and the green check mark should
appear.)

Thanks! It's greatly appreciated.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Guest]

Done.

Thanks again!

You're very welcome. It appears that you've marked my post above as an
answer to your question. Unfortunately, the Web portal is still rather
buggy and it didn't recognize you as the original poster of the question.
Would you please do me a favor and sign in again to the Microsoft Online
Community with your .Net Passport and try to mark the "Did this post answer
your question?" question on my previous post until a green check mark shows
up? (Refresh the page about a minute later and the green check mark should
appear.)

Thanks! It's greatly appreciated.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

['69 Camaro]

Terrific! Much obliged!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.