Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Auckland
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Import (Office XP + Service Pack2)

    Hi,

    I have imported some data and i have a square box between two sets of data in the same field. it looks like: john (now the square box) smith what i need to do is get rid of the square box. is there a way to doi this using a query? i have 900 odd records to do and ideally don't want to have to do this manually as it will be an ongoing weekly thing! Thanks for any help!

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

    Re: Data Import (Office XP + Service Pack2)

    First, you need to find out what the square box is. Create a query based on your table. Add the field that contains the square boxes. In the next column, enter this expression:

    Asc(Mid([NameOfField],5,1))

    where NameOfField must be replaced with the name of the field with the square boxes. Switch to datasheet view. Locate a record in which the square box is in the 5th position (as in John

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    The little square could be from a tab character.
    Try an update query and use an expression like :
    Replace([YourField],Chr(9)," ")
    to replace the tab character by a space.
    Francois

  4. #4
    New Lounger
    Join Date
    Oct 2004
    Location
    Auckland
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    fantastic - thanks guys. the tab character was number 10 and i have now successfully eliminated them. this now leads onto my next question. the remaining data is as follows: ASPIRING MEDICAL CENTRE 28 Dungarbon Street, Wanaka, N/A, , NZ this is just an example. the data is in one stream within a field and i need to split it into the follwoing fields: name, address1. address2, address3, address4. what seems to complicate it is that there in some cases is a , between the data and in other cases a space. the space is most recently from the box removal query. i could i guess reimport the data and run the box removal query and replace the space with a , -what do you think.

    i really appreciate the help!

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

    Re: Data Import (Office XP + Service Pack2)

    Since the data can also contain 'legitimate' spaces, such as between Dungarbon and Street, it's probably best to reimport the data and replace the Chr(10) boxes with a comma. After you have done this:
    Add the fields you need (name, address1 etc.) to the table.
    Create a custom function in a standard module:

    Public Function Part(strSomething, intIndex)
    Part = Split(strSomething, ",")(intIndex - 1)
    End Function

    Create an update query based on the table.
    Add the new fields, and enter expressions like the following in the 'Update to' line:

    Part([NameOfField],1)

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    If all your data is separated by a , then you could use this code to split the data into the different fields.
    This code use DAO, so you should set a reference to Microsoft DAO xx Object Library.
    This code suppose also that the complete address is in the first field and the new address fields are the next ones.
    <pre>Sub SplitAddres()
    Dim arAddress() As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim x As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("YourTable", dbOpenDynaset)
    rst.MoveFirst
    Do While Not rst.EOF
    arAddress() = Split(rst!YourCompleteField, Chr(44))
    rst.Edit
    For x = 0 To UBound(arAddress)
    rst.Fields(x + 1) = arAddress(x)
    Next x
    rst.Update
    rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
    End Sub</pre>

    Francois

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    Once again you beat me, and with a simpler solution. Should I <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23> ? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Francois

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

    Re: Data Import (Office XP + Service Pack2)

    Your solution is useful too - it shows that there can be totally different ways to approach the same problem. Moreover, Loungers using Access 97 can't use the Split function, it was introduced in Office 2000.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    My code use also the split function and will not run in 97.
    Francois

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

    Re: Data Import (Office XP + Service Pack2)

    Oops <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  11. #11
    New Lounger
    Join Date
    Oct 2004
    Location
    Auckland
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    wow, clearly a lot to learn about this stuff! I have started using Han's code and have successfully moved the data into the correct fields now -thank you both so much. I don't really understand the reference in Francois's post about seting a reference to Microsoft DAO xx Object Library, how do you do this? is this then actioned through a query? sorry to sound so vauge!

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    Open the Visual Basic Editor by opening a module or pressing ALT-F11.
    In the menu, select Tools / References and scroll down to Microsoft DAO xx Object Library.
    Check the checkbox beside it and close the window.
    That's all.
    Francois

  13. #13
    New Lounger
    Join Date
    Oct 2004
    Location
    Auckland
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Import (Office XP + Service Pack2)

    Thank You!!!

Posting Permissions

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