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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <CanonMaiden@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2736084F-30B1-45D5-8685-A72C2F7D136D@xxxxxxxxxxxxxxxx
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" <CanonMaiden@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B8DB5B90-6DE7-4859-8F9B-0FF58309E1D8@xxxxxxxxxxxxxxxx
> 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.)



.

Quantcast