Testing the Return value of a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to test to see if a query comes back null or not.

If not null, it should enable a control.

Ron
 
Try

If DCount("*","[QueryName]") = 0 Then
Me.[ControlName].Enabled = False
Else
Me.[ControlName].Enabled = True
End If

Or, to make it shorter:

Me.[ControlName].Enabled = (DCount("*","[QueryName]") = 0)
 
RonMan said:
I need to test to see if a query comes back null or not.

If not null, it should enable a control.
Click to expand...

mu.... (meaning question too big) 8-)

If you have a named (stored) query,
and you want to know if it
returns any records....

If DCount("*","nameofquery") > 0 Then
Me!somecontrol.Enabled = True
Else
Me!somecontrol.Enabled = False
End If

the rub:

DCount(expr, domain[, criteria])

"domain" = A string expression identifying the set of records that
constitutes the domain.
It can be a *table name or a query name.*

{that rules out using a SQL stmt for domain ---
DCount("*","SELECT * FROM tbl") will give error 3078!
or
strSQL = "SELECT * FROM tbl"
?DCount("*",strSQL) will give error 3078!!}

workaround -- redefine SQL of some stored query

strSQL = "SELECT * FROM tbl WHERE n=1"
CurrentDb.QueryDefs("nameofquery").SQL = strSQL

If DCount("*","nameofquery") > 0 Then
Me!somecontrol.Enabled = True
Else
Me!somecontrol.Enabled = False
End If


"expr" -- The DCount function doesn't count records that contain Null values
in the field
referenced by expr, unless expr is the asterisk (*) wildcard
character.
If you use an asterisk, the DCount function calculates the
total number of records,
including those that contain Null fields (as would using pk
for expr).

So, you can construct "expr" to determine if a field (or several fields)
will return non-null values...

--- ex 1: count non-null in field "f1"

If DCount("f1","nameofquery")>0 Then

{counts records in "nameofquery" where
"f1" is not Null}

--- ex 2: count non-nulls in "either" fields "f1" or "f2"

If DCount("[f1] & [f2]","nameofquery")>0 Then

{counts records in "nameofquery" where
"f1" is not Null or "f2" is not Null}

--- ex 3: count "simultaneous" non-nulls in "f1" and "f2"

If DCount("[f1] + [f2]","nameofquery")>0 Then

{the "+" propagates Nulls, so a record in "nameofquery"
will only be counted if *both* fields are not Null}
 
Back
Top