Format #,### not working on a report

  • Thread starter Thread starter Susan L
  • Start date Start date
S

Susan L

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
 
I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?
 
The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
 
You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

Susan L said:
The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


Duane Hookom said:
I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?
Click to expand...
Click to expand...
 
I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


Duane Hookom said:
You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

Susan L said:
The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


Duane Hookom said:
I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Oops. I spoke too soon.

I made the change on a few fields in the query to test it -- and it seemed
to be working as desired. Then I continued to change the other fields.

Now, the query grid has the zeros, but they don't appear on the report.
However, the number separator does appear. I compacted the database in case
it had developed a quirk.

Any thoughts?
--
susan


Susan L said:
I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


Duane Hookom said:
You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

Susan L said:
The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


:

I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
I don't know what you mean by "Now, the query grid has the zeros, but they
don't appear on the report. However, the number separator does appear."

How do the numbers appear in the report's record source query? What's a
number separator?

--
Duane Hookom
Microsoft Access MVP


Susan L said:
Oops. I spoke too soon.

I made the change on a few fields in the query to test it -- and it seemed
to be working as desired. Then I continued to change the other fields.

Now, the query grid has the zeros, but they don't appear on the report.
However, the number separator does appear. I compacted the database in case
it had developed a quirk.

Any thoughts?
--
susan


Susan L said:
I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


Duane Hookom said:
You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

:

The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


:

I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
My apologies for my lack of clarity.

When I run the query, the zeros are present when I view the query grid.

When I run the report that's based on the query, there are no zeros. But
number of 1,000 or more contain the ",". This was the origin of my post,
that the Format property #,### was not working. (Now I know because the data
is now strings instead of numbers.)
--
susan


Duane Hookom said:
I don't know what you mean by "Now, the query grid has the zeros, but they
don't appear on the report. However, the number separator does appear."

How do the numbers appear in the report's record source query? What's a
number separator?

--
Duane Hookom
Microsoft Access MVP


Susan L said:
Oops. I spoke too soon.

I made the change on a few fields in the query to test it -- and it seemed
to be working as desired. Then I continued to change the other fields.

Now, the query grid has the zeros, but they don't appear on the report.
However, the number separator does appear. I compacted the database in case
it had developed a quirk.

Any thoughts?
--
susan


Susan L said:
I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


:

You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

:

The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


:

I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Make sure the control sources are correct and try delete everything out of
the Format property.

--
Duane Hookom
Microsoft Access MVP


Susan L said:
My apologies for my lack of clarity.

When I run the query, the zeros are present when I view the query grid.

When I run the report that's based on the query, there are no zeros. But
number of 1,000 or more contain the ",". This was the origin of my post,
that the Format property #,### was not working. (Now I know because the data
is now strings instead of numbers.)
--
susan


Duane Hookom said:
I don't know what you mean by "Now, the query grid has the zeros, but they
don't appear on the report. However, the number separator does appear."

How do the numbers appear in the report's record source query? What's a
number separator?

--
Duane Hookom
Microsoft Access MVP


Susan L said:
Oops. I spoke too soon.

I made the change on a few fields in the query to test it -- and it seemed
to be working as desired. Then I continued to change the other fields.

Now, the query grid has the zeros, but they don't appear on the report.
However, the number separator does appear. I compacted the database in case
it had developed a quirk.

Any thoughts?
--
susan


:

I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


:

You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

:

The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


:

I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
I removed all format specifications and checked all the control sources. The
latter were accurate. I saved and closed and reopened the report. The zeros
were all there.

I then put #,### in the format property for the controls containing integers
(not percentages). The zeros dissappeared.

I am beginning to wonder if the report is corrupted. I had some problems
earlier and pulled all objects into a new blank database -- but I forgot to
turn off the automatic Track Name change feature. So I just completed
creating another fresh database and turned off the auto track name feature
before I imported the objects. It may be too late for this report.
--
susan


Duane Hookom said:
Make sure the control sources are correct and try delete everything out of
the Format property.

--
Duane Hookom
Microsoft Access MVP


Susan L said:
My apologies for my lack of clarity.

When I run the query, the zeros are present when I view the query grid.

When I run the report that's based on the query, there are no zeros. But
number of 1,000 or more contain the ",". This was the origin of my post,
that the Format property #,### was not working. (Now I know because the data
is now strings instead of numbers.)
--
susan


Duane Hookom said:
I don't know what you mean by "Now, the query grid has the zeros, but they
don't appear on the report. However, the number separator does appear."

How do the numbers appear in the report's record source query? What's a
number separator?

--
Duane Hookom
Microsoft Access MVP


:

Oops. I spoke too soon.

I made the change on a few fields in the query to test it -- and it seemed
to be working as desired. Then I continued to change the other fields.

Now, the query grid has the zeros, but they don't appear on the report.
However, the number separator does appear. I compacted the database in case
it had developed a quirk.

Any thoughts?
--
susan


:

I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


:

You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

:

The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


:

