Fiendish Update Query!!!!???? 1

[proximity]

Hi Folks,

Here's an interesting one! I am attempting to process a raw text file, Example 1 below. Field1 contains a date value as well as other useful stuff. The records that immediately follow the date value in Field1 all need to have that date assigned to them in the Date field, with this Date field changing when the date changes (see Example2)

Example 1:
Field1 Field2 Field3 Field4 Date
25-May-05 00:05
FO TPGA12 000/0000 HH12 0
FP TPGA12 000/0000 HH12 0
FO TPGA12 000/0000 HH12 0
26-May-05
AJ UNSINP 340/9158 0
AJ UNSINP 875/7049 0
AJ UNSINP 875/7166 0
27-May-05
AJ UNSINP 340/9158 0
AJ UNSINP 875/7049 0
AJ UNSINP 875/7166 0

Example 2 (what I want to achieve!)
Field1 Field2 Field3 Field4 Date
25-May-05
FO TPGA12 000/0000 HH12 0 25-May-05
FP TPGA12 000/0000 HH12 0 25-May-05
FO TPGA12 000/0000 HH12 0 25-May-05
26-May-05
AJ UNSINP 340/9158 0 26-May-05
AJ UNSINP 875/7049 0 26-May-05
AJ UNSINP 875/7166 0 26-May-05
27-May-05
AJ UNSINP 340/9158 0 27-May-05
AJ UNSINP 875/7049 0 27-May-05
AJ UNSINP 875/7166 0 27-May-05
Is this possible with an Update Query? I have tried all manner of things all to no avail!

--
Steven

 

[Crowley16]

I don't think you can do this with a pure query, sql doesn't work like that...

you would need to open this into a recordset, and step through it to process each row...

--------------------
Procrastinate Now!

 

[BobStubbs]

You could also do this with some 'pure' Visual Basic code. In VB you have the necessary commands to:

Open a sequential text file;
Read the file one line at a time;
See if the line contains a date;
If it does, store the date in a string variable;
If it doesn't, add the previously stored date to the end of the line;
Write the results back to an output text file.

Example:

Dim InFile as Integer
Dim OutFile as Integer
Dim strOneLine as String
Dim strDate as String

InFile = FreeFile
Open "YourFileName.txt" for Input as #InFile
OutFile = FreeFile
Open "OutputFileName.txt" for Output as #OutFile

Do
    Line Input #InFile, strOneLine
    If IsDate(strOneLine) then 
        strDate = strOneLine
        Print #OutFile, strOneLine
    Else
        Print #OutFile, strOneLine, strDate
    End If
Loop until EOF(InFile)

Close #InFile
Close #OutFile

MsgBox "Finished", vbExclamation

You could (for example) write this into the On_Click event of a command button on an Access form, then just run it by clicking the button.

Bob Stubbs

 

[proximity]

Thank you Bob Stubbs!!!

Your suggestion worked flawlessy and gave me exactly what I was looking for! Have a well-earned star!

Thanks again,

--
Steven