Query value in VBA variable?

  • Thread starter Thread starter alexvts
  • Start date Start date
A

alexvts

I have the following code in an access database event:

strCatYear = "SELECT CatYearID, cboYear, cboCat FROM tblCatYear " _
& " WHERE cboYear = " & "Year" _
& " AND cboCat = " & "Cat" & ";"

Is there a way to have the result of the query (which by the way is always a
single number) as the strCatYear value instead of the query itself?

Alex

P.S. I'm just starting with access and VBA. Any book recomendations would be
appreciated.
 
Sorry, but the results of that query won't be a single number: it'll be 3
values, since you've specified 3 fields in your SELECT statement.

One way to get the value of CatYearID (since you already know what value
will be returned with cboYear and cboCat) is to use DLookup:

MyValue = DLookup("CatYearID", "tblCatYear", "cboYear = " & Year & " AND
cboCat = " & Cat)

Another is to open a recordset based on the SQL statement you've generated,
and then retrieve the value of the appropriate field from that recordset.
 
Back
Top