Replace i where-sætning (ADO)

Jeg ved ikke helt hvorfor det ikke virker i ADO.
Du kunne ikke - én gang for alle - opdatere tabellen vha en forespørgsel og Replace, således at du ikke har problemet mere. Herefter kan du styre brugerens indtastninger f.eks. vha inputmasker.

Alternativt har jeg en gammel Replace-funktion, som jeg selv bruger i Office 97, når det er nødvendigt:
Public Function mieReplace(Streng As String, Find As String, Erstat As String) As String
    On Error Resume Next
    Dim Pos As Integer, findpos As Integer
    Dim tmpStr As String
    Pos = 1
    If Len(Streng) = 0 Then
      mieReplace = Streng
      Exit Function
    End If
    tmpStr = Streng
    Do
        findpos = InStr(Pos, tmpStr, Find)
        If findpos <> 0 Then
            tmpStr = Left(tmpStr, findpos - 1) & Erstat & Mid(tmpStr, findpos + Len(Find))
            Pos = Pos
        Else
            Pos = Pos + 1
        End If
    Loop Until Pos = Len(Streng)
    If Err Then
      mieReplace = Streng
    Else
      mieReplace = tmpStr
  End If
End Function

make a query in Access

SELECT *, replace(telefonnummer, ' ', '') AS Telf FROM YourTable

Then you can (hopefully) use

select telefonnummer where telf = '12345678'

Oops!


select telefonnummer from qry where telf = '12345678'

Det var netop dette jeg ville undgå ved at replace i sql-en....

THomas I think its because some VBA functions are not recognised by the SQL parser in ADO

Terry: Der er en ikke-defineret funktion "Replace" i udtrykket....

Måske kan det ikke lade sig gøre ?

?
You have just said that replace works in Access!

(select telefonnummer where replace(telefonnummer, ' ', '') = '12345678' )


You have to make the query in Access, then from your ADO program make a select on the query instead of the table

Thats just what I do, query 1 works inside access, but when called by query2 (and processed) via ado, it doesn't work.

so this works in Access?

SELECT *, replace(telefonnummer, ' ', '') AS Telf FROM YourTable

Have you SAVED this in Acess as a query? (qryThisIsMyQuery)

now in ADO you select from the query!

SELECT * FROM qryThisIsMyQuery

Virker ikke, jeg ændrer data i tabellen istedet.