Welcome to SPN

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

Sign Up Now!

Currency Rounding and Splitting Pounds and Pence

Discussion in 'Information Technology' started by Simon Harris, Jul 28, 2006.

  1. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Hi All,

    I am writing a report in Access 2003 which is for invoicing. I need to
    display a currency value, but this needs to be displayed in two seperate
    boxes - One for pounds and one for pence, both seeded from the same table
    column. The table column has a currency data type.

    I have two problems:

    1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I would
    like this displayed as 0.30 (30 pence)

    2) Can anyone recommend a way of splitting the pounds and pence values? I
    did think about using Split (Not tried yet) but then how would I tell Access
    what element of the array that I need?

    Any help will be much appreciated.

    Thanks!
    Simon.

    --
    -
    * Please reply to group for the benefit of all
    * Found the answer to your own question? Post it!
    * Get a useful reply to one of your posts?...post an answer to another one
    * Search first, post later : http://www.google.co.uk/groups
    * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
     
  2. Loading...

    Similar Threads Forum Date
    Controversy Surrounding Nanakshahi Sikh Calender Sikh Sikhi Sikhism Dec 3, 2009
    Controversy Surrounding Prof Darshan Singh Ji Hard Talk Nov 14, 2009

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Pounds:
    =Fix([InvoiceTotal])

    Pence:
    = [InvoiceTotal] - Fix([InvoiceTotal])

    Check that Fix() gives you the right answers for negative values, rather
    than Int().

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    >
    > I am writing a report in Access 2003 which is for invoicing. I need to
    > display a currency value, but this needs to be displayed in two seperate
    > boxes - One for pounds and one for pence, both seeded from the same table
    > column. The table column has a currency data type.
    >
    > I have two problems:
    >
    > 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I would
    > like this displayed as 0.30 (30 pence)
    >
    > 2) Can anyone recommend a way of splitting the pounds and pence values? I
    > did think about using Split (Not tried yet) but then how would I tell
    > Access what element of the array that I need?
    >
    > Any help will be much appreciated.
    >
    > Thanks!
    > Simon.
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    As Always Several Ways

    PoundAmount = TotalAmount \1 '(Whole number Division)
    -- Or --
    PoundAmount = Clng(TotalAmount)

    And then
    PenceAmount=Format(Round(TotalAmount-PoundAmount,2)*100,"00")

    HTH

    Pieter


    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    > Hi All,
    >
    > I am writing a report in Access 2003 which is for invoicing. I need to
    > display a currency value, but this needs to be displayed in two seperate
    > boxes - One for pounds and one for pence, both seeded from the same table
    > column. The table column has a currency data type.
    >
    > I have two problems:
    >
    > 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I would
    > like this displayed as 0.30 (30 pence)
    >
    > 2) Can anyone recommend a way of splitting the pounds and pence values? I
    > did think about using Split (Not tried yet) but then how would I tell
    > Access what element of the array that I need?
    >
    > Any help will be much appreciated.
    >
    > Thanks!
    > Simon.
    >
    > --
    > -
    > * Please reply to group for the benefit of all
    > * Found the answer to your own question? Post it!
    > * Get a useful reply to one of your posts?...post an answer to another one
    > * Search first, post later : http://www.google.co.uk/groups
    > * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Try using the Format function:

    =Format(Round(CCur(InvoiceTotal),2), "0.00")

    In fact, see whether this is sufficient for your needs:

    =Format(CCur(InvoiceTotal),"0.00")

    For that matter, why isn't your field already Currency if you're dealing
    with money?

    To split a value into pounds and pence, you can use the Int function to get
    the pounds, and MyValue - Int(MyValue) to get the pence.

    If it were a string, and you wanted to use Split, Split(MyValue, ".")(0)
    would give you the pounds, while Split(MyValue, ".")(1) would give you the
    pence.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    > Hi All,
    >
    > I am writing a report in Access 2003 which is for invoicing. I need to
    > display a currency value, but this needs to be displayed in two seperate
    > boxes - One for pounds and one for pence, both seeded from the same table
    > column. The table column has a currency data type.
    >
    > I have two problems:
    >
    > 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I would
    > like this displayed as 0.30 (30 pence)
    >
    > 2) Can anyone recommend a way of splitting the pounds and pence values? I
    > did think about using Split (Not tried yet) but then how would I tell

    Access
    > what element of the array that I need?
    >
    > Any help will be much appreciated.
    >
    > Thanks!
    > Simon.
    >
    > --
    > -
    > * Please reply to group for the benefit of all
    > * Found the answer to your own question? Post it!
    > * Get a useful reply to one of your posts?...post an answer to another one
    > * Search first, post later : http://www.google.co.uk/groups
    > * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
    >
    >
     
  6. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Thank you all very much for your replies.

    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    > Hi All,
    >
    > I am writing a report in Access 2003 which is for invoicing. I need to
    > display a currency value, but this needs to be displayed in two seperate
    > boxes - One for pounds and one for pence, both seeded from the same table
    > column. The table column has a currency data type.
    >
    > I have two problems:
    >
    > 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I would
    > like this displayed as 0.30 (30 pence)
    >
    > 2) Can anyone recommend a way of splitting the pounds and pence values? I
    > did think about using Split (Not tried yet) but then how would I tell
    > Access what element of the array that I need?
    >
    > Any help will be much appreciated.
    >
    > Thanks!
    > Simon.
    >
    > --
    > -
    > * Please reply to group for the benefit of all
    > * Found the answer to your own question? Post it!
    > * Get a useful reply to one of your posts?...post an answer to another one
    > * Search first, post later : http://www.google.co.uk/groups
    > * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
    >
     
  7. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Thanks, Fix([InvoiceTotal]) seems to work AOK. Your suggetstion for Pence
    returns 0.302, which is the original value.

    Any suggestions?

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:O0$CLOzpGHA.756@TK2MSFTNGP05.phx.gbl...
    > Pounds:
    > =Fix([InvoiceTotal])
    >
    > Pence:
    > = [InvoiceTotal] - Fix([InvoiceTotal])
    >
    > Check that Fix() gives you the right answers for negative values, rather
    > than Int().
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    > news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    >>
    >> I am writing a report in Access 2003 which is for invoicing. I need to
    >> display a currency value, but this needs to be displayed in two seperate
    >> boxes - One for pounds and one for pence, both seeded from the same table
    >> column. The table column has a currency data type.
    >>
    >> I have two problems:
    >>
    >> 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I
    >> would like this displayed as 0.30 (30 pence)
    >>
    >> 2) Can anyone recommend a way of splitting the pounds and pence values? I
    >> did think about using Split (Not tried yet) but then how would I tell
    >> Access what element of the array that I need?
    >>
    >> Any help will be much appreciated.
    >>
    >> Thanks!
    >> Simon.

    >
    >
     
  8. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Ok, apologies - I've just worked out that my value of 0.302 obviously is
    pence only, so the value being returned is correct.

    Can you advise how I can display this as 30, i.e. Rounded without the
    decimal?

    Thankyou.

    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:%23e%23geS0pGHA.148@TK2MSFTNGP04.phx.gbl...
    > Thanks, Fix([InvoiceTotal]) seems to work AOK. Your suggetstion for Pence
    > returns 0.302, which is the original value.
    >
    > Any suggestions?
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:O0$CLOzpGHA.756@TK2MSFTNGP05.phx.gbl...
    >> Pounds:
    >> =Fix([InvoiceTotal])
    >>
    >> Pence:
    >> = [InvoiceTotal] - Fix([InvoiceTotal])
    >>
    >> Check that Fix() gives you the right answers for negative values, rather
    >> than Int().
    >>
    >> --
    >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    >> news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    >>>
    >>> I am writing a report in Access 2003 which is for invoicing. I need to
    >>> display a currency value, but this needs to be displayed in two seperate
    >>> boxes - One for pounds and one for pence, both seeded from the same
    >>> table column. The table column has a currency data type.
    >>>
    >>> I have two problems:
    >>>
    >>> 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I
    >>> would like this displayed as 0.30 (30 pence)
    >>>
    >>> 2) Can anyone recommend a way of splitting the pounds and pence values?
    >>> I did think about using Split (Not tried yet) but then how would I tell
    >>> Access what element of the array that I need?
    >>>
    >>> Any help will be much appreciated.
    >>>
    >>> Thanks!
    >>> Simon.

    >>
    >>

    >
    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Look in my answer

    Combined With Allen's way of doing it

    Pence:
    = Round([InvoiceTotal] - Fix([InvoiceTotal]);2)*100

    Pieter

    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:%23XYgTV0pGHA.1440@TK2MSFTNGP03.phx.gbl...
    > Ok, apologies - I've just worked out that my value of 0.302 obviously is
    > pence only, so the value being returned is correct.
    >
    > Can you advise how I can display this as 30, i.e. Rounded without the
    > decimal?
    >
    > Thankyou.
    >
    > "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    > news:%23e%23geS0pGHA.148@TK2MSFTNGP04.phx.gbl...
    >> Thanks, Fix([InvoiceTotal]) seems to work AOK. Your suggetstion for Pence
    >> returns 0.302, which is the original value.
    >>
    >> Any suggestions?
    >>
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:O0$CLOzpGHA.756@TK2MSFTNGP05.phx.gbl...
    >>> Pounds:
    >>> =Fix([InvoiceTotal])
    >>>
    >>> Pence:
    >>> = [InvoiceTotal] - Fix([InvoiceTotal])
    >>>
    >>> Check that Fix() gives you the right answers for negative values, rather
    >>> than Int().
    >>>
    >>> --
    >>> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >>> Tips for Access users - http://allenbrowne.com/tips.html
    >>> Reply to group, rather than allenbrowne at mvps dot org.
    >>>
    >>> "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    >>> news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    >>>>
    >>>> I am writing a report in Access 2003 which is for invoicing. I need to
    >>>> display a currency value, but this needs to be displayed in two
    >>>> seperate boxes - One for pounds and one for pence, both seeded from the
    >>>> same table column. The table column has a currency data type.
    >>>>
    >>>> I have two problems:
    >>>>
    >>>> 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I
    >>>> would like this displayed as 0.30 (30 pence)
    >>>>
    >>>> 2) Can anyone recommend a way of splitting the pounds and pence values?
    >>>> I did think about using Split (Not tried yet) but then how would I tell
    >>>> Access what element of the array that I need?
    >>>>
    >>>> Any help will be much appreciated.
    >>>>
    >>>> Thanks!
    >>>> Simon.
    >>>
    >>>

    >>
    >>

    >
    >
     
  10. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Look in my answer

    Combined With Allen's way of doing it

    Pence:
    = Round([InvoiceTotal] - Fix([InvoiceTotal]);2)*100

    Pieter

    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:%23XYgTV0pGHA.1440@TK2MSFTNGP03.phx.gbl...
    > Ok, apologies - I've just worked out that my value of 0.302 obviously is
    > pence only, so the value being returned is correct.
    >
    > Can you advise how I can display this as 30, i.e. Rounded without the
    > decimal?
    >
    > Thankyou.
    >
    > "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    > news:%23e%23geS0pGHA.148@TK2MSFTNGP04.phx.gbl...
    >> Thanks, Fix([InvoiceTotal]) seems to work AOK. Your suggetstion for Pence
    >> returns 0.302, which is the original value.
    >>
    >> Any suggestions?
    >>
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:O0$CLOzpGHA.756@TK2MSFTNGP05.phx.gbl...
    >>> Pounds:
    >>> =Fix([InvoiceTotal])
    >>>
    >>> Pence:
    >>> = [InvoiceTotal] - Fix([InvoiceTotal])
    >>>
    >>> Check that Fix() gives you the right answers for negative values, rather
    >>> than Int().
    >>>
    >>> --
    >>> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >>> Tips for Access users - http://allenbrowne.com/tips.html
    >>> Reply to group, rather than allenbrowne at mvps dot org.
    >>>
    >>> "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    >>> news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    >>>>
    >>>> I am writing a report in Access 2003 which is for invoicing. I need to
    >>>> display a currency value, but this needs to be displayed in two
    >>>> seperate boxes - One for pounds and one for pence, both seeded from the
    >>>> same table column. The table column has a currency data type.
    >>>>
    >>>> I have two problems:
    >>>>
    >>>> 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I
    >>>> would like this displayed as 0.30 (30 pence)
    >>>>
    >>>> 2) Can anyone recommend a way of splitting the pounds and pence values?
    >>>> I did think about using Split (Not tried yet) but then how would I tell
    >>>> Access what element of the array that I need?
    >>>>
    >>>> Any help will be much appreciated.
    >>>>
    >>>> Thanks!
    >>>> Simon.
    >>>
    >>>

    >>
    >>

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  11. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Great - Thanks! :)

    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    wrote in message news:%237lx4m0pGHA.4932@TK2MSFTNGP05.phx.gbl...
    > Look in my answer
    >
    > Combined With Allen's way of doing it
    >
    > Pence:
    > = Round([InvoiceTotal] - Fix([InvoiceTotal]);2)*100
    >
    > Pieter
    >
    > "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    > news:%23XYgTV0pGHA.1440@TK2MSFTNGP03.phx.gbl...
    >> Ok, apologies - I've just worked out that my value of 0.302 obviously is
    >> pence only, so the value being returned is correct.
    >>
    >> Can you advise how I can display this as 30, i.e. Rounded without the
    >> decimal?
    >>
    >> Thankyou.
    >>
    >> "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    >> news:%23e%23geS0pGHA.148@TK2MSFTNGP04.phx.gbl...
    >>> Thanks, Fix([InvoiceTotal]) seems to work AOK. Your suggetstion for
    >>> Pence returns 0.302, which is the original value.
    >>>
    >>> Any suggestions?
    >>>
    >>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >>> news:O0$CLOzpGHA.756@TK2MSFTNGP05.phx.gbl...
    >>>> Pounds:
    >>>> =Fix([InvoiceTotal])
    >>>>
    >>>> Pence:
    >>>> = [InvoiceTotal] - Fix([InvoiceTotal])
    >>>>
    >>>> Check that Fix() gives you the right answers for negative values,
    >>>> rather than Int().
    >>>>
    >>>> --
    >>>> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >>>> Tips for Access users - http://allenbrowne.com/tips.html
    >>>> Reply to group, rather than allenbrowne at mvps dot org.
    >>>>
    >>>> "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    >>>> news:%23ya06wypGHA.1548@TK2MSFTNGP04.phx.gbl...
    >>>>>
    >>>>> I am writing a report in Access 2003 which is for invoicing. I need to
    >>>>> display a currency value, but this needs to be displayed in two
    >>>>> seperate boxes - One for pounds and one for pence, both seeded from
    >>>>> the same table column. The table column has a currency data type.
    >>>>>
    >>>>> I have two problems:
    >>>>>
    >>>>> 1) Using this code: =Round(CCur(InvoiceTotal),2) brings back 0.3 - I
    >>>>> would like this displayed as 0.30 (30 pence)
    >>>>>
    >>>>> 2) Can anyone recommend a way of splitting the pounds and pence
    >>>>> values? I did think about using Split (Not tried yet) but then how
    >>>>> would I tell Access what element of the array that I need?
    >>>>>
    >>>>> Any help will be much appreciated.
    >>>>>
    >>>>> Thanks!
    >>>>> Simon.
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page