Error: “Undefined function ‘Replace$’ in expression” when Importing from Excel

Summary

The information in this article applies to LIMS 3.3.

A runtime error (see Figure 1) may occur in the results entry screens when importing results from Excel. Note that the error may be limited to a specific workstation in a multi-user installation and will only occur with LIMS version 3.3 or newer. This article describes how to troubleshoot and correct the error.

Figure 1 Runtime Error

More Information

When importing analysis results from Excel that contain both a result type and result value such as “< 0.1”, LIMS attempts to find a LIMS result type with a Report Format field that matches the cell’s contents. Prior to LIMS version 3.3, any spaces between the cell’s result type and result value had to exactly match the spacing in the result type’s Report Format field to successfully import the cell. Beginning with LIMS version 3.3 exactly matching the spacing is no longer necessary. However, the import may trigger the runtime error shown above.

The “Undefined function ‘Replace$’ in expression” error is caused by the workstation’s Microsoft Jet 4.0 Database Engine. The Jet database engine is a component of Microsoft Windows that is used by a number of applications including Microsoft Access and LIMS. Any attempt to reinstall LIMS will not solve the problem since LIMS does not install Jet, which is a component of Windows.

Use the following simple test to verify that the Jet 4.0 Database Engine is the cause of the problem. In the LIMS, select the Sample Summary report option on the Samples menu, click the SQL tab of the query controls then copy and paste the following expression in the “Enter SQL” field:

SampleID IN (SELECT SampleID FROM SampleAnalysis 
WHERE DLookup("[ResultTypeID]", "ResultType", 
"Replace$([ReportFormat], "" "", """") = '<#'") IS NOT NULL)

Click the [Query] button. If you receive the error shown in Figure 2 below, there is a problem with the Microsoft Jet 4.0 Database Engine on the workstation. The Structured Query Language (SQL) expression above includes the “Replace$” function which is also used when importing results from Excel. Using the function in an SQL expression isolates the problem to the Jet database engine.

Figure 2 SQL Error

Install the Latest Jet Service Pack

To correct the problem, first verify that the workstation has the most current version of the Jet database engine. In Windows Explorer, navigate to folder C:\Windows\System32, right-click file msjet40.dll, choose Properties then find the version on the Version or Details tab. At the time of this writing, the most current version of msjet40.dll for Windows XP systems is 4.0.9511.0. If you have an LIMS multi-user license, compare the Jet version with a workstation that does not exhibit the results import problem. See the Microsoft article How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine to verify the current version and to download and install the latest service pack. If you install a new Jet service pack, repeat the query with the SQL expression above. If the query still fails, proceed to the next step below.

Verify Jet’s Sandbox Mode

If the workstation has the latest Jet version and the problem persists, review the Jet 4.0 Database Engine’s “SandBoxMode” setting in the Windows registry. See the Microsoft article How to configure Jet 4.0 to prevent unsafe functions from running in Access for complete instructions. Jet’s Sandbox mode may be blocking expressions that include the Replace$ function. Figure 3 below shows the correct Jet 4.0 default SandBoxMode setting of 2 in the Windows registry.

Figure 3 Jet Sandbox Mode Registry Setting