Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013
    Posts
    4

    Unanswered: Record set erroring with "item not found" when returning null value **SOLVED**

    I have a form that I am using to pull data via VBA to populate a table. not the most efficient way to do it, but...
    Anyway, I am using DCOUNT to pull the number of hand held devices from my asset inventory by year of purchase and when I get to 3 years ago I receive the error of "Item not found in this collection".
    I know the code is good because it it pulling the information for 2 other years ao I am pretty sure it is pulling a null value for that year.

    My question is: How do I have it ignore the error and substitute a 0 in place?

    here is the line of code in question
    Code:
    rst("TabPC_Current_year_3").Value = DCount("[Asset_tag]", "info", "([Type***] Like 'Tablet' or [type***] like 'ipad' or [type***] like 'ipod' or [type***] like 'kindle') and ([po_date]" & poyr3 & ")")
    The exact same code is used for other years that are returning values.
    Last edited by frank.mccane; 12-12-13 at 08:46.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Consider using the nz function
    Or explicitly test if the value returned from the dcount isnull
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The date criteria in the DLookup looks funky (no "="), but if it works for other years it's probably fine. My guess from that error is that field name does not exist in the recordset. See if this throws the same error:

    rst("TabPC_Current_year_3").Value = 7

    If so, it's not the DCount, it's the field.
    Paul

  4. #4
    Join Date
    Dec 2013
    Posts
    4

    figured it out

    bad spelling on my part...

    thanks for the input

Tags for this Thread

Posting Permissions

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