Search code examples
ms-accessvbacountisnumeric

VBA Code to Count all Columns that are numeric in ListBox containing SQL Query Results

This is in Access 2007. I have tried writing somethig like:

Forms!MyForm!TextBox = Count(ISNUMERIC(ISNUMERIC(QueryResult.Reading)))

But this returns non-numeric results. I have also tried:

Forms!MyForm!TextBox = IIF(ISNUMERIC(QueryResult.Reading),Count(ISNUMERIC(QueryResult.Reading),"")

Anyone have any ideas? I am lost.

Solution

  • Try using SUM instead of COUNT, in such a way that you are counting a result if it is numeric and ignoring it if it is not. Please note, I am not in a position to test the code below, but it should give you an idea of what I mean. 

    Forms!MyForm!TextBox = SUM(IIF(ISNUMERIC(QueryResult.Reading),1,0))