Zero length strings

[hansretallick]

I have a form which uses data from several subforms to populate a table. The subforms are based on several queries and other tables, but they all have one thing in common. When there is no data, the text boxes in the main form which look at the subforms display #Error. I have tried Nz and IIf IsNull in both the subform text boxes, and the main form, but still have the error. A typical text box in a subform is =Nz([Rent],0), and a sum text box based on this shows blank when no records. This is based on sql

SELECT qryRentPaid.LandlordID, qryRentPaid.RentAmount AS Rent, qryRentPaid.TenantFindOnly
FROM qryRentPaid
WHERE (((qryRentPaid.TenantFindOnly)=False));

On the main form a text box =frmSubRentPaid.Form!RentSum looks up the data in the subform, and this one shows #Error when no data is present. I have also tried this box with Nz and IIf IsNull, but to no avail. When data is present, everything works perfectly.
Please can someone help.

 

[pbaldy]

One way:

http://www.mvps.org/access/forms/frm0022.htm

Paul
MS Access MVP 2007/2008

 

[hansretallick]

Thank you Paul for your suggestion. I have visited the link, and done what was suggested, but I now have #Name as error message in the relevant fields. I might have done something stupid, so please forgive me if I describe what I have done. I have created a module called nnz in the objets/modules section of the database window, and populated it with the suggested code. I have changed the main form reference to =nnz(frmSubRentPaid.Form!RentSum) to refer to the subform. Perhaps you could see if I have done anything wrong here? Many thanks.

 

[PHV]

You can't have a function with the same name as a module, so rename the module.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hansretallick]

Fantastic, PHV. Such a stupid thing and it now works perfectly.

Best regards to you and to Paul for your help.