Removing/replace special character in a field

[jimt]

I have a table with a field for names. Some entries include the ampersand
(&); for example, John & Mary Smith. The database is also FTP to our web site
and a query, as a stored procedures, is used on an ASP to display
information. The name is selected via a drop-down combo list box on the page.

The problem is the & char drives the html validator crazy; 300+ errors, one
for each name that contains the &. I know in VBA you can use the replace
function to "strip" out a character or string. I've tried substitute and
replace in the query but they are not recognized as a valid function.

Is there a simple way to strip out the & in the name field within the
query(ies)?

Thanks
Jim T.

 

[John W. Vinson]

The problem is the & char drives the html validator crazy; 300+ errors, one
for each name that contains the &. I know in VBA you can use the replace
function to "strip" out a character or string. I've tried substitute and
replace in the query but they are not recognized as a valid function.

What version of Access do you have? It's only since, IIRC, A2002 that
Replace() has been allowed directly in a query. Could you post the SQL view of
the query that you tried, and the error message that it gave you?

 

[jimt]

I'm using Access 2000. The query works fine in both Access and on the Web
Page. The issue is when I try to validate the web page at W3-Validator the
data from the combo box using the query is brought in and errors out in the
validator because of the & char in the owner name field (database of property
owners in our small town).

I can ignore the validation errors but would like to have a clean validation
on all the pages as I convert the site from a site using Frontpage 2000 to a
new site using Expression 2.

As last resort option I can run the function to strip (replace) the special
characters and output the results to a new table to use on the web instead of
the current name table. However I would prefer to do it directly in a query
if possible.

Thanks
Jim T.

Thanks
Jim T.

 

[John W. Vinson]

As last resort option I can run the function to strip (replace) the special
characters and output the results to a new table to use on the web instead of
the current name table. However I would prefer to do it directly in a query
if possible.

With A2000, I'm pretty sure you'll need to create a wrapper function:

Public Function MyReplace(strIn As String, strOld, strNew)) As String
MyReplace = Replace(strIn, strOld, strNew)
End Function

In your query use

MyReplace([fieldname], "&", "")