0

I am attempting to use the DCount() Function to return a count from my table. My issue is that it always returns a NULL value.

How should I Re-write this VBA statement so that it returns the accurate count?

ReturnedCount = DCount("CountOfItems", "[__TestTable]", "NameOfItem = " & ItemName)
Debug.Print ReturnedCount
Erik A
  • 31,639
  • 12
  • 42
  • 67
IcyPopTarts
  • 494
  • 1
  • 12
  • 25

2 Answers2

1

NameOfItem implies a string. You need to wrap strings in single quotes when passing them as a parameter to a D-Function; just like passing them as a parameter in a Query.

ReturnedCount = DCount("CountOfItems", "[__TestTable]", "NameOfItem = '" & ItemName & "'")

Using the immediate window to test your D-Functions will simplify debugging.

enter image description here

  • That gives me a compile error - expected expression error – IcyPopTarts Oct 18 '17 at 20:25
  • I was missing a double quote. –  Oct 18 '17 at 20:27
  • When I step through the code and I use a Debug.Print ReturnedCount it is Empty - and if it matters CountOfItems is a Number and NameOfItem is a shortttext – IcyPopTarts Oct 18 '17 at 20:28
  • `DCount` will return 0 but never returns empty. Likewise if `ReturnedCount` was properly typed (e.g. Dim ReturnedCount As Long) then it would not return empty. –  Oct 18 '17 at 20:46
  • Is there a way to have it return 0 if NULL? And turns out DSUM() is what I was after as well ,not the DCount() – IcyPopTarts Oct 18 '17 at 20:53
  • I don't think that the D-Functions can return null. In any case, [Nz Function](https://msdn.microsoft.com/en-us/library/office/aa172237(v=office.11).aspx) is used in Access to return a differenct value when null. (e.g. NZ(Value,0) returns 0 if Value is null) –  Oct 18 '17 at 21:01
0

You should use:

On Error Goto 0
ReturnedCount = DCount("*", "[__TestTable]", "NameOfItem = '" & ItemName & "'")

It will at least return 0 (zero) ... if the table and field names are correct.

Gustav
  • 53,498
  • 7
  • 29
  • 55