asked on

How to avoid a Runtime error 3075, Function is not available in query expression when importing a txt file into an Access table?

I am developing an application using Access 2003.

I import a txt file into an Access table.

I attached my Access mdb file and the txt file that I am importing.

When I click on the Import button from the interface, I get the following error message:

Runtime error '3075'

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

-----------------------------------------------------------
OI-WRS.txt
ReportsDB.mdb

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jerryb30

Get this when I open:

zimmer9

ASKER

OI-WRS.txt
ReportsDB.mdb

zimmer9

ASKER

I updated my Access mdb file to remove a reference to PDFCreator.exe

kmslogic

I think what's happening is that you're encountering null values in your table, and your expressions can't handle that. Use the nz() function to fixup null values like:

IIf(isnumeric(clientacct),left(clientacct,3),"")

becomes

IIf(isnumeric(nz(clientacct,"")),left(nz(clientacct, ""),3),"")

for numeric columns use

nz(numericcolumn, 0)

in your expressions

Jim Dettman (EE MVE)

<<I updated my Access mdb file to remove a reference to PDFCreator.exe >>

Does your app compile?

Jim.

kmslogic

I'm curious what the final resolution of this was--did you remove a reference from the vba editor and your app started working? Which one?

zimmer9

ASKER

I checked on a reference I wasn't using. Then I closed the Access application. Then I reopened the application and unchecked this reference and saved once again and as Jim stated this procedure forces a refresh of the references collection.