Currency Rounding and Splitting Pounds and Pence

[Simon Harris]

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!

 

[Allen Browne]

Pounds:
=Fix([InvoiceTotal])

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

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

 

[Douglas J Steele]

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.

 

[Pieter Wijnen]

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

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!

--

 

[Simon Harris]

Thank you all very much for your replies.

 

[Simon Harris]

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

Any suggestions?

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Simon Harris]

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.

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

Any suggestions?

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Pieter Wijnen]

Look in my answer

Combined With Allen's way of doing it

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

Pieter

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.

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

Any suggestions?

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.

Reply to group, rather than allenbrowne at mvps dot org.


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.

 

[Pieter Wijnen]

Look in my answer

Combined With Allen's way of doing it

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

Pieter

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.

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

Any suggestions?

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.

Reply to group, rather than allenbrowne at mvps dot org.


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.

--

 

[Simon Harris]

Great - Thanks!

"Pieter Wijnen"

Look in my answer

Combined With Allen's way of doing it

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

Pieter

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.

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

Any suggestions?

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.

Reply to group, rather than allenbrowne at mvps dot org.


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.