Removing/replace special character in a field

  • Thread starter Thread starter jimt
  • Start date Start date
J

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.
 
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.
Click to expand...

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?
 
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.
 
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.
Click to expand...

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], "&", "")
 
Back
Top