Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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];
0
zimmer9
Asked:
zimmer9
2 Solutions
 
Kelvin SparksCommented:
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
 
mbizupCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now