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

    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
    200
    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,344
    Put a field from the query instead of the *.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    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
    7,070
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  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
    14,852
    As others are saying, check you calculations with Debug.print etc before trying to execute your code.

    Some excellent info for checking your code at :
    http://www.cpearson.com/excel/DebuggingVBA.aspx
    http://www.cpearson.com/excel/errorhandling.htm

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 - Senior Forums