Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count the number of rows in a query result set

Posted on 2008-06-16
9
Medium Priority
?
2,746 Views
Last Modified: 2013-11-27
I have a procedure that, depending on whether the query returns 0 rows it must do something and if it returns 1 or more, do something else.  So, how so I simply count the number of rows in a query?  I need to count the number or rows in qryPauseDurations first, if 0, then I only want to run the one query in the ElseIf portion.
Private Sub RunDurations()
'Runs queries to calculate durations
If RowCount >= 1 Then
 
        DoCmd.OpenQuery "qryPauseDurations", acViewNormal 'Need to    count number of rows in this query
 
        DoCmd.OpenQuery "qryStepDurations", acViewNormal
        DoCmd.OpenQuery "qryTaskDurations", acViewNormal
        DoCmd.OpenQuery "qryUpdatePauseStepDurations", acViewNormal
        DoCmd.Close acQuery, "qryPauseDurations"
        DoCmd.Close acQuery, "qryStepDurations"
        DoCmd.Close acQuery, "qryTaskDurations"
        DoCmd.Close acQuery, "qryUpdatePauseStepDurations"
ElseIf RowCount = 0 Then
        DoCmd.OpenQuery "qryUpdateStepOnly", acViewNormal
End If
End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 6
  • 3
9 Comments
 
LVL 75
ID: 21797183
If DCount("*", "qryPauseDurations") = 0  then

0
 
LVL 75
ID: 21797196
Private Sub RunDurations()
'Runs queries to calculate durations
If DCount("*", "qryPauseDurations") >= 0  then
 
        DoCmd.OpenQuery "qryPauseDurations", acViewNormal 'Need to    count number of rows in this query
 
        DoCmd.OpenQuery "qryStepDurations", acViewNormal
        DoCmd.OpenQuery "qryTaskDurations", acViewNormal
        DoCmd.OpenQuery "qryUpdatePauseStepDurations", acViewNormal
        DoCmd.Close acQuery, "qryPauseDurations"
        DoCmd.Close acQuery, "qryStepDurations"
        DoCmd.Close acQuery, "qryTaskDurations"
        DoCmd.Close acQuery, "qryUpdatePauseStepDurations"
Else
        DoCmd.OpenQuery "qryUpdateStepOnly", acViewNormal
End If
End Sub
0
 
LVL 75
ID: 21797211
And you won't need this:

        DoCmd.OpenQuery "qryPauseDurations", acViewNormal 'Need to    count number of rows in this query

unless you really want to open also ??

Private Sub RunDurations()
'Runs queries to calculate durations
If DCount("*", "qryPauseDurations") >= 0  then
         DoCmd.OpenQuery "qryStepDurations", acViewNormal
        DoCmd.OpenQuery "qryTaskDurations", acViewNormal
        DoCmd.OpenQuery "qryUpdatePauseStepDurations", acViewNormal
        DoCmd.Close acQuery, "qryPauseDurations"
        DoCmd.Close acQuery, "qryStepDurations"
        DoCmd.Close acQuery, "qryTaskDurations"
        DoCmd.Close acQuery, "qryUpdatePauseStepDurations"
Else
        DoCmd.OpenQuery "qryUpdateStepOnly", acViewNormal
End If
End Sub
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:ssmith94015
ID: 21797335
No, don't care to open, be right back.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 21797359
***I made a TYPO ***

Private Sub RunDurations()
'Runs queries to calculate durations
If DCount("*", "qryPauseDurations") > 0  then                                     '   ** HAD TYPO HERE >=0
         DoCmd.OpenQuery "qryStepDurations", acViewNormal
        DoCmd.OpenQuery "qryTaskDurations", acViewNormal
        DoCmd.OpenQuery "qryUpdatePauseStepDurations", acViewNormal
        DoCmd.Close acQuery, "qryPauseDurations"
        DoCmd.Close acQuery, "qryStepDurations"
        DoCmd.Close acQuery, "qryTaskDurations"
        DoCmd.Close acQuery, "qryUpdatePauseStepDurations"
Else
        DoCmd.OpenQuery "qryUpdateStepOnly", acViewNormal
End If
End Sub
0
 

Author Comment

by:ssmith94015
ID: 21797406
Ok, it recorgnized if it i 1 or more and runs the first if code.  However, it simply does not recognize what to do if there are 0 rows.  Let me check the query for that first.....
0
 
LVL 75
ID: 21797434
I had a typo - see my correction above  @ http:#a21797359
0
 

Author Comment

by:ssmith94015
ID: 21797545
Thanks DatabaseMX, it seems to be working.
0
 
LVL 75
ID: 21797562
you are welcome.  sorry for the typo confusion ...

mx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question