variable equals executed SQL statement

[Dan]

How can you assign a variable to equal the results of
executing a SQL statement. I want to do something like:

strSQL = "SELECT MAX(BillNumber From [Bills] WHERE ..."
billNumber = CurrentDb.Execute strSQL, dbFailOnError

it doesn't work when you say "billNumber = " to the
executed DB but I need to set the billNumber equal to
that.

 

[Sandra Daigle]

A query doesn't return a value. You can use a query to return a recordset
but I think you are looking for the Dmax Function instead.

billNumber=nz(dmax("BillNumber","Bills",<where criteria>),0)

Replace <where criteria> with your criteria - something like:

"Custid=" & me.custid

Making the full statement:

billNumber=nz(dmax("BillNumber","Bills", "Custid=" & me.custid),0)