Results 1 to 6 of 6
  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

    Adding ID Field (Access 2000)

    I have a table with Telephone call detail by specific extension. I'm importing it from a text file into Access, The format is as follows:

    Ext 1578
    05/02/02 15m17sec 05785412 $1.54
    05/02/02 16m17sec 05752521 $2.54
    05/02/02 1m17sec 05799912 $3.54

    Ext 1579
    06/02/02 15m17sec 05785412 $1.54
    07/02/02 16m17sec 05752521 $2.54
    08/02/02 1m17sec 05799912 $3.54

    Now I would like to repeat the Extension in a seperate field to show against all record underneath that extension. Example

    Ext 1578 05/02/02 15m17sec 05785412 $1.54
    Ext 1578 05/02/02 16m17sec 05752521 $2.54
    Ext 1578 05/02/02 1m17sec 05799912 $3.54

    Ext 1579 06/02/02 15m17sec 05785412 $1.54
    Ext 1579 07/02/02 16m17sec 05752521 $2.54
    Ext 1579 08/02/02 1m17sec 05799912 $3.54

    Now I know that with an if statement it will be a piece of cake in Excel but how do you do the append query in Access

    Thanks

    Mario

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

    Re: Adding ID Field (Access 2000)

    I don't see a way to do this with an append query. I think you'll have to write a VBA procedure that loops through the records of the imported table (or opens the text file directly, and loops through the lines), assembles the records one by one and appends them to the target table.

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

    Re: Adding ID Field (Access 2000)

    If you have a moment can you give me a few pointers/code examples on the vba code

    Thanks

    Mario

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

    Re: Adding ID Field (Access 2000)

    Here is some code - you will have to adapt it for your situation.

    Assumptions:
    <UL><LI>The text file has been imported as tblOld (replace this by appropriate name). It has 4 fields.
    <LI>An empty table tblNew (replace this by the name you want) has already been created, with 5 fields (one extra for the Extension).
    <LI>Since the code uses DAO, you must set a reference to the Microsoft DAO n.n Object Library.
    <LI>Depending on how you want to store your data, you may have to add some type conversion, for example
    .Fields(1) = DateValue(rstOld.Fields(0)) ' Date[/list]Sub FillNew()
    Dim dbs As DAO.Database
    Dim rstOld As DAO.Recordset
    Dim rstNew As DAO.Recordset
    Dim strExtension As String

    On Error GoTo Err_Handler

    ' Open recordsets
    Set dbs = CurrentDb
    Set rstOld = dbs.OpenRecordset("tblOld")
    Set rstNew = dbs.OpenRecordset("tblNew")

    ' Loop through imported data
    Do While Not rstOld.EOF
    ' Skip empty records
    If Not IsNull(rstOld.Fields(0)) Then
    If IsNull(rstOld.Fields(1)) Then
    ' If second field is empty, first field is extension
    strExtension = rstOld.Fields(0)
    Else
    ' Otherwise, we have phone call data - add to target
    With rstNew
    .AddNew
    .Fields(0) = strExtension ' Extension
    .Fields(1) = rstOld.Fields(0) ' Date
    .Fields(2) = rstOld.Fields(1) ' Duration
    .Fields(3) = rstOld.Fields(2) ' Phone number
    .Fields(4) = rstOld.Fields(3) ' Cost
    .Update
    End With
    End If
    End If
    rstOld.MoveNext
    Loop

    Exit_Handler:
    ' Clean up
    On Error Resume Next
    rstNew.Close
    Set rstNew = Nothing
    rstOld.Close
    Set rstOld = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    ' Display error message
    MsgBox Err.Description, vbExclamation
    ' Always clean up after you!
    Resume Exit_Handler
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding ID Field (Access 2000)

    Thanks it works great, but now I want to do the upload from the original text file. How will the code change to upload it from a text file(rstOld)

    Thanks

    Mario

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

    Re: Adding ID Field (Access 2000)

    An easy way out (for me) is to inport the text file into a temporary table using DoCmd.TransferText. You can then process the temp table and delete it afterwards.

    If you want to work directly with the text file, use something like this:

    Dim strLine As String
    Open "C:Phone.txt" For Input As #1
    Do While Not EOF(1) ' Loop until end of file.
    Input #1, strLine ' Read data into string variable.
    ' Insert code to process string here.
    ' You must find out whether there is anything in it, whether it contains an extension or call data, etc.
    Loop
    Close #1

    You will have to parse the string variable yourself; I have no time now to do that for 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
  •