Format Label as "Standard" Number

  • Thread starter Thread starter Bob Barnes
  • Start date Start date
B

Bob Barnes

I'm using Queries to populate a Report w/ numbers using the label's caption.
The financial Report uses various Queries.

If a label.caption populates as 1234.56, how can I get it to appear as
1,234.56??
And, if needed later, as $1,234.56.

I've tried various CCur, and formatting as Standard, in code.

TIA - Bob
 
I'm using Queries to populate a Report w/ numbers using the label's caption.
The financial Report uses various Queries.
Click to expand...

Why label captions, rather than the much simpler idea of bound control
textboxes????
If a label.caption populates as 1234.56, how can I get it to appear as
1,234.56??
And, if needed later, as $1,234.56.
Click to expand...

Use an explicit format: "#,##0.00" or "Currency" respectively.
I've tried various CCur, and formatting as Standard, in code.
Click to expand...

Thera are lots of canned formats - but lots more "do it yourself" custom
formats. Dig down in the online help for Format, it goes many layers deep.

John W. Vinson [MVP]
 
John - thank you.

The Report is a snapshot format that, for me, works better than several
Queries populating bound textboxes.

Will test and report back.

Bob
 
John - thank you.

The Report is a snapshot format that, for me, works better than several
Queries populating bound textboxes.
Click to expand...

If you're using VBA code to fill the captions, you can use the Format()
function to cast the table value into a text string:

Format([dollarvalue], "Currency")

or

Format([dollarvalue], "$#,##0.00")

John W. Vinson [MVP]
 
John - Thank you.

If you're using VBA code to fill the captions, you can use the Format()
function to cast the table value into a text string:

Format([dollarvalue], "Currency")
Click to expand...

I populate on Report_Open in a Do Loop to find things such as Cash, Checks,
AMEX, Visa.

I clear all Captions, so if there's no Visa for the day, the label is "" (or
blank).
In the Do Loop, I run code such as Cash.Caption = Format(!Cash, "Currency"),
but it's appearing as Fixed, no decimals (the test data is 95% of the time
whole dollars = no decimal places..and is all whole numbers now).

Was surprised when it resulted like that last nite, and I won't be there
again until this Saturday. Will either Post in this thread (2 days old) or
start a new thread.

Thanks again, Bob

John W. Vinson said:
John - thank you.

The Report is a snapshot format that, for me, works better than several
Queries populating bound textboxes.
Click to expand...

If you're using VBA code to fill the captions, you can use the Format()
function to cast the table value into a text string:

Format([dollarvalue], "Currency")

or

Format([dollarvalue], "$#,##0.00")

John W. Vinson [MVP]
Click to expand...
 
In the Do Loop, I run code such as Cash.Caption = Format(!Cash, "Currency"),
but it's appearing as Fixed, no decimals (the test data is 95% of the time
whole dollars = no decimal places..and is all whole numbers now).
Click to expand...

Well then, don't use the predefined Currency: use "$#,##0.00".

John W. Vinson [MVP]
 
John - Will be able to check this tomorrow (and post result here). Even
thought running the code on Detail_Format would work (maybe it does, but I
had a lloonngg Format that didn't finish after a minute, and the User prefers
a quick open of the Report...so I stopped before it finished).

Thank you, Bob
 
John - the "$#,##0.00" does NOT work on Report_Open.

I'll keep trying other code.

Thank you - Bob
 
John - the "$#,##0.00" does NOT work on Report_Open.
Click to expand...

That's not what I suggested. Use it in the Format() function to cast the
numeric value into a string for use as the caption of the label.
 
It works..

snippet..
Case "AMEX"
AU.Caption = !TheNum: AC.Caption = Format(!Credit, "$#,##0.00")

Thank you John - Bob
 
Back
Top