"0.0" Value (not Null)... missing value in report
in [Reports]
|
Prev: Change Group Level order on Access Report from selection made on f
Next: How can I display a Report (Access 2003) on a web page?
From: CanonMaiden on 12 Jul 2008 13:02 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!
From: Allen Browne on 13 Jul 2008 03:43 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" <CanonMaiden(a)discussions.microsoft.com> wrote in message news:2D478C1B-42D9-4EE1-9E7B-B6CE90DA7E4E(a)microsoft.com... >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!
From: CanonMaiden on 15 Jul 2008 23:37 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" <CanonMaiden(a)discussions.microsoft.com> wrote in message > news:2D478C1B-42D9-4EE1-9E7B-B6CE90DA7E4E(a)microsoft.com... > >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! > >
From: Allen Browne on 16 Jul 2008 03:28 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(a)discussions.microsoft.com> wrote in message news:B8DB5B90-6DE7-4859-8F9B-0FF58309E1D8(a)microsoft.com... > 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.)
From: CanonMaiden on 16 Jul 2008 12:58
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(a)discussions.microsoft.com> wrote in message > news:B8DB5B90-6DE7-4859-8F9B-0FF58309E1D8(a)microsoft.com... > > 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.) > > |