Solved

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?

Posted on 2013-01-26
2
405 Views
Last Modified: 2013-01-27
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
Comment
Question by:zimmer9
2 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 250 total points
ID: 38823452
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
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 38823458
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question