none
countcollecting using VBA with Multiple Criteria RRS feed

  • Question

  • I have many queries were simply to come up with a number that is a count of records with a specific criteria, to be used later in a calculation. Because those criteria are so lengthy, and change with each variation of the dataI am  collecting, I’m not sure if an IIF structure would return the results you want. Crosstab queries can get quirky when fields are not available on different data runs. And to be honest, a crosstab query is really just a type of pivot table like in Excel, but not as flexible. I want to do most (if not all) of the count-collecting in VBA using recordsets or the DCOUNT() function. Then, dumping the values into a temporary table and base the report off of that. The queries I  already have built are doing pretty much what I described, but when I try to link them together for your final report, if anything had missing or null values, it causes problems. Doing the work in VBA givesme much more flexibility in handling these situations.
     But I do not know how to write up the function to do that how can u do what is above?
    Thursday, February 14, 2019 6:53 PM

All replies