Using query results in code

[Z34Lee]

My query:

SELECT Count(*) AS Expr1
FROM tblBuildListTemplate
WHERE (((tblBuildListTemplate.[Part ID#]) Is Null));

This returns the number of records in the table where the field Part ID# is null.

When this query is run, a value is given in the Expr1 field. My question is, how can I then call that value in code? For example, I would like to do something like this:

DoCmd.OpenQuery "qryBLPartIDcount"
If [qryBLPartIDcount]![Expr1] > 0 Then
....

 

[modest]

Use the query as a hidden textbox's control source. And use the textbox value in the IF statement, or...

Dim rs as DAO.recordset
Set rs = CurrentDb.OpenRecordset("Query Name",dbReadyOnly)

If rs.fields(0) > 0 Then 'instead of 0 you can use rs.fields("field name")
...
End If

 

[KeithG]

You should also look into the DCount function.

 

[Z34Lee]

I had tried the text box method before I posted and had no luck, but the code given in the first response worked perfectly. Thanks!