HOW DO CHECK IF QUERY RETURNS ANY RECORS 4

[villica]

I have a report which the the control source is a query, but this query is based on other queries. How do I check if the query result is greater than zero before displaying the report. If there is not data I want a mesage no data and no display the report

thanks everyone for any suggetions

 

[TheInsider]

Not the suggestion that you probably wanted but... you could code the report's "On No Data" event to display a message and close the report right away. Rob Marriott
rob@career-connections.net

 

[raskew]

This function will count the number of records in your query. Is this enough to get you on the right track?

Function CountRecords(pstrname As String) As Long

'this function counts the number of records in a query

Dim myDB As DATABASE
Dim ctr As Integer, num As Integer
Dim myrs As Recordset

On Error GoTo points_Err:

Set myDB = CurrentDb
Set myrs = myDB.OpenRecordset("select * from [" & pstrname & "]&quot

If myrs.BOF = True And myrs.EOF = True Then
num = 0
Else
myrs.MoveFirst
myrs.MoveLast
num = myrs.RecordCount
End If
myrs.Close
CountRecords = num
Exit Function

 

[villica]

Hello I posted this question a while a go and then requirement was cancel but now is back againg. I would like to thank Raskew for your reply. If Raskew is not on this forum any longer I am hopping someone else can help me figure out this code. First of all what is pstrname and where do I call this function from.

thanks anyone.

 

[gwinn7]

Hello,

Pstrname is a parameter passed to the function that represents the name of the table the code requires to build the SQL statement.

I must add that the EOF check is not necessary. The BOF is sufficient. Here is another way I would write the same code...

Set myrs = _
currentdb.OpenRecordset("select * from [" & pstrname & "]&quot

num = 0

If Not myrs.BOF Then
myrs.MoveLast
num = myrs.RecordCount
myrs.MoveFirst
End If

It does the same thing, but less typing.


Gary
gwinn7

 

[villica]

Thank you for your reply, where do I call this function from. I Have a button that opens the report, is this the place do it on the on clikc even. thank you

 

[kjc]

I like to use:

MyVariable = Dcount("QueryFieldName","MyQueryName&quot

Then you can 'If' or 'Select Case' on MyVariable
(it will contain a 0 if there are no records...)

 

[villica]

thanks you very much all of you.

 

[tonyflavell]

There is a very easy way to do this. Use

If IsNull(DLookup("FieldName","QueryName&quot) then "Close Report" else "Run Report"