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

    Using DCount WIth Null Parameter In VBA


    I want to get a count of how many rows are returned from this query in VBA
    Code:
    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

    Code:
    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?

    <strong>edit</strong>...
    Should I do something like this instead
    Code:
    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)
    
    rst.Close
    Set rst = Nothing

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

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