Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146

    Exclamation Unanswered: Returning #Error when theres no record

    I have a query that populates a subform based on specific criteria. The subform has a text box ([Summed]) in it's footer that contains "=Sum([Payment])" as it's control source. From the parent form, I then create another text box that contains the subform.Form!Summed output as it's control source. The problem is, if there are no records returned from the query, I get "#Error". I've tried both NZ and IIF statements to handle null values, but is it actually considered null if there is no record to begin with?

    My question is this, how can I make the text box on the parent form read 0 if there is no record returned by the query? I want to use the text box in a series of calculations and the #Error doesn't allow me to do this. I'd greatly appreciate any help on this.

  2. #2
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Just a brief thought from a brain dead person.
    If no records are being returned and NZ or an IIF is not working could you not add a count to your query and if the count is Zero then determine what you want the from to do.

    IIf ([subformcount] = 0 ,0,the formula you actualy want to use)

    Just my 2 cents I am sure someone smarter will look at my post and give a better idea.
    Darasen

  3. #3
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    I did some research on the topic and I beleive you might be on the right track with the Count. It seems to me that if there are no records returned by the query, then both NZ and IIF are useless. If a record was present and just contained null values, then NZ and IIF would probably work, but since there are no records, they fail to get the job done.

    This leads me to beleive that I need to count the number of records returned by the query. I have a subform that uses the query as it's record source, so I'd need to check the count for each new record. (Probably having something to do with the OnCurrent event)

    My new question is this: How can I count the number of records returned by a query? And if there are no records, how can I make a text box read "0" instead of "#Error". Anyone willing to help me out with this one? I'm stumped.

  4. #4
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Count

    I usually make two queries. First counts the number of records and the second actually provides the needed results.

    try to write some code...

    Some ideas

    if dcount("*", "table1") > 0 then
    docmd.openform "form1"
    else
    msgbox "No records..."
    end if

    or maybe

    if dcount("*", "table1") > 0 then
    form1.recordsource = "query_1"
    else
    form1.recordsource = "dummy_query"
    end if

    "dummy_query" ' _> instead of real results shows No data or sth like that
    Back to the basics...

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Just a suggestion:
    Have you investigated the NULL value from the display side. Providing you are using the sum value for display pruposes only, I'd be tempted to look at the values as set in the form / report

    eg in the forms "current" event tets if the value is null and if so hide the sum control.

  6. #6
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Quote Originally Posted by healdem
    Just a suggestion:
    Have you investigated the NULL value from the display side. Providing you are using the sum value for display pruposes only, I'd be tempted to look at the values as set in the form / report

    eg in the forms "current" event tets if the value is null and if so hide the sum control.
    I've also thought about this approach and tried it once. I tested to see if the value was NULL and if so, set another control to "0", but it never worked. Forgive me if I sound ignorant, but I'm thinking it doesn't work because the field I'm testing isn't actually NULL - it has "#Error" in it. Wouldn't the fact that the text "#Error" is in the control count as being "something" and therefore, not NULL.

    I've tested for this condition with the following code and it has always returned "False".

    If IsNull(Me.txtSum) then
    Me.txtTest.Value="True"
    Else
    Me.txtTest.Value="False"
    End If

    It's strange, I'm still convinced that this has something to do with checking the number of records returned by the query and if "0", programatically setting the value of a text box control on the form. On a side note, I'm actually using the SUM'd value in a calculation on the form, so simply hiding the control won't do it. I add the SUM values of two separate queries together to come to 1 total figure.
    Last edited by smacdonaldnc; 12-18-04 at 18:17.

  7. #7
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    I toyed with this for quite a bit today and finally figured it out with all of your help. I knew that the DCount function worked on tables, but I wasn't aware that it also worked on queries. I ended up using the following in the control source of my text box:

    =IIf(DCount("*","qryPayActive")>0,[subPayActive].Form!txtSum,0)

    Where:
    qryPayActive = Select query
    subPayActive = Subform containing the sum control
    txtSum = The control that contains the sum

    Many thanks to all those who offered suggestions on getting this to work. As always, the help was much appreciated. Have a nice weekend!

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just to be contrary, i don't think that's the best way.

    Dcount() is not so bad with small local tables, but with remote tables and/or huge tables you are making unnecessary round trips to the server and/or unnecessary re-queries.

    you can do it in one trip using .recordsetclone:

    private sub WhateverFillsYourForm() 'this could be Form_Load() or whatever
    if me.recordsetclone.recordcount = 0 then
    msgbox "no records"
    'and maybe, if it is useful, uncomment the following three lines:
    ' else
    ' me.recordsetclone.movelast
    ' msgbox "we got " & me.recordsetclone.recordcount & " records"
    end if
    end sub

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by smacdonaldnc
    I've also thought about this approach.....
    Did you test the contorl to see if it was set to "# Error"?, wondered if that might be vivisble in the control.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •