"0.0" Value (not Null)... missing value in report

I have a report that is failing to produce a value for some records in the
final calculation column. The data is populated in the datasheet view of the
query. The strange thing is, I produced a simple report for the purpose of
visually validating my calculations and all values populate... life is good.
But on the form designed for my application certain values refuse to show
themselves.
Following are the calculations used in the query:

Rate: IIf([Sale_Price]0 And [Sale_Price]50,".35",IIf([Sale_Price]=50 And
[Sale_Price]=299.99,".25",IIf([Sale_Price]=300 And
[Sale_Price]=999.99,".20",IIf([Sale_Price]=1000,".15","99"))))

PreCom: IIf([Sale_Price]=0,0,[Sale_Price]*[Rate])

PreCom2: IIf([Sale_Price]=0.01 And [Sale_Price]=14,5,[PreCom])

Commission: IIf([SPC]0,[SPC],[PreCom2])

Profit: IIf([PreCom2]0,[Sale_Price]-[Commission],0)

Records affected by the [PreCom2] calculation are the ones not appearing in
the final calculation, [Profit]. All fields with the exception of [Rate] are
formatted both in the query and in the report as "Currency".

If this has anything to do with the NZ function please be literal as I am
clueless on it. (perhaps that is quite obvious...) Ugh!

Thanks to all you wonderful people who take the time to help us newbies out!

