Undefined function error when creating database results page to MS Access DB from Front Page with Replace() function

Posted on 2003-03-28
Medium Priority
Last Modified: 2013-12-24
I'm trying to strip the dashes out of an ISBN string field for my book database for use in my web page. Here's the SQL query as created by MS Access (and subsequently modifed by me :-)

SELECT Editions.ID, Editions.ISBN, Replace([Editions].[ISBN],"-","") AS CleanISBN
FROM Editions;

The query works fine in Access, but I get the following error when I try to create a database results region in FrontPage 2000:

Server error: Unable to retrieve schema information from the record source 'Web: Edition Detail foo' in a database using the connection string:

'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/library.mdb'.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

I've read in various places that not all functions available in Access are supported by the ODBC driver, however my research seems to indicate that Replace() is an ODBC 1.0 function, and I'm using drivers with versions 4.0+

So I'd like to know how to tell the ODBC driver to smarten up, or how to remove the dashes after the data has been retrieved into the web page. I'm not adverse to using JavaScript or something like that, but I'd need a detailed answer, as I'm not familiar with JavaScript or other client side scripting options.

Question by:rstrang
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 14

Accepted Solution

hhammash earned 300 total points
ID: 8229616

You do not need to put it in your SQL statement.

Just make your DRW as normally,  then when you finish save your DRW.  AFTER saving your DRW put it on Spooky Diet.  Then replace the field name with this line:

Your line will be like this ORIGINALLY:


<%=replace (FP_Field(fp_rs,"ISBN"),"-","") %></td>

Note that here FP_Field is used instead of FP_FieldVal

You can find Spooky Diet on:

Special thanks to Spooky.

Best regards

Author Comment

ID: 8230402
Wow! Fast and easy. How come I didn't find the Spooky Diet when I was doing my search for Front Page web resources?

Thanks a lot!

LVL 14

Expert Comment

ID: 8233247
You are welcome


Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
Suggested Courses

762 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