Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text to Column & if (Access 2000)

    I have a table with number of records in access of 65000, so I can't work with Excel, except by splitting the data.

    How do I do a query to do a Data Text to Column?

    How do I do an if statement for the following problems?

    I have a table with records in and each set of records have a header that I would like to be repeated at each record. Example

    Pers Peter(Header)
    Oranges
    Apples
    Pers John
    Grapes
    Apples

    I would like the data to look as follows:

    Pers Peter Oranges
    Pers Peter Apples

    Pers John Grapes
    Pers John Apples

    Thanks for the help

    Mario

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

    Re: Text to Column & if (Access 2000)

    (1) Text To Column

    If you have a column with text you would like to split, create a query that displays just that column and a unique key field, for instance an AutoNumber field. Next, export the query to a text file. Then import the text file. The Import Text Wizard will provide the Text to Columns functionality of Excel. Use the unique key to combine the imported data with the original table.

    (2) Pers

    This can probably be done in a query, but I would write a routine that opens the table as a (DAO) recordset and loops through it. If the field starts with Pers, store the value in a variable (and delete the record?). If not, prepend the variable to the field. Something like (you may have to set a reference to DAO for this):

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strField As String
    Dim strPers As String
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tablename")
    Do While Not rst.EOF
    strField = rst.Fields("fieldname")
    If Left(strField, 4) = "Pers" Then
    strPers = strField
    ' Delete record if that's what you want
    rst.Delete
    Else
    rst.Edit
    rst.Fields("fieldname") = strPers & " " & strField
    rst.Update
    End If
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

Posting Permissions

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