Using vba function in query

[rorymurray]

HI,

I have written a function that basically gets a translation value from a table and returns it. Now I want to use this function on a field in a query, but I keep getting a message of "compile error in query expression get_costctr_code([pdept])".

The code is below:

function get_costctr_code(pronto_cd as number) as number

dim rs as DAO.recordset

set db=currentdb()
set rs = db.openrecordset("select * from tblCostCtrTrans where PCode = " & pronto_cd)

rs.movefirst

get_costctr_code = rs("LCostCtrCode&quot
rs.close

end function

I don't know if the problem is with the way I've typed the function in the query (Expr1: get_costctr_code([pdept]) ), or the function itself.

Thaks in advance.

Rory

 

[Nassu]

Function described in query OK - so problem is in VBA code. There are no data type Number. It should be Integer (probably). Also there are other errors (db is not declared, etc..) You have to compile module separately and You see, where is compilation error.

But why You do not add table tblCostCtrTrans to query. Than You can simpli relate pcode fields in main table and codes tabel and get immediale required value to query.

Another (slower) method would be function Dlookup, which You can use immediately in query:

Expr1: Dlookup("LCostCtrCode","tblCostCtrTrans","PCode="+Str$([PCode])

That should work (in my computer Access requres semicolons instead commas as function arguments separator)
Hopefully helps, Oleg

 

[rorymurray]

Thanks, don't know why I didn't think to add the table to the query...

I'll give it a go.