Programmatically copy field to null values in a table

[zionman4]

Hi Everyone,
I currently bring in a text file into Access. After deleting all unnecesary data I am left with the following table:
Description Totals GroupSet CycleDate
Cycle Date 05/19/2006
LOB010 010
OFFSET $500
HISTORY $100
TOTAL $600
Cycle Date 05/22/2006
LOB020 020
OFFSET $400
HISTORY $400
TOTAL $800
*** END OF REPORT ***

I need a way to copy the GroupSet number and CycleDate as they change so the resulting table is as follows:

Description Totals GroupSet CycleDate
Cycle Date 010 05/19/2006
LOB010 010 05/19/2006
OFFSET $500 010 05/19/2006
HISTORY $100 010 05/19/2006
TOTAL $600 010 05/19/2006
Cycle Date 010 05/22/2006
LOB020 020 05/22/2006
OFFSET $400 020 05/22/2006
HISTORY $400 020 05/22/2006
TOTAL $800 020 05/22/2006
*** END OF REPORT ***

I currently use the following program in Excel to do this but don't know how to convert it into Access VBA.

TheTop:

TheCells = ActiveCell.Formula
Selection.NumberFormat = "General"
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
If ActiveCell.Value = "" Then
ActiveCell.FormulaR1C1 = "=If(RC[-2] = ""*** END OF REPORT ***"", ""END"", """")"
If ActiveCell.Value = "END" Then
GoTo Top
Else
Selection.NumberFormat = "@"
ActiveCell.Formula = TheCells
End If
Else
GoTo TheTop
End If

GoTo TheTop

Top:

Columns("E:E").Select
Selection.NumberFormat = "mm/dd/yyyy"
Range("E2").Select


Thanks!

 

[CautionMP]

zionman4,
I used to do a lot of this and usually took one of two approches:[ol]
[li]Transform the file, then import into the database.[/li]
[li]Transfrom the file as I import ot the database.[/li][/ol]
Either route uses a routine similar to below, the main difference is that option 1 creates a second file that can be linked to or imported using [tt]TransferText()[/tt], while option 2 creates the records as the file is read (using either a DAO or ADO recodset object).

Each method has it's benefits and drawbacks so I would recomend going with what your most comfortable with. For demonstration purposes I just output the normalized data to the immediate window, if you have a preference on how you want to process the data just post back.

[green]'Make the text comparisons NOT case dependent[/green]
Option Compare Text

[navy]Sub [/navy] PrepForImport()
[green]'The Left() & Mid() Functions below are based on this map[/green]
[green]'Description     Totals    GroupSet   CycleDate[/green]
[green]'123456789,123456789,123456789,123456789,123456789,123456789[/green]

[green]'Next three lines For handling input file[/green]
[green]'Change the text in bold to match the path & name of your text file[/green]
Const cFileName [navy]As String[/navy] = [b]"C:\FileName.txt"[/b]
[navy]Dim[/navy] intFile [navy]As Integer[/navy]
[navy]Dim[/navy] strCurrentLine [navy]As String[/navy]

[green]'Next two lines For output[/green]
[navy]Dim[/navy] strDescription [navy]As String[/navy], strTotals [navy]As String[/navy]
[navy]Dim[/navy] strGroupSet [navy]As String[/navy], strCycle[navy]Date As String[/navy]

[green]'Open the file[/green]
intFile = FreeFile
Open cFileName [navy]For[/navy] Input [navy]As[/navy] #intFile

[green]'Process the input file one line at a time[/green]
Do
  Line Input #intFile, strCurrentLine
  [green]'Test the line and decide what To Do, used both[/green]
  [green]'Like and InStr() For demonstration, either will work[/green]
  [navy]If[/navy] strCurrentLine Like "Description*" [navy]Then[/navy]
    [green]'Header line, Do Nothing[/green]
  Else[navy]If[/navy] InStr(strCurrentLine, "[navy]End[/navy] OF REPORT") [navy]Then[/navy]
    [green]'Last line in file, Do Nothing[/green]
  Else[navy]If[/navy] strCurrentLine Like "CycleDate*" [navy]Then[/navy]
    strCycle[navy]Date[/navy] = Trim(Mid(strCurrentLine, 35))
  Else[navy]If[/navy] strCurrentLine Like "Description*" [navy]Then[/navy]
    strGroupSet = Trim(Mid(strCurrentLine, 27, 8))
  Else
    strDescription = Trim(Left(strCurrentLine, 16))
    strTotals = Trim(Mid(strCurrentLine, 17, 9))
    [green]'*** Should be a complete record, Do something[/green]
    [green]'*** with the data here[/green]
    Debug.Print strDescription, strTotals, strGroupSet, strCycleDate
  [navy]End If[/navy]
[navy]Loop[/navy] Until EOF(intFile)

[green]'Close the input file[/green]
Close #intFile
[navy]End Sub [/navy]

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[zionman4]

Thanks CMP but I already have the data in an Access table so I don't need to re-import it again. All I need to do is fill in the blanks...

Zionman4

 

[Golom]

Unfortunately this relies on some specific ordering of the rows in the table and Access doesn't guarantee any such ordering without an ORDER BY clause. If you can pull this table into a recordset (and get the order you want) then you can do something like

Dim GroupSet As String
Do Until rs.EOF
   If GroupSet <> rs![GroupSet] And Not IsNull(rs![GroupSet]) Then
      GroupSet = rs![GroupSet]
   End If

   If IsNull(rs![GroupSet]) and Len(GroupSet) > 0 Then
      rs.Edit
      rs![GroupSet] = GroupSet
      rs.Update
   End If

   rs.MoveNext
Loop

and a similar sort of thing for the CycleDate column.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[CautionMP]

zionman4,
Does your Access table have field that you can sort by that guarantees the records will be sorted in the same order they were imported?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[zionman4]

I will give this a try. It's a long weekend and I have to go. I will post next week.

Thanks for the help!!!

Zionman4

 

[zionman4]

I created an AutoNumber field to preserve the order and then I implemented Golom's code. Thank you both for your help!
Z4