Update Query Help

[GT]

Hi, all, if this question has been posted before, my apologies. I wasn't
sure how to search for this particular problem. So here goes. I have a
table created from a series of Windows directory listings. Currently, the
original data column contains the raw data of the directories. At the start
of each listing within the column is the machine name that the listing
originated from. I've added a second empty column to into which I want to
put the related machine name. The table currently looks like this:

Datacolumn MachineName
-------------- ----------------
Machine1
infostring
infostring
infostring
Machine2
infostring
infostring
Machine3
infostring
infostring
etc

I want to associate the machine name with its records that follow it so that
the end result looks as follows:
Datacolumn MachineName
-------------- -------------
Machine1 Machine1.log
infostring Machine1.log
infostring Machine1.log
infostring Machine1.log
Machine2 Machine2.log
infostring Machine2.log
Machine3 Machine3.log
infostring Machine3.log
etc

An suggestions for an update query to generate my desired result? For now,
I'm not concerned about the rows where the data string and the machine name
are the same. I can eliminate those easily enough. Thanks in advance for
any responses.

 

[Ofer]

I can't help you with a query, but I can help you with a function, Assuming
that the recordset come in the right order.

Function UpdateField()
dim MyDB as database, MyRec as recordset, MyCount as integer
set MyDB=codedb
Set MyRec=mydb.openrecordset("Select * From MyTable")
While not MyRec.eof
If left(MyRec!Datacolumn,7)="Machine" then
MyCount = cint(Right(MyRec!Datacolumn,len(MyRec!Datacolumn)-7))
End If
MyRec.Update
MyRec!MachineName = "Machine" & MyCount & ".Log"
MyRec.Edit
MyRec.movenext
Wend
end function

I hope it will work

 

[GT]

Thanks, my first thought was that I might have to code it rather than do a
straight query. I'll give this a shot.

 

[GT]

Ofer,

One followup question, in the line:
set MyDB=codedb
Do I leave the 'codedb' or do I substitute something for it?

 

[Ofer]

leave codedb, it will set for your current DB

Ofer,

One followup question, in the line:
set MyDB=codedb
Do I leave the 'codedb' or do I substitute something for it?

 

[GT]

Ofer, I'm running Access 2002. Even though the Dim x As Database option
shows up in my Access VB help, when I try to run the script, the debugger
tells me Database is not a defined type. Thoughts?

 

[Ofer]

Do you have reference to Access 11.00 Library?
Have you open recordset in your mdb before?

To check refernce open your debug, choose tools, refernce.
if you dont have add it, it call MSACC.OLB

 

[GT]

I'm accessing the Access 10.0 Library and no, I haven't done an open
recordset before. Unfortunately, due our company workstation policies, I
can't add the 11.0 library as that would make my workstation non-standard.
I'll check the help and knowledgebase for opening recordsets with the 10.0
Library. Thanks for your help.