Query value in VBA variable?

[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.

 

[Douglas J. Steele]

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.

 

[alexvts]

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

cboCat = " & Cat)

Worked great!

Thanks a lot
Alex