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
Reply to group, rather than allenbrowne at mvps dot org.
CanonMaiden said:
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 said:
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
Reply to group, rather than allenbrowne at mvps dot org.
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.
:
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.)
