variable equals executed SQL statement

  • Thread starter Thread starter Dan
  • Start date Start date
D

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.
 
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)
 
Back
Top