Results 1 to 2 of 2
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    6 Feet Under

    Using DCount WIth Null Parameter In VBA

    I want to get a count of how many rows are returned from this query in VBA

    SELECT Count(ID) AS CountOfID FROM vbaImportList WHERE (((location) Is Null));
    I tried to use the DCount() function, but even when the count is >= 1 my variable continue never sets as False

    continue = True
    missingLocation = DCount("ID", "vbaImportList", Null)
    If missingLocation >= 1 Then
        continue = False
    End If
    Debug.Print continue
    Am I not understanding how DCount() works?

    Should I do something like this instead
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT Count(ID) AS CountOfID FROM vbaImportList WHERE (((location) Is Null));"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    missingLocation = rst.Fields(0)
    Set rst = Nothing

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Kelowna, BC, Canada
    Maybe try:
    continue = True
    missingLocation = DCount("*", "vbaImportList", "[location] Is Null")
    If missingLocation >= 1 Then
        continue = False
    End If
    Vlad Cucinschi
    MS Access Developer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Null parameter as wildcard
    By enilc in forum Queries
    Replies: 3
    Last Post: 09-18-2020, 03:31 PM
  2. Replies: 2
    Last Post: 11-27-2019, 01:14 PM
  3. Replies: 6
    Last Post: 06-02-2016, 08:07 PM
  4. dcount and is not null conflict?
    By Paintballlovr in forum Forms
    Replies: 10
    Last Post: 03-17-2014, 01:45 PM
  5. Parameter Query - No Null Record Msg
    By Dan Kenton in forum Queries
    Replies: 4
    Last Post: 02-18-2014, 08:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums