Results 1 to 6 of 6
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    count records in query, export query only if count achieved

    Hello,



    I have a requirement to spit out a spreadsheet from an Access DB if the record count of a query is greater than 0 or (or would it be not null?).

    In this case, I am searching for special characters in a file that must be removed prior to proceeding, and an analyst must manually remove them (automation of this task is not ideal at this time).

    The query finds records with problem chars, and then if there are any records in that set, it needs to export. I already know how to export and have a functioning query, I just need help with creating vba to instruct it to export ONLY if it finds some records meeting that criteria.

    Thanks in advance,

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    could be as simple as
    Code:
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset ("qryNameOfYourQuery", dbOpenDynaset)
    If rs.RecordCount > 0
      'export, output, whatever
    End If
    
    Set rs = Nothing
    This assumes you have a reference to DAO and you don't care about the count of records, just that there's at least one. From reading your post, I'm guessing you're OK with the rest, so I didn't put in any error checking.
    Last edited by Micron; 02-07-2017 at 06:12 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    and an analyst must manually remove them (automation of this task is not ideal at this time)
    I would think it would be easy to automatically remove the special characters from the dataset (query) using the Replace function.
    Or you could remove the special characters from the tables, then export the data.......

  4. #4
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by ssanfu View Post
    I would think it would be easy to automatically remove the special characters from the dataset (query) using the Replace function.
    Or you could remove the special characters from the tables, then export the data.......
    That is, of course, very reasonable and correct. However, my "client" (boss, who knows nothing about access) would prefer at this time not to destroy the files we import and recreate them again and then load them into a second system where they are fed into. (tihs is the same project you and I are discussing for Vendor/Voucher file importation in the other thread)

    Essentially, the data is considered too sensitive to trust this auto-process to auto-correct, in addition to auto-checking certain data integrity needs.

    EDIT: I should also add that this step in our biz process is not the ideal step to remove special chars, but rather in an earlier step when these files are initially created by an analyst, who QC's some files coming out of a system. Hoewver, I have tackled automating this step in our process first, and then will tackle that later. But in the meantime, we need to be aware of any special chars that exist...

  5. #5
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by Micron View Post
    could be as simple as
    Code:
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset ("qryNameOfYourQuery", dbOpenDynaset)
    If rs.RecordCount > 0
      'export, output, whatever
    End If
    
    Set rs = Nothing
    This assumes you have a reference to DAO and you don't care about the count of records, just that there's at least one. From reading your post, I'm guessing you're OK with the rest, so I didn't put in any error checking.
    Thank you, this worked perfectly!

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Welcome,you are.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-30-2016, 06:38 PM
  2. Year Count and Month Count Same Query
    By NateSmith in forum Queries
    Replies: 1
    Last Post: 05-13-2015, 08:23 AM
  3. Count of Records in Query
    By McArthurGDM in forum Access
    Replies: 2
    Last Post: 01-12-2015, 09:22 AM
  4. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  5. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 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