Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

pounds & ounces (noob alert)

Discussion in 'Information Technology' started by esa, Oct 28, 2005.

  1. esa

    esa
    Expand Collapse
    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
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "esa" <esa@discussions.microsoft.com> wrote in message
    news:8BFE4293-7EC6-4ACD-86BB-C94662249AE0@microsoft.com
    > 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 -----

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. '69 Camaro

    '69 Camaro
    Expand Collapse
    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.


    "esa" wrote:

    > 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
    >
     
  5. fredg

    fredg
    Expand Collapse
    Guest

    On Thu, 27 Oct 2005 16:55:02 -0700, esa wrote:

    > 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")

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  6. esa

    esa
    Expand Collapse
    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
     
  7. '69 Camaro

    '69 Camaro
    Expand Collapse
    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.


    "esa" wrote:

    > 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
    >
     
  8. esa

    esa
    Expand Collapse
    Guest

    Thanks you so much! It works great!

    :-D


    ~esa

    "'69 Camaro" wrote:

    > 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.
    >
    >
    > "esa" wrote:
    >
    > > 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
    > >
     
  9. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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.



    "esa" <esa@discussions.microsoft.com> wrote in message
    news:2F806AB6-9673-4526-8931-D30B2135502E@microsoft.com...
    > Thanks you so much! It works great!
    >
    > :-D
    >
    >
    > ~esa
    >
    > "'69 Camaro" wrote:
    >
    >> 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.
    >>
    >>
    >> "esa" wrote:
    >>
    >> > 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
    >> >
     
  10. esa

    esa
    Expand Collapse
    Guest

    Done.

    Thanks again!

    "'69 Camaro" wrote:

    > 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.
    >
    >
    >
    > "esa" <esa@discussions.microsoft.com> wrote in message
    > news:2F806AB6-9673-4526-8931-D30B2135502E@microsoft.com...
    > > Thanks you so much! It works great!
    > >
    > > :-D
    > >
    > >
    > > ~esa
    > >
    > > "'69 Camaro" wrote:
    > >
    > >> 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.
    > >>
    > >>
    > >> "esa" wrote:
    > >>
    > >> > 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
    > >> >

    >
    >
    >
     
  11. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Terrific! Much obliged!

    Gunny

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


    "esa" <esa@discussions.microsoft.com> wrote in message
    news:9D2645B7-0EA1-42CE-B591-77CB807F02D7@microsoft.com...
    > Done.
    >
    > Thanks again!
    >
    > "'69 Camaro" wrote:
    >
    >> 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.
    >>
    >>
    >>
    >> "esa" <esa@discussions.microsoft.com> wrote in message
    >> news:2F806AB6-9673-4526-8931-D30B2135502E@microsoft.com...
    >> > Thanks you so much! It works great!
    >> >
    >> > :-D
    >> >
    >> >
    >> > ~esa
    >> >
    >> > "'69 Camaro" wrote:
    >> >
    >> >> 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.
    >> >>
    >> >>
    >> >> "esa" wrote:
    >> >>
    >> >> > 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
     

Share This Page