SQL Select with Replace error

[yaccoff]

Why am I getting SQL errors for the use of REPLACE in EA SQL?

sub searchURLSql ( src_ea_guid, inputUrl )
   dim sSQL, f
   dim xml, xmlDoc
   
   f =    "to.ea_guid, tp.Name AS Package, to.Name AS Document, "
   f = f & "to.Alias, to.[Version], to.Note, "
   f = f & "t_p1.[Value] AS Type, t_p2.[Value] AS Origin, t_p3.[Value] AS URL, "
   f = f & "replace(t_p3.[value],'http://extranet.burts.co.uk','http://supernet.co.uk') AS NewURL "
   
   sSQL = "SELECT " & f   
   sSQL = sSQL & "FROM  ((((( t_object to "
   sSQL = sSQL & "LEFT JOIN t_package tp ON tp.Package_ID = to.Package_ID )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p0 ON (t_p0.Object_ID = to.Object_ID AND t_p0.Property = 'dataObjectRef') )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p1 ON (t_p1.Object_ID = to.Object_ID AND t_p1.Property = 'type') )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p2 ON (t_p2.Object_ID = to.Object_ID AND t_p2.Property = 'origin') )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p3 ON (t_p3.Object_ID = to.Object_ID AND t_p3.Property = 'URL') )"   
   sSQL = sSQL & "WHERE ((to.Stereotype = 'A_Document') AND (t_p3.[Value] IS NOT NULL) AND (t_p3.[Value] = inputURL))"
   
   TRACE ( sSQL )
   
   ' Execute sSQL query
   xml = Repository.SQLQuery (sSQL)
end sub

[qwerty]

Well, **what** error do you get? Which database do you use?

q.

[yaccoff]

Fair point. MS Access and the error is DAO.Database [3085] Undefined function 'replace' in expression

[qwerty]

And there you have your answer. MS Access is a, say, limited database. You can't do any SQL stuff in any DB. And Mickesoft always has its own flavors (plural!).

q.

[yaccoff]

Okay, looking at the MS Access SQL documentation it does seem sparse in terms of add on'd, is it possible to add/define your own functions? Replace?

[qwerty]

Something better asked at SO, I'd guess.

q.