Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering for Carrage Return or Ascii 13

    I am running Access 97 on Windows NT and have a database that has entries in the Title field that have been entered with a carriage return in the field i.e.
    Mr
    r
    (It wasn't me, incase you wondering)
    I need to filter for these errors so I can extract the unique ID to another table. Does anyone have any ideas on how to do this.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Filtering for Carrage Return or Ascii 13

    Hi Phil,
    What you need is a query with 2 columns - 1 is the ID column that you want to return, the other is something like:
    HasCR: InStr([Title],chr(13)). On this second column put a criterion of >0 - this will return only those records with a carriage return in them.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    mark_riddick
    Guest

    Re: Filtering for Carrage Return or Ascii 13

    Hi
    You could try something like this:
    I have table called tblLocations with a field that can have carriage returns. Instr finds the first occurence of chr(13) if any giving TRUE.

    Hope it helps

    Public Sub FindCR()
    Dim dba As dao.Database
    Dim rs As dao.Recordset

    Set dba = CurrentDb()
    Set rs = dba.OpenRecordset("tblLocations")

    rs.MoveFirst
    Do While Not rs.EOF
    If InStr(1, rs.Fields("nameAddress"), Chr(13)) Then
    MsgBox rs.Fields("companyname") & " has a carriage return"
    Else
    MsgBox rs.Fields("location") & " does not have a carriage return"
    End If

    rs.MoveNext
    Loop

    End Sub

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering for Carrage Return or Ascii 13

    you could try this function in a make or update table query-
    use chr(13) for xOldStr and " " for xNewStr

    Function ReplaceAll(xString As String, xOldStr As String, xNewStr As String) As String

    ' Replace all instances of xOldStr with xNewStr in xString.

    Dim OutStr As String
    Dim LenOldStr As Integer
    Dim x As Integer

    x = InStr(xString, xOldStr)
    LenOldStr = Len(xOldStr)

    If x = 0 Then
    ReplaceAll = xString
    Exit Function
    End If

    OutStr = ""

    Do While x <> 0

    OutStr = OutStr & Left(xString, x - 1) & xNewStr
    xString = Mid(xString, x + LenOldStr)

    If Not IsNull(xString) Then
    x = InStr(xString, xOldStr)
    Else
    ReplaceAll = OutStr
    Exit Function
    End If

    Loop
    OutStr = OutStr & xString
    ReplaceAll = OutStr

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •