 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / July 2005Null Problem
I have a Main Form with a Subform. In the footer of the subform I have an Expression that works as long as there is a record. If there are no records I get nothing, I want the total to be 0, I tried the two following expressions, they only work if there are records. Otherwise I see nothing. Does anyone know what I'm doing wrong? =Nz([TotwTax],Sum([TotwTax]),0) =IIf(IsNull([TotwTax]),Sum(TotwTax),0) Thanks DS Try this =nz(Sum([TotwTax]),0) > I have a Main Form with a Subform. In the footer of the subform I have > an Expression that works as long as there is a record. If there are no [quoted text clipped - 7 lines] > Thanks > DS > Try this > [quoted text clipped - 11 lines] >>Thanks >>DS Tried it and it doesn't work. Thanks DS > Try this > [quoted text clipped - 11 lines] >>Thanks >>DS I think he problem is with the Query itself. When I go to the Query, and have no records, the Query shows nothing. The Queries that work on the subForm show a record line even if there are no records. How can this be corrected. Thanks DS Try and change the UniqueRecords property of the query to true > > Try this > > [quoted text clipped - 18 lines] > Thanks > DS > Try and change the UniqueRecords property of the query to true > [quoted text clipped - 20 lines] >>Thanks >>DS That didn't work. I ended up using Tinas suggestion. Thank you for your help! As always, much appreciated. DS > I think he problem is with the Query itself. When I go to the Query, > and have no records, the Query shows nothing. The Queries that work on > the subForm show a record line even if there are no records. How can > this be corrected. > Thanks > DS well, the query itself *may not* be wrong. it sounds like you are working with a "non-updateable" query. if it's written correctly, and is returning the dataset you need, and if you do *not* need to be able to add/edit/delete records in the dataset, then the query is fine. forcing the unbound control in the subform to show a zero (0) when the query does not return any records - that just takes a little more work. try this: in the form's design view, go to the unbound control (i'll call it txtTotal), and delete the expression from the ControlSource property, leaving that line in the Properties box empty or blank. add the following procedure to the form's Open event, as Private Sub Form_Open(Cancel As Integer) If Me.Recordset.RecordCount = 0 Then Me!txtTotals = 0 Else Me!txtTotals.ControlSource = "=Nz(Sum([TotwTax]),0)" End If End Sub just change the name "txtTotals" to the name of your unbound control. if you don't know how to add a VBA procedure to a form, post back and i'll give you instructions - it's not hard to do. hth >>I think he problem is with the Query itself. When I go to the Query, >>and have no records, the Query shows nothing. The Queries that work on [quoted text clipped - 32 lines] > > hth Tina, That was pretty slick! It worked great! Thanks DS try =Nz(Sum([TotwTax]),0) hth > I have a Main Form with a Subform. In the footer of the subform I have > an Expression that works as long as there is a record. If there are no [quoted text clipped - 7 lines] > Thanks > DS > I have a Main Form with a Subform. In the footer of the subform I > have an Expression that works as long as there is a record. If there [quoted text clipped - 4 lines] > =Nz([TotwTax],Sum([TotwTax]),0) > =IIf(IsNull([TotwTax]),Sum(TotwTax),0) Try: =Nz(Sum([TotwTax]);0) Or =IIf(Not IsNumeric(Sum([TotwTax])),0,Sum(TotwTax)) Or =IIf(IsNull(Sum([TotwTax])),0,Sum(TotwTax)) Acki >> I have a Main Form with a Subform. In the footer of the subform I >> have an Expression that works as long as there is a record. If there [quoted text clipped - 18 lines] > > Acki I don't understand this! None of them work! It shoudn't be this hard. I've done this before! Thanks DS |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.