Hi dirk,
Thank you very much for qucik response.
I have to do one more conditional checking and add the whole row based on
that condition and do some changes in the added strt tm and end tm.
I will post my data so that it clears you a bit
PTyp Cl Sgn Log Dt Strt Tm End Tm Dur
PGR MOVIE 061001 000000 000700 000700
PRO MOVIE 061001 000700 000730 000030
PRO MOVIE 061001 001900 001930 000030
PRC MOVIE 061001 002000 002030 000030
PRO MOVIE 061001 002000 002035 000035
PRO MOVIE 061001 002100 002130 000030
PRO MOVIE 061001 002130 002160 000030
PSA MOVIE 061001 002200 002230 000030
PSA MOVIE 061001 002230 002260 000030
PRO MOVIE 061001 002500 002530 000030
PRO MOVIE 061001 002600 002635 000035
PRC MOVIE 061001 002630 002660 000030
PGR MOVIE 061001 003000 003000 000900
PRO MOVIE 061001 003000 003100 000100
PRO MOVIE 061001 003900 003935 000035
PRO MOVIE 061001 003930 003960 000030
PRO MOVIE 061001 004000 004030 000030
this is the data i have got after running your code which is based on the
condition of ptyp = "pgr"
PGR runs from 000000 to 000700 then PRO runs from 000700 to 000730 after
which 001900 comes.
But it shud be continuosly timed,so in this case a new row with type PGR
should be inserted with the same data for all the fields from previous PGR
between strt tm 000700 and 001900.
so the new row should look like below
PGR MOVIE 061001 000730 001900 001130
the last value 001130 is the time difference between 001900-000730.But all
the fields are in text which i can manipulate to time values using queries.
This shud be inserted as below
PGR MOVIE 061001 000000 000700 000700
PRO MOVIE 061001 000700 000730 000030
PGR MOVIE 061001 000730 001900 001130-- new row inserted
PRO MOVIE 061001 001900 001930 000030
This shud be done for all the records.My table approximately holds 6000
records
The concept is end tm of one row shuld be the next row strt tm.If there is a
break in the timing then the previously PGR row data need to added with strt
tm and end tm changed .
Please can you help upon this logic
Thanks in advance
Dirk Goldgar said:
Kowsy said:
I have the code that looks like below.Actually i have to update the
table iwth certain conditions.
If Ptyp field in the table is "PGR" then it should grab the next
row's [strt tm] value and put in the current row [end tm] value if
not it has to do some manipulations as show in the code.
I am not able to geb the next row [strt tm] and it is putting the
same [strt tm] in the current row
--------
Function Changeendtm()
Dim TableName As String
Dim NumRecords As Long
Dim i As Long
Dim DB As DAO.Database, TABLE As DAO.Recordset
TableName = "tblMovie"
Set Rst = CurrentDb.OpenRecordset("select * from [tblMovie]",
dbOpenDynaset) Set DB = CurrentDb
Set TABLE = DB.OpenRecordset(TableName, dbOpenTable)
NumRecords = Rst.RecordCount
Do Until TABLE.EOF
For i = 1 To NumRecords
If TABLE![ptyp] = "pgr" Then
TABLE.Edit
TABLE![END TM] = Format(CLng(TABLE![STRT TM]), "000000")
TABLE.Update
Else
TABLE.Edit
TABLE![END TM] = Format(CLng(TABLE![STRT TM]) + CLng(TABLE![DUR]),
"000000") TABLE.Update
End If
TABLE.MoveNext
Next i
Loop
Rst.Close
Set Rst = Nothing
DB.Close
Set DB = Nothing
End Function
Thanks in advance
There are a number of problems with that code, plus it isn't obvious
that you can count on the records in "tblMovie" to be returned in the
order you want. Are you assuming that the records in either recordset
will be returned in ascending sequence on [strt tm]? And what do you
want to do for the very last record?
You *may* get what you want using code something this:
'----- start of possible (UNTESTED) code -----
Function Changeendtm()
Dim rst As DAO.Recordset
Dim lngEndTime As Long
Set rst = CurrentDb.OpenRecordset( _
"SELECT * FROM tblMovie ORDER BY [strt tm]")
With rst
Do Until .EOF
' Compute end time for current record
If ![ptyp] = "pgr" Then
.MoveNext
If .EOF Then
lngEndTime = -1
Else
lngEndTime = CLng(![strt tm])
End If
.MovePrevious
Else
lngEndTime = CLng(![strt tm]) + CLng(!dur)
End If
' Update the record.
.Edit
If lngEndTime < 0 Then
' Don't know when this movie ends!
![end tm] = Null
Else
![end tm] = Format(lngEndTime, "000000")
End If
.Update
' Go on to the next movie.
.MoveNext
Loop
' All done. Close the recordset.
.Close
End With
Set rst = Nothing
End Function
'----- end of code -----
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)