Not holding number format

[Bonnie]

Hi! Using A02 on XP. I have a macro imported text table
with a few number (integer) fields. [F14] may be null, but
I need to run a total query on the results ([Y]) so I want
to place zeros in the blanks. Where [F14] is not null, I
need the calculation result to be formatted as a number
with comma separator and show negatives with the leading
hyphen, hence my formula. This query is a make table. My
resulting field [Y] in the new table is text, data is left
justified and the nulls are still blank. UGH! What am I
doing wrong? Should I create the table and then run an
update query to fill the 'result' fields?

Y: IIf(Not IsNull([F14]),Format$([F14]*[MRNAPercent]
*0.01,"#,##0.00;-#,##0.00"),"0")

Any help or advice would be VERY much appreciated! Thanks
in advance!!!

 

[John Spencer (MVP)]

What you are doing wrong is applying format to a number field. When you do that
it is automatically a string field. Number fields do not store commas, leading
or trailing zeroes, or other formatting items (such as dollar signs). Also, if
you are using the aggregate SUM function to get a total, it will automatically
treat the null as if it were a zero.

You might try the following, if you really need the zero in the field.

Y: CCUR(IIF(IsNull([F14]),0,[F14]*[MRNAPercent]))

If you need to round that to just two digits after the decimal, you could use
your original formula and wrap it with the CCur function to force the result
back to a number.
Y: CCur(IIf(Not IsNull([F14]),Format$([F14]*[MRNAPercent]*0.01,"#,##0.00;-#,##0.00"),"0"))

 

[John Vinson]

Hi! Using A02 on XP. I have a macro imported text table
with a few number (integer) fields. [F14] may be null, but
I need to run a total query on the results ([Y]) so I want
to place zeros in the blanks. Where [F14] is not null, I
need the calculation result to be formatted as a number
with comma separator and show negatives with the leading
hyphen, hence my formula. This query is a make table. My
resulting field [Y] in the new table is text, data is left
justified and the nulls are still blank. UGH! What am I
doing wrong? Should I create the table and then run an
update query to fill the 'result' fields?

Y: IIf(Not IsNull([F14]),Format$([F14]*[MRNAPercent]
*0.01,"#,##0.00;-#,##0.00"),"0")

Any help or advice would be VERY much appreciated! Thanks
in advance!!!

Don't confuse data PRESENTATION - commas, currency symbols, hyphens,
etc. - with data STORAGE. Number fields do not have or need such
things! You also do not need a MakeTable in order to manage or present
data.

A couple of comments:

- NULL fields are treated as if they were zero in a Totals query, so
no conversion is needed.
- Expressions within the same record - e.g. [F14] + [F15] - will
"propagate nulls"; if either field is NULL the result will be NULL
(unlike the behavior of a Totals query). To get around this you can
use Nz([F14]) + NZ([F15]) to convert nulls to zero for the purpose of
the calculation.
- You can apply a Format property to a textbox on a form or a report
based on the Query in which you do the calculations. It's not
necessary to store the formatted data in a table to display it
formatted!

 

[Tom Ellison]

Aren't NULL values treated differently from zero values in an AVG() or
COUNT()?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi! Using A02 on XP. I have a macro imported text table
with a few number (integer) fields. [F14] may be null, but
I need to run a total query on the results ([Y]) so I want
to place zeros in the blanks. Where [F14] is not null, I
need the calculation result to be formatted as a number
with comma separator and show negatives with the leading
hyphen, hence my formula. This query is a make table. My
resulting field [Y] in the new table is text, data is left
justified and the nulls are still blank. UGH! What am I
doing wrong? Should I create the table and then run an
update query to fill the 'result' fields?

Y: IIf(Not IsNull([F14]),Format$([F14]*[MRNAPercent]
*0.01,"#,##0.00;-#,##0.00"),"0")

Any help or advice would be VERY much appreciated! Thanks
in advance!!!

Don't confuse data PRESENTATION - commas, currency symbols, hyphens,
etc. - with data STORAGE. Number fields do not have or need such
things! You also do not need a MakeTable in order to manage or present
data.

A couple of comments:

- NULL fields are treated as if they were zero in a Totals query, so
no conversion is needed.
- Expressions within the same record - e.g. [F14] + [F15] - will
"propagate nulls"; if either field is NULL the result will be NULL
(unlike the behavior of a Totals query). To get around this you can
use Nz([F14]) + NZ([F15]) to convert nulls to zero for the purpose of
the calculation.
- You can apply a Format property to a textbox on a form or a report
based on the Query in which you do the calculations. It's not
necessary to store the formatted data in a table to display it
formatted!

 

[Bonnie]

Thanks VERY much for your reply. I really appreciate the
explanation.

My main purpose for doing the whole format thing was to
round to 2 digits. I have contributions that must be split
60/40, 70/30, etc and I need to round the number on an odd
number calculation. Such as:
21.52*40*.01=8.608 (I need to load 8.61)
21.52*60*.01=12.912 (I need to load 12.91)