I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Corruption can occur. I would consider trying a new report to see if it
behaves the same.
--
Duane Hookom
Microsoft Access MVP


Susan L said:
I removed all format specifications and checked all the control sources. The
latter were accurate. I saved and closed and reopened the report. The zeros
were all there.

I then put #,### in the format property for the controls containing integers
(not percentages). The zeros dissappeared.

I am beginning to wonder if the report is corrupted. I had some problems
earlier and pulled all objects into a new blank database -- but I forgot to
turn off the automatic Track Name change feature. So I just completed
creating another fresh database and turned off the auto track name feature
before I imported the objects. It may be too late for this report.
--
susan


Duane Hookom said:
Make sure the control sources are correct and try delete everything out of
the Format property.

--
Duane Hookom
Microsoft Access MVP


Susan L said:
My apologies for my lack of clarity.

When I run the query, the zeros are present when I view the query grid.

When I run the report that's based on the query, there are no zeros. But
number of 1,000 or more contain the ",". This was the origin of my post,
that the Format property #,### was not working. (Now I know because the data
is now strings instead of numbers.)
--
susan


:

I don't know what you mean by "Now, the query grid has the zeros, but they
don't appear on the report. However, the number separator does appear."

How do the numbers appear in the report's record source query? What's a
number separator?

--
Duane Hookom
Microsoft Access MVP


:

Oops. I spoke too soon.

I made the change on a few fields in the query to test it -- and it seemed
to be working as desired. Then I continued to change the other fields.

Now, the query grid has the zeros, but they don't appear on the report.
However, the number separator does appear. I compacted the database in case
it had developed a quirk.

Any thoughts?
--
susan


:

I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


:

You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

:

The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


:

I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Yes, I know -- I read Allen Browne's admonitions about that feature (kicking
myself soundly for not remembering.) In thinking through the problem (driving
home - great time to let one's mind wander for solutions), it occurred to me
that there are other oddities: a label for a control that I had repositioned,
reappeared in it's unaligned form. A control with a "Normal" background that
I had changed to "Transparent" was back to "Normal". At the time, I thought
that I was simply losing my mind and forgetting what I had done.

I will try a simplified version of a new report. And I will go back to an
earlier version of the database (I regularly save dated versions) to see if I
can find one that is OK. The report took a lot of time to tweak.

I'll post back on which approach is successful in case others are reading
this thread for an answer. (I can't count the number of answers I have found
on these forums without posting questions. I always try to remember to click
"Useful.")
--
susan


Duane Hookom said:
Corruption can occur. I would consider trying a new report to see if it
behaves the same.
--
Duane Hookom
Microsoft Access MVP


Susan L said:
I removed all format specifications and checked all the control sources. The
latter were accurate. I saved and closed and reopened the report. The zeros
were all there.

I then put #,### in the format property for the controls containing integers
(not percentages). The zeros dissappeared.

I am beginning to wonder if the report is corrupted. I had some problems
earlier and pulled all objects into a new blank database -- but I forgot to
turn off the automatic Track Name change feature. So I just completed
creating another fresh database and turned off the auto track name feature
before I imported the objects. It may be too late for this report.
--
susan


Duane Hookom said:
Make sure the control sources are correct and try delete everything out of
the Format property.

--
Duane Hookom
Microsoft Access MVP


:

My apologies for my lack of clarity.

When I run the query, the zeros are present when I view the query grid.

When I run the report that's based on the query, there are no zeros. But
number of 1,000 or more contain the ",". This was the origin of my post,
that the Format property #,### was not working. (Now I know because the data
is now strings instead of numbers.)
--
susan


:

I don't know what you mean by "Now, the query grid has the zeros, but they
don't appear on the report. However, the number separator does appear."

How do the numbers appear in the report's record source query? What's a
number separator?

--
Duane Hookom
Microsoft Access MVP


:

Oops. I spoke too soon.

I made the change on a few fields in the query to test it -- and it seemed
to be working as desired. Then I continued to change the other fields.

Now, the query grid has the zeros, but they don't appear on the report.
However, the number separator does appear. I compacted the database in case
it had developed a quirk.

Any thoughts?
--
susan


:

I went ahead with Val, but will try 0 in the Format Property. Thanks so much.
--
susan


:

You can use the Format property to display 0 where the values are null. Just
leave the data as it is.

If you think you want to do the conversion to 0 in the query, try:
E306: Val(Nz([Err_306],0))

--
Duane Hookom
Microsoft Access MVP

:

The numbers in the record source query are left aligned. In the previous
query that this query is based on, they were right-aligned. And yes, Nz seems
to have made the difference. I need to show 0s for null values. Here's a
sample.
E306: Nz([Err_306],0)
--
susan


:

I expect the "number field" is being converted to a string. When you view the
datasheet of the report's record source, is the "number field" right or left
aligned?

Are you using and IIf() or Nz() or similar in the query?

--
Duane Hookom
Microsoft Access MVP


:

I don't understand why this is not working, because I've used it before in
reports. The underlying field in the table is a number field. I put it in the
"Format" property of the control.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top