In my Access 2003 application, I get the following error when I execute a query: Function is not available in query expression and I don't know why?

I am executing an Access application using Access 2003.
I run the following query:

qry6OpendItemsAppendWRS which is defined as follows:

INSERT INTO tblOpenItems ( Bank, Office, checkNum, refNo, [trans date], Type, [process date], Pending, AgeDays, T, Description, amount, UserID, AuditDate, footnote, responsibility, rptID, [REPORT NAME] )
SELECT qry5CreateOpenItemsWRS.bankNum, qry5CreateOpenItemsWRS.office, qry5CreateOpenItemsWRS.checkNum, qry5CreateOpenItemsWRS.refNum, qry5CreateOpenItemsWRS.Expr2, qry5CreateOpenItemsWRS.transType, qry5CreateOpenItemsWRS.Expr3, qry5CreateOpenItemsWRS.pending, qry5CreateOpenItemsWRS.Expr4, qry5CreateOpenItemsWRS.cr_db, qry5CreateOpenItemsWRS.descr, qry5CreateOpenItemsWRS.Expr5, qry5CreateOpenItemsWRS.userID, qry5CreateOpenItemsWRS.auditDt, qry5CreateOpenItemsWRS.Expr6, qry5CreateOpenItemsWRS.Expr7, qry5CreateOpenItemsWRS.rptID, qry5CreateOpenItemsWRS.[REPORT NAME]
FROM qry5CreateOpenItemsWRS;

I get the following error:

Runtime error '3075'

Function is not available in query expression
'IIf(isnumeric(clientacct),left(clientacct,3),"")'.
----------------------------------------------------------

qry5CreateOpenItemsWRS is defined as follows:



SELECT OI_WRS.bankNum,
IIf(isnumeric(clientacct),left(clientacct,3),"") AS office,

IIf(isnumeric(clientacct),right(clientacct,6),clientacct) AS checkNum,

OI_WRS.refNum, OI_WRS.transMnth & "/" & OI_WRS.transDay & "/" & OI_WRS.transYr AS Expr2, OI_WRS.transType, OI_WRS.procMnth & "/" & OI_WRS.procDay & "/" & OI_WRS.processYr AS Expr3, OI_WRS.pending, OI_WRS.age*1 AS Expr4, OI_WRS.cr_db, OI_WRS.descr, IIf(sign="-",CDbl(OI_WRS.amount*(-1)/100),CDbl(amount)/100) AS Expr5, OI_WRS.userID, OI_WRS.auditDt, " " AS Expr6, " " AS Expr7, tblBanks.rptID, tblBanks.[REPORT NAME]
FROM OI_WRS INNER JOIN tblBanks ON OI_WRS.bankNum=tblBanks.[Bank Code];
zimmer9Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
If that doesn't help,  check your Macro Security settings.   I believe a setting of 'high' will prevent VB expressions from running in property sheets and queries in Access 2003.
0
 
Kelvin SparksConnect With a Mentor Commented:
Generally, this error occurs if you have a brokne reference in VBA. Goto any VBA windows, click tools, REferences and see if any of the ones that are ticked have the worg MISSING after then. Resolve that problem and you should be away OK.


Kelvin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.