getting rid of Error on reports of no records

[MICKI0220]

I have a report that calculates text boxes. If a report has no records then an error appears where the text box calculation is. How do I get this to either display 0 for no records or just blank in those fields?

 

[MazeWorX]

Search the forums before you post you may just find what you are looking for

http://www.tek-tips.com/viewthread.cfm?qid=842382

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

I don't use the IsError() function. If you want the report to open but display 0 in the calculated text boxes even if there are no records, I would use:

=IIf([HasData], [your expression here], 0)

If you want to display a blank:

=IIf([HasData], [your expression here], Null)

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I tried this and it still comes up with Error

=IIf([HasData],[Calc_Commision], 0)

 

[MazeWorX]

=IIf([Report].[HasData],[Calc_Commision],0)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MICKI0220]

Thanks for responding, however that still gives me the #Error in place of the text value. I copied and pasted the response.

 

[dhookom]

What is Calc_Commission? Is this a field or control or what?

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

It is a field based on a query.

 

[MazeWorX]

why not handle the no data in the query



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MICKI0220]

MazeWorx...
I am not familiar with what you are suggesting, can you please elaborate?

 

[dhookom]

MICKI0220,
Why are you referring to this as "calculates text boxes" and "text box calculation" when this is just a field from a query?

What aren't you telling us. The control isn't in a page section is it?


Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

I'm with dhookom on this one Mick the solutions provided are for calculated controls not a calculation performed by a query.

Maybe we could backtrack here and give us a detailed explanation of what you need.

is it a calculated control? is it based on a query? if so post the query SQL

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MICKI0220]

I didn't realize I confused anyone......There are several areas on this report that are erroring....One is the [cal_commision] field....it is a field directly from the query that this report uses as it's control...Then there are a few other text boxes that are dlookup boxes......When I said it was a calculated text box, I meant that another box uses the values from this text box in it's calculation. After I get the [cal_commision] text box working properly, I was hoping the same syntax could be used in my dlookup text box.....Did I make sense this time.....The query is posted below.

SELECT DISTINCT tbl_ats_Comm_Draw_Hist.Sls_RepID, tbl_ats_Comm_Draw_Hist.Tran_Type, tbl_ats_Comm_Draw_Hist.Cust_ID, tbl_ats_Comm_Draw_Hist.Contract_ID, tbl_ats_Comm_Draw_Hist.Calc_Comm_Draw_Amt, tbl_ats_Comm_Draw_Hist.Comm_Draw_Date, dbo_JobMaster.[Job Name - Master Reference], tbl_ats_Comm_Draw_Hist.Calc_Commision, tbl_ats_Comm_Draw_Hist.Inv_Date, tbl_ats_Comm_Draw_Hist.Inv_Num, tbl_ats_Comm_Draw_Hist.Pmt_Amt, tbl_ats_SlsRep.Rep_name, tbl_ats_Comm_Draw_Hist.Pmt_Date, tbl_ats_Comm_Draw_Hist.Search_Date, tbl_ats_Comm_Draw_Hist.EligibleForComm, tbl_ats_Contract.Search_Key
FROM tbl_ats_Contract AS tbl_ats_Contract_1, ((tbl_ats_Comm_Draw_Hist INNER JOIN dbo_JobMaster ON tbl_ats_Comm_Draw_Hist.Cust_ID = dbo_JobMaster.JobNo) INNER JOIN tbl_ats_SlsRep ON tbl_ats_Comm_Draw_Hist.Sls_RepID = tbl_ats_SlsRep.Sls_RepId) INNER JOIN tbl_ats_Contract ON tbl_ats_Comm_Draw_Hist.Contract_ID = tbl_ats_Contract.Bill_Type_ID
WHERE (((tbl_ats_Comm_Draw_Hist.Sls_RepID)=[Forms]![frmRptSalesRepPaidHist]![cboSls_RepFrom]) AND ((tbl_ats_Comm_Draw_Hist.Tran_Type)="C") AND ((tbl_ats_Comm_Draw_Hist.Calc_Commision)<>0) AND ((tbl_ats_Comm_Draw_Hist.Search_Date) Between [Forms]![frmRptSalesRepPaidHist]![DateFrom] And [Forms]![frmRptSalesRepPaidHist]![DateTo]) AND ((tbl_ats_Comm_Draw_Hist.EligibleForComm)=-1));

 

[MazeWorX]

Nz([tbl_ats_Comm_Draw_Hist].[Calc_Commision],0) AS 'renamethefieldhereminusthe quote'

you would need to edit your where statement to reflect the renamed field name, this should assign the value to 0 incase of a null since you exclude any 0 values for Calc_Commision in your where statement

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

So, you are stating a text box with a control source of Calc_Commision is showing Error?

Also, you mention calculations but you aren't providing any evidence of calculations. The least you could do is provide examples of control sources from "few other text boxes that are dlookup boxes"

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

dhookum,

Yes, the text box with control source of Calc_commision is showing error if no records are found from the above query.

Here is the code of the text box control source that does a lookup....=DLookUp("DrawAmount","qryDrawAmount")

code for one of the calculated text box's control source is...=(DLookUp("DrawAmount","qryDrawAmount")-(Sum([calc_commision])/2))

Thanks for your help

 

[dhookom]

Did you change the name of the Calc_Commision text box so that it is not Calc_Commision?

Did you try the HasData on the text boxes with DLookup()?

Does qryDrawAmount return only a single record?

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

is it in a sub report? spelled wrong? sometimes an error in a calculated control can cause other controls to error even though they are correct.



so since you already(below) assigned to a control the value of the draw amount and you assign the calc_commision value to a control why not just calculate referencing the controls replace the names i have used with the control names

=[[blue]txtDrawAmount[/blue]]-Nz((Sum([blue][txtCalCommision][/blue])/2),0)

.=Nz(DLookUp("[DrawAmount]","qryDrawAmount"),0)

btw is it me or shouldn't you minus the draw amount from the commission?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

actually

=[txtDrawAmount]-IIf(Nz([txtCalCommision]),0,(Sum([txtCalCommision])/2))

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>