Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete blank space in records... (2000 sr 1)

    Have:

    database: my_mdb.mdb
    table: my_table
    filed: field01

    is possible with vba for excel to delete in all recods in field01 the blank space and remain only one...

    similar

    old record = sal21__________sal21 (i dont know how many blank are present in old records)
    new record = sal21_sal21

    note: underscore are blank

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete blank space in records... (2000 sr 1)

    Why do you want to do this in Excel? It appears to be an Access problem.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete blank space in records... (2000 sr 1)

    148678 records...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete blank space in records... (2000 sr 1)

    Yes, I understand that you want to use Access for this, but why do you want to use Excel VBA to solve a problem in Access?

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete blank space in records... (2000 sr 1)

    Sorry Hans, i know a little only VBA for excel i not have experince with Access VB, but if is possible directly with vb for Access solution are welcome, sure!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete blank space in records... (2000 sr 1)

    OK, here is a snippet of code you can use from Excel.

    You must set a reference to the Microsoft ActiveX Data Objects 2.1 Object Library. Declare a variable cn of type ADODB.Connection and a variable rs of type ADODB.Recordset. You already know how to open a connection to an Access database - see your own older posts.

    ...
    rs.Open "my_table", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Do While Not rs.EOF
    If Not IsNull(rs!Field01) Then
    If InStr(rs!Field01, Space(2)) > 0 Then
    rs!Field01 = Application.WorksheetFunction.Trim(rs!Field01)
    rs.Update
    End If
    End If
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    ...

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete blank space in records... (2000 sr 1)

    wizard!!!!!!!!!!!!!!
    WORK PERFECT!!!!!

Posting Permissions

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