Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Unanswered: How to trim the enter key?

    Inside the access table, there are some data in the field with enter key,
    i have try to use "trim" to remove it, but fail, e.g.,

    select trim(col1) from table1;

    How to remove the "entry" ??

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Col1 = Trim(Col1)
    If Right$(Col1,1) = Chr$(12) Then Col1 = Left$(Col1, Len(Col1)-1)

    or try:

    If Right$(Col1,1) = vbKeyReturn Then Col1 = Left$(Col1, Len(Col1)-1)

  3. #3
    Join Date
    Dec 2003
    Posts
    10
    Excuse me, how to writ it in a single SQL statement??
    Thanks~~

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487

    Re: How to trim the enter key?

    Originally posted by mantat
    Inside the access table, there are some data in the field with enter key,
    i have try to use "trim" to remove it, but fail, e.g.,

    select trim(col1) from table1;

    How to remove the "entry" ??
    Oh boy....I'm sorry

    You should first ensure that the entry field does'nt put the carriage return into the table anymore. So you should find and correct that problem first. You can then run a cleanup routine to remove it from all your col1 records:

    I'll just take a quick poke at it here.....
    Code:
        Dim dbs As Database, rst As Recordset
        Dim i As Long, x As Long
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(table1)
        rst.MoveLast
        x = rst.RecordCount
        rst.MoveFirst
        Do Until i = x
           If Right$(rst!col1, 1) = Chr$(12) Then
              rst!col1 = Left$(rst!col1, Len(rst!col1) - 1)
           End If
           i = i + 1
           rst.MoveNext
        Loop
        rst.Close
        Set dbs = Nothing
    then use your generic:

    SELECT col1 FROM table1;

    to fill what I assume is a combo box.
    Last edited by CyberLynx; 12-05-03 at 02:14.

Posting Permissions

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