Return the number of records in a query

If the query does not take long time then you may execute it twice:
1)  SELECT COUNT(*)  FROM YourTable WHERE YourConditions   ... returns number of rows
2) SELECT field1, field2, ...  FROM YourTable WHERE YourConditions  ... returns data rows

Hi James, I'm just getting this and it's late. I will put the code uphow tomorrow. As for reports embedded, I'm probably using the wrong terminology. I'm using access 2010 and I created a report and dragged it onto a form. Similar to a subform, only it's a report. It works great for what I'm developing (a phone directory). Not sure if that can be done in previous versions.

Pcelba, it's not a long query and I can execute it twice, but I'm not sure how your solution will help. How do I get a record count out of that? I apologize in advance as I haven't developed anything in many years and I'm just getting back to it again.

 Thanks so much to both of you for responding!

To reduce our assumptions, upload a sample database. Tell what to expect.
Report not to display?
Just a message is displayed?
....

One way:
Have an unbound field on main from say, TextS, and the same on Subreport.
Set TextS on Subform =1
Link report control with master and child fields as TextS.

The search process set TextS on mainform to 1 if dataexists, 0 if not.
Refresh displays the found records.

You can use my CreateAndTestQuery function to return the number of records in a query.  Here is some sample code that uses it, followed by the function:

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   If lngCount = 0 Then
      strPrompt = "No records found; canceling"
      strTitle = "Canceling"
      MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
      GoTo ErrorHandlerExit
   Else
      'Use this line if you need a recordset
      Set rst = dbs.OpenRecordset(strQuery)
   End If

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 11-May-2013

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   'Delete old query
   CurrentDb.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = CurrentDb.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = CurrentDb.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Select allOpen in new window

Hi everyone. I am at work today and don't have the db with me. I can prepare a sample tonight if you still need it. In the interim, I'm attaching 3 screen shots and some code in the hopes it will clarify. The code is from a previous iteration, but it's basically the same. The user enters some search criteria (up to 3 fields) I add * to any left out. Click on search, and a query is run and results are displayed in the report. The report is used for display only, there's no interaction with the user so it's not connected with master/child fields as a subform would be.  The form is just a container for the controls and to hold the report.It all works great with the exception of not being able to tell when there are no records that match the criteria. I've included the module code I tried, but it didn't work. I think I put the code to call the module in the "search form code" just under the requery. It didn't work so I took it out.  Looking at this setup, is there a way to take the criteria, run the query, and if there's no records found return a message to the user stating that "There are no records matching criteria?" I would think this is such a basic thing, but I just can't figure it out.....Thanks so much for all your help!
sample-form.png
design-view.png
Objects.png
Code-for-Sample.docx

Thanks Helen. I will test it tonight!

I wasn't able to use any of the solutions in their entirety, but I looked around a little more and found the DCount method was the simplest to implement. I placed the below code in the search buttons click event and it works great! Thank you so much everyone, I appreciate all your help!!
 Me.sub_rpt_StaffPhoneList.Requery
   
    RecCount = DCount("*", "qry_staffphonelist")