The qoute marks around ".35" etc are telling JET to treat the RATE field as
*text.* I think you want to treat it as a number, so lose the quotes:
Rate:IIf([Sale_Price Between 0 And 50, 0.35, IIf(...

If Sale_Price is zero, multiplying by anything will yield zero, so just use:
PreCom: [Sale_Price] * [Rate]

Nz() applies where there are nulls. Form your subject line, I'm assuming you
have the zeros and so it is not a matter of nulls. However, you have not
handled the case where Sale_Price is null (nor the case where it is
negative.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
I have a report that is failing to produce a value for some records in the
final calculation column. The data is populated in the datasheet view of
the
query. The strange thing is, I produced a simple report for the purpose of
visually validating my calculations and all values populate... life is
good.
But on the form designed for my application certain values refuse to show
themselves.
Following are the calculations used in the query:

Rate: IIf([Sale_Price]0 And [Sale_Price]50,".35",IIf([Sale_Price]=50
And
[Sale_Price]=299.99,".25",IIf([Sale_Price]=300 And
[Sale_Price]=999.99,".20",IIf([Sale_Price]=1000,".15","99"))))

PreCom: IIf([Sale_Price]=0,0,[Sale_Price]*[Rate])

PreCom2: IIf([Sale_Price]=0.01 And [Sale_Price]=14,5,[PreCom])

Commission: IIf([SPC]0,[SPC],[PreCom2])

Profit: IIf([PreCom2]0,[Sale_Price]-[Commission],0)

Records affected by the [PreCom2] calculation are the ones not appearing
in
the final calculation, [Profit]. All fields with the exception of [Rate]
are
formatted both in the query and in the report as "Currency".

If this has anything to do with the NZ function please be literal as I am
clueless on it. (perhaps that is quite obvious...) Ugh!

Thanks to all you wonderful people who take the time to help us newbies
out!

Hello Allen, Thanks for your reply.
I removed the quotes.
The If Sale_Price=0 thing was my attempt to avoid nulls. My
misunderstanding.... I removed that as well.
Sale_Price is never null or less than zero.
I did go back and ensure I didn't leave any IIf statements hanging without
an else statement. Still getting blank results on my application report while
the simple calculation report looks beautiful. Any other thoughts??
btw: I discovered your site a few weeks ago. In my humble opinion, it's a
wonderful site and I thank you for sharing.
"Allen Browne" wrote:

The qoute marks around ".35" etc are telling JET to treat the RATE field as
*text.* I think you want to treat it as a number, so lose the quotes:
Rate:IIf([Sale_Price Between 0 And 50, 0.35, IIf(...

If Sale_Price is zero, multiplying by anything will yield zero, so just use:
PreCom: [Sale_Price] * [Rate]

Nz() applies where there are nulls. Form your subject line, I'm assuming you
have the zeros and so it is not a matter of nulls. However, you have not
handled the case where Sale_Price is null (nor the case where it is
negative.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
I have a report that is failing to produce a value for some records in the
final calculation column. The data is populated in the datasheet view of
the
query. The strange thing is, I produced a simple report for the purpose of
visually validating my calculations and all values populate... life is
good.
But on the form designed for my application certain values refuse to show
themselves.
Following are the calculations used in the query:

Rate: IIf([Sale_Price]0 And [Sale_Price]50,".35",IIf([Sale_Price]=50
And
[Sale_Price]=299.99,".25",IIf([Sale_Price]=300 And
[Sale_Price]=999.99,".20",IIf([Sale_Price]=1000,".15","99"))))

PreCom: IIf([Sale_Price]=0,0,[Sale_Price]*[Rate])

PreCom2: IIf([Sale_Price]=0.01 And [Sale_Price]=14,5,[PreCom])

Commission: IIf([SPC]0,[SPC],[PreCom2])

Profit: IIf([PreCom2]0,[Sale_Price]-[Commission],0)

Records affected by the [PreCom2] calculation are the ones not appearing
in
the final calculation, [Profit]. All fields with the exception of [Rate]
are
formatted both in the query and in the report as "Currency".

If this has anything to do with the NZ function please be literal as I am
clueless on it. (perhaps that is quite obvious...) Ugh!

Thanks to all you wonderful people who take the time to help us newbies
out!

Can you clarify the situation as it is now please?

Is Rate a calculated field in the query the report is based on? If so, does
it show correctly in the query?

If Rate is only a calculated control on the text box, you will need to trace
the values back to see where it's coming from.

Are there any controls (even hidden ones) that show #Name or #Error? Solve
those first.

Otherwise, is Rate the name of the text box? Or its ControlSource? Or both?
And what is it dependent on?

It may take some debugging to trace this back. The core concept is to add an
extra text box bound to something that does work. Then take it an extra
step, and check it works. Taking it one step at a time, you can find the
point at which it fails, which gives you the clue as to what the cause might
be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
Hello Allen, Thanks for your reply.
I removed the quotes.
The If Sale_Price=0 thing was my attempt to avoid nulls. My
misunderstanding.... I removed that as well.
Sale_Price is never null or less than zero.
I did go back and ensure I didn't leave any IIf statements hanging without
an else statement. Still getting blank results on my application report
while
the simple calculation report looks beautiful. Any other thoughts??
btw: I discovered your site a few weeks ago. In my humble opinion, it's a
wonderful site and I thank you for sharing.
"Allen Browne" wrote:

The qoute marks around ".35" etc are telling JET to treat the RATE field
as
*text.* I think you want to treat it as a number, so lose the quotes:
Rate:IIf([Sale_Price Between 0 And 50, 0.35, IIf(...

If Sale_Price is zero, multiplying by anything will yield zero, so just
use:
PreCom: [Sale_Price] * [Rate]

Nz() applies where there are nulls. Form your subject line, I'm assuming
you
have the zeros and so it is not a matter of nulls. However, you have not
handled the case where Sale_Price is null (nor the case where it is
negative.)

I'll do my best to clarify:
Rate is indeed a calculated field in the query the report is based on. It
does show correctly in the query and in the simple report I refer to as
'CalculationChecker'.
The following fields are within the query in the order shown. They are
calculated within the query (unless noted with '*', these fields are from the
table):

*Sale_Price

Rate: IIf([Sale_Price]0 And [Sale_Price]50,0.35,IIf([Sale_Price]=50 And
[Sale_Price]=299.99,0.25,IIf([Sale_Price]=300 And
[Sale_Price]=999.99,0.2,IIf([Sale_Price]=1000,0.15,99))))

*SPC

PreCom: [Sale_Price]*[Rate]

PreCom2: IIf([Sale_Price]=0.01 And [Sale_Price]=14,5,[PreCom])

*B_D_Option_Grp

Deluxe_Refund: IIf([B_D_Option_Grp]=2 And [PreCom2]0,5,0)

Commission: Commission: IIf([SPC]0 And
[Sale_Price]0,[SPC]-[Deluxe_Refund],[PreCom2]-[Deluxe_Refund])

Profit: IIf([PreCom2]0,[Sale_Price]-[Commission],0)

The record with the following properties produces a profit of 4.99 in the
'CalculationChecker' report but produces a blank on my application report
'ReceiptCreator':

Sale_Price = 9.99
SPC = 0.0
B_D_Option_Grp = 1

There are no #Name, #Error, Parameter prompts, no signs of trouble other
than a big blank spot where my 4.99 should be.

Thanks so much for your patience.

"Allen Browne" wrote:

Can you clarify the situation as it is now please?

Is Rate a calculated field in the query the report is based on? If so, does
it show correctly in the query?

If Rate is only a calculated control on the text box, you will need to trace
the values back to see where it's coming from.

Are there any controls (even hidden ones) that show #Name or #Error? Solve
those first.

Otherwise, is Rate the name of the text box? Or its ControlSource? Or both?
And what is it dependent on?

It may take some debugging to trace this back. The core concept is to add an
extra text box bound to something that does work. Then take it an extra
step, and check it works. Taking it one step at a time, you can find the
point at which it fails, which gives you the clue as to what the cause might
be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
Hello Allen, Thanks for your reply.
I removed the quotes.
The If Sale_Price=0 thing was my attempt to avoid nulls. My
misunderstanding.... I removed that as well.
Sale_Price is never null or less than zero.
I did go back and ensure I didn't leave any IIf statements hanging without
an else statement. Still getting blank results on my application report
while
the simple calculation report looks beautiful. Any other thoughts??
btw: I discovered your site a few weeks ago. In my humble opinion, it's a
wonderful site and I thank you for sharing.
"Allen Browne" wrote:

The qoute marks around ".35" etc are telling JET to treat the RATE field
as
*text.* I think you want to treat it as a number, so lose the quotes:
Rate:IIf([Sale_Price Between 0 And 50, 0.35, IIf(...

If Sale_Price is zero, multiplying by anything will yield zero, so just
use:
PreCom: [Sale_Price] * [Rate]

Nz() applies where there are nulls. Form your subject line, I'm assuming
you
have the zeros and so it is not a matter of nulls. However, you have not
handled the case where Sale_Price is null (nor the case where it is
negative.)

If you open your table in design view, what data type are Sale_Price, SPC,
and B_D_Option_Grp? Assuming they are all either Number or Currency (not
Text), then I don't see any way Access could be misunderstanding the data
type in the query. When you view the query results directly, do all these
fields right-align (like numbers), and none left-align (like text)? I will
assume so, which leads us to conclude that the query is now fine, so the
problem is with the report.

We should also eliminate the possibility that Access is mis-identifying the
fields, by turning off Name AutoCorrect. Uncheck the boxes under Tools |
Options | General (or in Access 2007: Office Button | Access Options.) Then
compact the database: Tools | Database Utilities | Compact/Repair (or in
Access 2007, Office Button | Manage.) It might also be a good idea to clear
any Caption property you have set in table design.

Now open the report in design view. Double-check the properties of the
Profit text box. Particularly:
Control Source Profit
Format Currency
Name Profit
I'm assuming this text box is in the Detail section of the report (not in a
page header or footer.)

If it's still blank, we are going to have to get Access to tell us what's
going on. In the Print event procedure of the (Detail?) section, add
Debug.Print Me.[ID], Me.Profit
Use your primary key field instead of ID: this is just so you can tell which
profit value is associated with which record. Run the report. Then open the
Immediate Window (Ctrl+G) to see what came out.

If you are getting numbers out in the debug window, we are down to looking
for really obvious things, like using white font on a white background,
conditional formatting, hide duplicates, etc. Presumably you have already
tried deleting the text box, saving the report, closing it, and then opening
it in design view and adding it back in.

If no numbers come out in the debug window, try:
Debug.Print Me.[ID], Me.Profit, IsNull(Me.Profit), (Me.Profit = "")
to see if the value is either a null or a zero-length string.

HTH.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
I'll do my best to clarify:
Rate is indeed a calculated field in the query the report is based on. It
does show correctly in the query and in the simple report I refer to as
'CalculationChecker'.
The following fields are within the query in the order shown. They are
calculated within the query (unless noted with '*', these fields are from
the
table):

*Sale_Price

Rate: IIf([Sale_Price]0 And [Sale_Price]50,0.35,IIf([Sale_Price]=50 And
[Sale_Price]=299.99,0.25,IIf([Sale_Price]=300 And
[Sale_Price]=999.99,0.2,IIf([Sale_Price]=1000,0.15,99))))

*SPC

PreCom: [Sale_Price]*[Rate]

PreCom2: IIf([Sale_Price]=0.01 And [Sale_Price]=14,5,[PreCom])

*B_D_Option_Grp

Deluxe_Refund: IIf([B_D_Option_Grp]=2 And [PreCom2]0,5,0)

Commission: Commission: IIf([SPC]0 And
[Sale_Price]0,[SPC]-[Deluxe_Refund],[PreCom2]-[Deluxe_Refund])

Profit: IIf([PreCom2]0,[Sale_Price]-[Commission],0)

The record with the following properties produces a profit of 4.99 in the
'CalculationChecker' report but produces a blank on my application report
'ReceiptCreator':

Sale_Price = 9.99
SPC = 0.0
B_D_Option_Grp = 1

There are no #Name, #Error, Parameter prompts, no signs of trouble other
than a big blank spot where my 4.99 should be.

Thanks so much for your patience.

"Allen Browne" wrote:

Can you clarify the situation as it is now please?

Is Rate a calculated field in the query the report is based on? If so,
does
it show correctly in the query?

If Rate is only a calculated control on the text box, you will need to
trace
the values back to see where it's coming from.

Are there any controls (even hidden ones) that show #Name or #Error?
Solve
those first.

Otherwise, is Rate the name of the text box? Or its ControlSource? Or
both?
And what is it dependent on?

It may take some debugging to trace this back. The core concept is to add
an
extra text box bound to something that does work. Then take it an extra
step, and check it works. Taking it one step at a time, you can find the
point at which it fails, which gives you the clue as to what the cause
might
be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
Hello Allen, Thanks for your reply.
I removed the quotes.
The If Sale_Price=0 thing was my attempt to avoid nulls. My
misunderstanding.... I removed that as well.
Sale_Price is never null or less than zero.
I did go back and ensure I didn't leave any IIf statements hanging
without
an else statement. Still getting blank results on my application report
while
the simple calculation report looks beautiful. Any other thoughts??
btw: I discovered your site a few weeks ago. In my humble opinion, it's
a
wonderful site and I thank you for sharing.
"Allen Browne" wrote:

The qoute marks around ".35" etc are telling JET to treat the RATE
field
as
*text.* I think you want to treat it as a number, so lose the quotes:
Rate:IIf([Sale_Price Between 0 And 50, 0.35, IIf(...

If Sale_Price is zero, multiplying by anything will yield zero, so
just
use:
PreCom: [Sale_Price] * [Rate]

Nz() applies where there are nulls. Form your subject line, I'm
assuming
you
have the zeros and so it is not a matter of nulls. However, you have
not
handled the case where Sale_Price is null (nor the case where it is
negative.)

Oh MY!!! (Shaking my head) Conditional formatting. Ugh!! Thank you soooo
much Allen. I'm so embarrassed. (giggling) Ugh!!! WOW. Well, this was my last
bug. My first project is complete. Do you like wine? I do. I think I'll open
a bottle.

"Allen Browne" wrote:

If you open your table in design view, what data type are Sale_Price, SPC,
and B_D_Option_Grp? Assuming they are all either Number or Currency (not
Text), then I don't see any way Access could be misunderstanding the data
type in the query. When you view the query results directly, do all these
fields right-align (like numbers), and none left-align (like text)? I will
assume so, which leads us to conclude that the query is now fine, so the
problem is with the report.

We should also eliminate the possibility that Access is mis-identifying the
fields, by turning off Name AutoCorrect. Uncheck the boxes under Tools |
Options | General (or in Access 2007: Office Button | Access Options.) Then
compact the database: Tools | Database Utilities | Compact/Repair (or in
Access 2007, Office Button | Manage.) It might also be a good idea to clear
any Caption property you have set in table design.

Now open the report in design view. Double-check the properties of the
Profit text box. Particularly:
Control Source Profit
Format Currency
Name Profit
I'm assuming this text box is in the Detail section of the report (not in a
page header or footer.)

If it's still blank, we are going to have to get Access to tell us what's
going on. In the Print event procedure of the (Detail?) section, add
Debug.Print Me.[ID], Me.Profit
Use your primary key field instead of ID: this is just so you can tell which
profit value is associated with which record. Run the report. Then open the
Immediate Window (Ctrl+G) to see what came out.

If you are getting numbers out in the debug window, we are down to looking
for really obvious things, like using white font on a white background,
conditional formatting, hide duplicates, etc. Presumably you have already
tried deleting the text box, saving the report, closing it, and then opening
it in design view and adding it back in.

If no numbers come out in the debug window, try:
Debug.Print Me.[ID], Me.Profit, IsNull(Me.Profit), (Me.Profit = "")
to see if the value is either a null or a zero-length string.

HTH.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
I'll do my best to clarify:
Rate is indeed a calculated field in the query the report is based on. It
does show correctly in the query and in the simple report I refer to as
'CalculationChecker'.
The following fields are within the query in the order shown. They are
calculated within the query (unless noted with '*', these fields are from
the
table):

*Sale_Price

Rate: IIf([Sale_Price]0 And [Sale_Price]50,0.35,IIf([Sale_Price]=50 And
[Sale_Price]=299.99,0.25,IIf([Sale_Price]=300 And
[Sale_Price]=999.99,0.2,IIf([Sale_Price]=1000,0.15,99))))

*SPC

PreCom: [Sale_Price]*[Rate]

PreCom2: IIf([Sale_Price]=0.01 And [Sale_Price]=14,5,[PreCom])

*B_D_Option_Grp

Deluxe_Refund: IIf([B_D_Option_Grp]=2 And [PreCom2]0,5,0)

Commission: Commission: IIf([SPC]0 And
[Sale_Price]0,[SPC]-[Deluxe_Refund],[PreCom2]-[Deluxe_Refund])

Profit: IIf([PreCom2]0,[Sale_Price]-[Commission],0)

The record with the following properties produces a profit of 4.99 in the
'CalculationChecker' report but produces a blank on my application report
'ReceiptCreator':

Sale_Price = 9.99
SPC = 0.0
B_D_Option_Grp = 1

There are no #Name, #Error, Parameter prompts, no signs of trouble other
than a big blank spot where my 4.99 should be.

Thanks so much for your patience.

"Allen Browne" wrote:

Can you clarify the situation as it is now please?

Is Rate a calculated field in the query the report is based on? If so,
does
it show correctly in the query?

If Rate is only a calculated control on the text box, you will need to
trace
the values back to see where it's coming from.

Are there any controls (even hidden ones) that show #Name or #Error?
Solve
those first.

Otherwise, is Rate the name of the text box? Or its ControlSource? Or
both?
And what is it dependent on?

It may take some debugging to trace this back. The core concept is to add
an
extra text box bound to something that does work. Then take it an extra
step, and check it works. Taking it one step at a time, you can find the
point at which it fails, which gives you the clue as to what the cause
might
be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CanonMaiden" wrote in message
...
Hello Allen, Thanks for your reply.
I removed the quotes.
The If Sale_Price=0 thing was my attempt to avoid nulls. My
misunderstanding.... I removed that as well.
Sale_Price is never null or less than zero.
I did go back and ensure I didn't leave any IIf statements hanging
without
an else statement. Still getting blank results on my application report
while
the simple calculation report looks beautiful. Any other thoughts??
btw: I discovered your site a few weeks ago. In my humble opinion, it's
a
wonderful site and I thank you for sharing.
"Allen Browne" wrote:

The qoute marks around ".35" etc are telling JET to treat the RATE
field
as
*text.* I think you want to treat it as a number, so lose the quotes:
Rate:IIf([Sale_Price Between 0 And 50, 0.35, IIf(...

If Sale_Price is zero, multiplying by anything will yield zero, so
just
use:
PreCom: [Sale_Price] * [Rate]

Nz() applies where there are nulls. Form your subject line, I'm
assuming
you
have the zeros and so it is not a matter of nulls. However, you have
not
handled the case where Sale_Price is null (nor the case where it is
negative.)