check if query returns empty.

[dgoulston]

is it possible on a form to put some code that when a button is pressed it runs a query and then checks if the query returns anything, because the query accepts 3 like/between statements and 1 is a date which the user enters and if that date isnt in the database the query will not return anything

how do i check for this?

thanx
DAL

 

[ritchieroo]

Use RecordCount property

A RecordSets "RecordCount" property will be zero if there are no records, and will be non-zero if there are one or more records. (note: the actual value you get depends on the type of cursor used to open the RecordSet so don't rely on it necessarily be a total count).

In this example we test whether the recordset is empty and return a message. If it isn't empty we can return the SQL, which we might use to change to RowSource of a Form (for example).

Sub TestMe()

  With CurrentDb.OpenRecordset("select * from tblTest where Condition='Fail'")
    If (.RecordCount = 0) Then
      MsgBox "Recordset empty"
    Else
      MsgBox "Not empty"
      MsgBox .Name
    End If
  End With

End Sub

 

[dgoulston]

thanx a lot that sounds cool, i realised i could use Dlookup,.

forgot about that one.

DAL