My sub purpose was that I noticed my created table had set
my calculated result fields as text rather than number. (I
am creating a table because I am exporting a delimited
file with a transfer text macro. That file is then loaded
into our contribution system.)

I'm going to use your second suggestion but suppose I
really don't need to force the format, just need the 2
digits. Any changes you would suggest?

BTW, What is CCur? Something about currency? (I couldn't
bring it up in either Access or VB Help.)

Again, thank you very much for taking the time to help out
and teach. I am truly grateful for the shared knowledge.

-----Original Message-----
What you are doing wrong is applying format to a number field. When you do that
it is automatically a string field. Number fields do not store commas, leading
or trailing zeroes, or other formatting items (such as dollar signs). Also, if
you are using the aggregate SUM function to get a total, it will automatically
treat the null as if it were a zero.

You might try the following, if you really need the zero in the field.

Y: CCUR(IIF(IsNull([F14]),0,[F14]*[MRNAPercent]))

If you need to round that to just two digits after the decimal, you could use
your original formula and wrap it with the CCur function to force the result
back to a number.
Y: CCur(IIf(Not IsNull([F14]),Format$([F14]*[MRNAPercent] *0.01,"#,##0.00;-#,##0.00"),"0"))

Hi! Using A02 on XP. I have a macro imported text table
with a few number (integer) fields. [F14] may be null, but
I need to run a total query on the results ([Y]) so I want
to place zeros in the blanks. Where [F14] is not null, I
need the calculation result to be formatted as a number
with comma separator and show negatives with the leading
hyphen, hence my formula. This query is a make table. My
resulting field [Y] in the new table is text, data is left
justified and the nulls are still blank. UGH! What am I
doing wrong? Should I create the table and then run an
update query to fill the 'result' fields?

Y: IIf(Not IsNull([F14]),Format$([F14]*[MRNAPercent]
*0.01,"#,##0.00;-#,##0.00"),"0")

Any help or advice would be VERY much appreciated! Thanks
in advance!!!

.

 

[Bonnie]

Thank you very much for your advice and information. My
main purpose for doing the whole format thing was to round
to 2 digits. I have contributions that must be split
60/40, 70/30, etc and I need to round the number on an odd
number calculation. Such as:
21.52*40*.01=8.608 (I need to load 8.61)
21.52*60*.01=12.912 (I need to load 12.91)

My sub purpose was that I noticed my created table had set
my calculated result fields as text rather than number. (I
am creating a table because I am exporting a delimited
file with a transfer text macro. That file is then loaded
into our contribution system.)

Again, thank you very much for taking the time to help out
and teach. I am truly grateful for the shared knowledge.

-----Original Message-----

Hi! Using A02 on XP. I have a macro imported text table
with a few number (integer) fields. [F14] may be null, but
I need to run a total query on the results ([Y]) so I want
to place zeros in the blanks. Where [F14] is not null, I
need the calculation result to be formatted as a number
with comma separator and show negatives with the leading
hyphen, hence my formula. This query is a make table. My
resulting field [Y] in the new table is text, data is left
justified and the nulls are still blank. UGH! What am I
doing wrong? Should I create the table and then run an
update query to fill the 'result' fields?

Y: IIf(Not IsNull([F14]),Format$([F14]*[MRNAPercent]
*0.01,"#,##0.00;-#,##0.00"),"0")

Any help or advice would be VERY much appreciated! Thanks
in advance!!!

Don't confuse data PRESENTATION - commas, currency symbols, hyphens,
etc. - with data STORAGE. Number fields do not have or need such
things! You also do not need a MakeTable in order to manage or present
data.

A couple of comments:

- NULL fields are treated as if they were zero in a Totals query, so
no conversion is needed.
- Expressions within the same record - e.g. [F14] + [F15] - will
"propagate nulls"; if either field is NULL the result will be NULL
(unlike the behavior of a Totals query). To get around this you can
use Nz([F14]) + NZ([F15]) to convert nulls to zero for the purpose of
the calculation.
- You can apply a Format property to a textbox on a form or a report
based on the Query in which you do the calculations. It's not
necessary to store the formatted data in a table to display it
formatted!


.

 

[John Vinson]

Thank you very much for your advice and information. My
main purpose for doing the whole format thing was to round
to 2 digits. I have contributions that must be split
60/40, 70/30, etc and I need to round the number on an odd
number calculation. Such as:
21.52*40*.01=8.608 (I need to load 8.61)
21.52*60*.01=12.912 (I need to load 12.91)

Ok...

Round(21.52*40*.01, 2)

will return 8.61. No formatting needed.

 

[Bonnie]

John, you are 'da bomb'!!! That rounder in the calculation
is JUST what I needed without the other hoopla. I've been
hovering over this entry hoping you would check back just
one more time. HOORAY! It works great! Thank you, thank
you, thank you! I love you guys!!!