Currency Format on Sum Text Box shows decimal instead of comma

[dzirkelb]

I have a calculated sum field in the report footer of a report. I have it set for format currency with 2 decimals; however, for the number 1234.56 it displays it as $1.234.56 instead of $1,234.56. What do I need to do to have it show the comma opposed to the decimal next to the number 1?

 

[vbaInet]

This Format property should do it

$#,##0.00

 

[dzirkelb]

Still shows a period instead of a comma.

I do have a calcuated field that is format currency on the detail section that displays correctly, not sure why this sum on the report footer does not.

 

[vbaInet]

Upload a stripped down version of your db so we can have a look.

 

[dzirkelb]

I have attached the report. Let me know if I need to add anything else to the database.

The tables are linked sql tables, so they will do no good.

 

[vbaInet]

Some records to test on will be nice. Also mentioning what the name of the textbox is would be useful too.

 

[dzirkelb]

The name of the textbox is called ExtTotal, it is the only field in the report footer.

Attached is the database with some dummy data so the report actually opens now.

 

[vbaInet]

Change the Decimal Places property from Auto to 2.

 

[dzirkelb]

Same issue. I attached an image of what it looks like on my screen.

 

[vbaInet]

Alright, test this:

1. Remove what's in the Control Source (i.e. copy and save it somewhere)
2. In the Format event of the Report Footer's section copy and paste this in:

Me.ExtTotal = Format(24000, "$#\,##0.00")

 

[dzirkelb]

Nothing shows up when I do that. To confirm, this is what I did:

1) removed everything in the control source section of the ExtTotal text box
2) I clicked on the report footer section, then went to the event tab
3) I added Me.ExtTotal = Format(24000, "$#\,##0.00") into the On Format section

 

[vbaInet]

3) I added Me.ExtTotal = Format(24000, "$#\,##0.00") into the On Format section

This should be:
1. Click on the elipsis (i.e. the ... button on the Format section)
2. Select Code Builder from the options and OK
3. Paste that line of code where the cursor is current is (i.e. between Private and End).

 

[dzirkelb]

Ok, I changed that and the code for the event is now:

Private Sub ReportFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.ExtTotal = Format(24000, "$#\,##0.00")
End Sub

and still nothing appears

 

[vbaInet]

Can I see a screenshot?

 

[dzirkelb]

I attached 3 photos for your review.

 

[vbaInet]

I think you are using Report View. Test it on Print Preview.

 

[dzirkelb]

Oh my goodness. So, I went to print preview like you suggested and I noticed it had the result listed as ######, which led me to believe that it was like Excel, so I made the field bigger, and viola, it showed a number; however, still with the period. So, I looked at it, and it looked like the bottom of the numbers were just ever so slightly chopped off, so I made the field taller. Well, if you don't know where this is going yet, then I'll tell ya. The comma tail was chopped off....

Thank you for your help, I never would have figured it out

 

[vbaInet]

So, I looked at it, and it looked like the bottom of the numbers were just ever so slightly chopped off, so I made the field taller. Well, if you don't know where this is going yet, then I'll tell ya. The comma tail was chopped off....

Thank you for your help, I never would have figured it out

I found it a bit strange that you said it worked on the other textboxes but not on the textbox in the footer. I was going to conclude that it was because the textbox was corrupt.

I guess you could go back to the previous Format property instead. Copy the Format property from your other textboxes.