I had been using the following to count a query result for a repor wheather or not to display the report

MyCount = DCount("*","QueryName")




What whould I need to to do to replace "QueryName" with an actual statement?

say - "Select * from Mytable where Myfield =34"

or better yet

SQLstr = "Select * from Mytable where Myfield =34"

then

MyCount = DCount("*",SQLstr)

???

TIA
Steve

You'd need something like:



If you absolutely must use the original SQL of SELECT * FROM MyTable WHERE MyField = 34, you'd have to do something like:

Hi Steve,

What version of Access are you using? I don't think you can use a SQL statement inside a DCount() function. You might have to write your own custom function that will accept SQL statements. What is the purpose of using a SQL statement? If only because the criteria changes, then DCount() function already has a facility for that. The syntax is:

DCount("*", "TableName", "FieldName=Value")

Just my 2 cents...


EDIT: And looks like Doug already wrote that custom function for you. Cheers!

I got this to work too

SQLStr = 'MyField = 34"

QCount = DCount("*", "Mytable", SQLstr)

If QCount = 0 Then
MsgBox "No Records Returned for Report", vbInformation, "No Records"
Else
'Opens Report with Where filter
DoCmd.OpenReport strDoc, acViewPreview, , SQLstr
End If


Thanks
Steve

Hi Steve,

Glad to hear you figured it out. Good luck with your project.

PS. For your scenario, another option is to use the Report's NoData event.

Cheers