Showing $0.00 in Access Reports

I am having a hard time figuring out why i can't get a report to a zero value as  $0.00. When i create a test report with one table and a few fields. One field set to Currency and zero values, the report shows the $0.00 in the field. The report I am having trouble with is a query from a couple of tables, but the currency field is static, or not calculated in the report, the report is just showing data. I have the field in the report formatted as Currency, but it will not show $0.00 in the report when the value is zero. The report shows nothing. Any ideas please.
jb702Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
What is the data type of the field in the table?

If you create a simple query with this expression, what do you see:

Select
    *, Format([YourField], "Currency") As Format1,Format(CDbl([YourField]), "Currency") As Format2
From
    tblYourTable

/gustav
0
 
Michael FowlerSolutions ConsultantCommented:
Is the query returning null. If so use the Nz function to change it too 0

http://www.techonthenet.com/access/functions/advanced/nz.php
0
 
jb702Author Commented:
I am already trapping for null values in my query, the field has either a zero value (0.00) or a negative or positive value. There are no null values.
Could i might have a corrupt control on my report?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Michael FowlerSolutions ConsultantCommented:
What have you got in the default value field. If this is blank change it to 0
0
 
jb702Author Commented:
I checked the tables, the default value is set to 0. This is just very odd.
0
 
Michael FowlerSolutions ConsultantCommented:
OK the last option that I can suggest is that format property is set to display 0 as an empty string eg "Currency";"Currency";"";""

Check the formatting and ensure this is not the case
https://support.office.com/en-ca/article/Specify-how-data-displays-by-using-custom-formatting-a5e5bcde-85da-4c7a-8164-1fe286636668
0
 
Jeffrey CoachmanMIS LiasonCommented:
but the currency field is static, or not calculated in the report, the report is just showing data.
It is not quite clear what this means...
Can you be more specific?

Is this a total or calculated value?

Then general rule is: If you what it to appear a certain way in the report, ...make sure that is the way it displays in the source data.
So, ...
Open the query/recordsource and see what your zero values display as.
If the zero values display as currency ($0.00), then create a new report, ...then the zero values will most likely display correctly in the report. (providing you do not change any of the "control's" properties...)

JeffCoachman
0
 
jb702Author Commented:
the query is not a calculated query, the values are coming straight from the source.
0
 
Jeffrey CoachmanMIS LiasonCommented:
I thought you said that the source was:
a query from a couple of tables
?
0
 
Gustav BrockCIOCommented:
> .. the values are coming straight from the source.

OK. So:

If you create a simple query with this expression, what do you see:

 Select
     *, Format([YourField], "Currency") As Format1,Format(CDbl([YourField]), "Currency") As Format2
 From
     tblYourTable

 /gustav
0
 
jb702Author Commented:
Ok, Sorry guys, i was out sick with the flu for several.  I took gustav's suggestion and now when i look at the query result for the report. this is what i see for the field data. But when the report is printed or run the report shows nothing still.

TotalAmt
$91.25
$0.00
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then post a small sample database that displays this issue.
0
 
jb702Author Commented:
Thank you
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
All Courses

From novice to tech pro — start learning today.