Results 1 to 6 of 6
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209

    VBA To Check If Query Has At Least One Row Of Results

    I am using vba to build a query from a simple select statement and am exporting the query results to a .csv file using Cmd.TransferSpreadsheet. There have been a few instances where a blank spreadsheet is exported, and I would like to combat that somehow. Is there a way to check the row count from the query before exporting to .csv, and if the rowcount = 0 then display a message on screen to the user that the query returned 0 results.

  2. #2
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    I tried to use the Dcount() function to do this, but it is exporting to CSV anyway - possibly my DCOUNT() function is not correct
    Code:
    If DCount("*","exportquery") > 0 Then
          ExportToCSV
    Else  
          MsgBox("No Data To Export")
    End If

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Put a field from the query instead of the *.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    does your exportquery have parameters? if so, then dcount is probably not working

    suggest you check the number returned using

    debug.print DCount("*","exportquery")

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe what follows (or even precedes) your code snippet is the problem. After the message box, there's no Exit statement, but we can't see what comes after.
    Try stepping through to see what happens, and check the value of the dcount as you watch what happens when it's > 0 and not > 0.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 2
    Last Post: 04-15-2017, 11:09 AM
  2. Replies: 2
    Last Post: 01-10-2016, 06:47 PM
  3. Replies: 5
    Last Post: 05-21-2013, 02:21 PM
  4. Replies: 3
    Last Post: 05-02-2013, 10:36 AM
  5. Replies: 6
    Last Post: 05-14-2012, 07:24 AM

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