Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    replace characters in a string

    hi,

    Below is the query I am trying to run and error it is given below.
    SELECT '<a href='+A.[Value]+'>'+Replace(A.[Value],"#","?")+'</a>' as total Value from Temp1

    Error:
    Undefined function 'Replace' in expression.

    Let me know is there any other way i can replace the Characters in the query.

    Thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Value is a reserved word and not to be used by you for a field name. It also looks like you have other syntax errors in your SQL: where is the table named A?

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Actual Query looks like below I have changed while posting which works correctly without 'replace' function.


    SELECT (A.[Value]) as [URL],A.[Visits],A.[Total Requests ]
    from(
    SELECT Parameter, ParamValue as [Value],
    iif(count(*)>0,format$(sum(sVisits),"#,##0"),0) as [Visits] ,
    format$(sum(sRequests),"#,##0") as [Total Requests]
    FROM temp0 GROUP BY Parameter, ParamValue
    ORDER BY Parameter, sum(sVisits) DESC,
    ParamValue) A

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So this thread is now ready for the Solved thread tool?

  5. #5
    Join Date
    Jul 2009
    Posts
    7
    no,the 'replace' function does not work when applied to above query.

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    Is there any alternative to 'REPLACE' function in MS Acess 2000 ?

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you checked your references for a *bogus* MISSING reference?

    Here's a function writen by Bob Askew.
    Code:
    Function onespace2(pStr As String, pDelim As String) As String
    '*******************************************
    'Purpose: Removes excessive spaces from a string
    ' and replaces remaining spaces with user
    ' selected delimiter.
    'Coded by: raskew 'Inputs: From debug window:
    ' ? onespace2(" the quick brown fox", ".")
    'Output: "the.quick.brown.fox"
    '*******************************************
       Dim strHold As String
       strHold = RTrim(pStr)
       Do While InStr(strHold, " ") > 0
          strHold = left(strHold, InStr(strHold, " ") - 1) & Mid(strHold, InStr(strHold, " ") + 1)
       Loop
       strHold = Trim(strHold)
       Do While InStr(strHold, " ") > 0
          strHold = left(strHold, InStr(strHold, " ") - 1) & pDelim & Mid(strHold, InStr(strHold, " ") + 1)
       Loop
       onespace2 = Trim(strHold)
    End Function
    
    Function Replace97( _
             Expression As String, _
             Find As String, _
             sReplace97 As String, _
             Optional start As Long = 1, _
             Optional Count As Long = -1, _
             Optional Compare As Long) As String
       Dim pos As Long
       Dim strTmp As String
       Dim LenFind As Long
       Dim LenReplace97 As Long
       Dim cnt As Long
       If Len(Expression) = 0 Then Exit Function
       If start > Len(Expression) Then Exit Function
       If Len(Find) = 0 Then
          Replace97 = Expression
          Exit Function
       End If
       If start < 1 Or start > Len(Expression) Then start = 1
       If Compare < 0 Or Compare > 2 Then Compare = 0
       strTmp = Expression
       LenFind = Len(Find)
       LenReplace97 = Len(sReplace97)
       pos = InStr(start, strTmp, Find, Compare)
       Do While pos > 0 And cnt <> Count
          strTmp = left$(strTmp, pos - 1) & sReplace97 & Mid$(strTmp, pos + LenFind)
          pos = InStr(pos + LenReplace97, strTmp, Find, Compare)
          cnt = cnt + 1
       Loop
       Replace97 = strTmp
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need to delete special characters
    By tlrutledge in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:10 AM
  2. Replies: 2
    Last Post: 08-04-2008, 04:16 PM
  3. Special Characters
    By orgelizer in forum Access
    Replies: 0
    Last Post: 03-20-2007, 08:24 PM
  4. Strings limited to 255 characters
    By ocordero in forum Programming
    Replies: 4
    Last Post: 08-09-2006, 09:13 AM
  5. Replies: 0
    Last Post: 12-05-2005, 04:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums