Need Help with Coding!urgent

[Kowsy]

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

 

[Dirk Goldgar]

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 -----

 

[Kowsy]

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

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)

 

[Kowsy]

Can anyone please help on this problem.
I would really appreciate all your help

Thanks in advance

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

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)

 

[Dirk Goldgar]

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

Basic logic: have a set of variables to store the previous record's
field values. You'll need to decide what the initial values of these
variables should be, before you process the very first record. As you
loop through the recordset and finish processing each record (so any
updates to that record have already been made), save that record's field
values in your "previous-record" variables.

As you *start* processing each record, compare its start time to the end
time that you saved from the previous record. If you see that there's a
gap, use the recordset's AddNew method to create a new record based on
the saved values from the previous record and the calculated start time,
end time, and duration.

I can think of a possible issue with this approach: Since you're adding
records to the recordset as you go, they may show up out of sequence at
the end. You may find it simpler to use a separate append-only
recordset just to add the new records. Or you could use the same
recordset but stop when you encounter a record that is out of sequence.

 

[Kowsy]

Hi Dirk,

Can you help me with coding as i find it very very difficult in storing the
previous values .
How do i then compare to find if there is any break?
I could not get it
Imagine as if like a real television program scenario.A program starts and
then some commercial goes and then again program starts --this goes on
Same in my case there should be a continuity of programs and commercials for
the entire month and the time durations starts from 000000 to 235930.
it is very complex for me as i am not expert in coding
Now we are changing everything manually.
Please help me
I appreciate your help

Thanks in advance

 

[Dirk Goldgar]

Hi Dirk,

Can you help me with coding as i find it very very difficult in
storing the previous values .

You should realize you're asking a lot. These newsgroups aren't really
intended as a place to get people to write your code for you, though
demonstrative code snippets are standard fare.

How do i then compare to find if there is any break?
I could not get it
Imagine as if like a real television program scenario.A program
starts and then some commercial goes and then again program starts
--this goes on
Same in my case there should be a continuity of programs and
commercials for the entire month and the time durations starts from
000000 to 235930.
it is very complex for me as i am not expert in coding
Now we are changing everything manually.
Please help me

Well, since I'm such a softy ... here's a version that may or may not
work the way you want, but would be a place to start:

'----- start of code -----
Function FixMovieTimes()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rsAdd As DAO.Recordset

Dim lngEndTime As Long
Dim lngGap As Long

Dim ClSgn_Prev As Variant
Dim LogDt_Prev As Variant
Dim EndTm_Prev As Variant

Set db = CurrentDb

' Open a recordset on the existing records, sorted by start time.
Set rst = db.OpenRecordset( _
"SELECT * FROM tblMovie ORDER BY [Strt tm]")
' Open a separate, empty recordset to add records.
Set rsAdd = db.OpenRecordset( _
"SELECT * FROM tblMovie WHERE False")

With rst

If Not .EOF Then
' Set initial "previous" values.
EndTm_Prev = "000000"
ClSgn_Prev = ![Cl Sgn]
LogDt_Prev = ![Log Dt]
End If

Do Until .EOF

' Do we have a gap between this record and the previous one?
lngGap = CLng(![Strt tm]) - CLng(EndTm_Prev)
If lngGap > 0 Then
' Create a record to fill that gap.
With rsAdd
.AddNew
![PTyp] = "PGR"
![Log Dt] = LogDt_Prev
![Cl Sgn] = ClSgn_Prev
![Strt tm] = EndTm_Prev
![End tm] = rst![Strt tm]
![Dur] = Format(lngGap, "000000")
.Update
End With
End If

' 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

' Save this record's values as "previous" for the
' next record.
EndTm_Prev = ![End tm]
ClSgn_Prev = ![Cl Sgn]
LogDt_Prev = ![Log Dt]

' Go on to the next movie.
.MoveNext

Loop

' All done. Close the recordset.
.Close

End With

rsAdd.Close
Set rst = Nothing
Set rsAdd = Nothing
Set db = Nothing

End Function

'----- end of code -----

I notice that you have these two records in your sample, which seem to
conflict:

PRC MOVIE 061001 002000 002030 000030
PRO MOVIE 061001 002000 002035 000035

And there are some other overlaps, as well. I don't know what you would
want to do in these cases.

 

[SteveS]

Dirk,

In the sample data, there are several lines that look like this:

PTyp ClSgn LogDt StrtTm EndTm Dur
PRO MOVIE 061001 002130 002160 000030
PSA MOVIE 061001 002200 002230 000030

Notice the end time in the first line. 2160 is really 2200.

When I ran your code, it added lines like this:


PTyp Cl Sgn Log Dt Strt Tm End Tm Dur
PRO MOVIE 061001 002130 002160 000030
PGR MOVIE 061001 002160 002200 000040


If I understand correctly, the [Strt Tm] & [End Tm] fields are hhmmss. So
having a start time of 2160 and an end time of 2200 doesn't make a lot of
sense.

Well, since you're such a softy :-D, here is a function to run *before* your
code is run that will correct the end times.


'---beg code---
Function CheckEndTimes()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngEnd As Long

Set db = CurrentDb

' Open a recordset on the existing records, sorted by start time.
Set rst = db.OpenRecordset( _
"SELECT [End Tm] FROM tblMovie" & _
" WHERE (((Right([End Tm],2))=60)) ORDER BY [Strt Tm];")

With rst
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
lngEnd = CLng(![End tm]) + 40
.Edit
![End tm] = Format(lngEnd, "000000")
.Update
.MoveNext
Loop
End If
' All done. Close the recordset.
.Close

End With

Set rst = Nothing
Set db = Nothing

End Function
'---end code---



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Dirk,

Can you help me with coding as i find it very very difficult in
storing the previous values .

You should realize you're asking a lot. These newsgroups aren't really
intended as a place to get people to write your code for you, though
demonstrative code snippets are standard fare.

How do i then compare to find if there is any break?
I could not get it
Imagine as if like a real television program scenario.A program
starts and then some commercial goes and then again program starts
--this goes on
Same in my case there should be a continuity of programs and
commercials for the entire month and the time durations starts from
000000 to 235930.
it is very complex for me as i am not expert in coding
Now we are changing everything manually.
Please help me

Well, since I'm such a softy ... here's a version that may or may not
work the way you want, but would be a place to start:

'----- start of code -----
Function FixMovieTimes()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rsAdd As DAO.Recordset

Dim lngEndTime As Long
Dim lngGap As Long

Dim ClSgn_Prev As Variant
Dim LogDt_Prev As Variant
Dim EndTm_Prev As Variant

Set db = CurrentDb

' Open a recordset on the existing records, sorted by start time.
Set rst = db.OpenRecordset( _
"SELECT * FROM tblMovie ORDER BY [Strt tm]")
' Open a separate, empty recordset to add records.
Set rsAdd = db.OpenRecordset( _
"SELECT * FROM tblMovie WHERE False")

With rst

If Not .EOF Then
' Set initial "previous" values.
EndTm_Prev = "000000"
ClSgn_Prev = ![Cl Sgn]
LogDt_Prev = ![Log Dt]
End If

Do Until .EOF

' Do we have a gap between this record and the previous one?
lngGap = CLng(![Strt tm]) - CLng(EndTm_Prev)
If lngGap > 0 Then
' Create a record to fill that gap.
With rsAdd
.AddNew
![PTyp] = "PGR"
![Log Dt] = LogDt_Prev
![Cl Sgn] = ClSgn_Prev
![Strt tm] = EndTm_Prev
![End tm] = rst![Strt tm]
![Dur] = Format(lngGap, "000000")
.Update
End With
End If

' 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

' Save this record's values as "previous" for the
' next record.
EndTm_Prev = ![End tm]
ClSgn_Prev = ![Cl Sgn]
LogDt_Prev = ![Log Dt]

' Go on to the next movie.
.MoveNext

Loop

' All done. Close the recordset.
.Close

End With

rsAdd.Close
Set rst = Nothing
Set rsAdd = Nothing
Set db = Nothing

End Function

'----- end of code -----

I notice that you have these two records in your sample, which seem to
conflict:

PRC MOVIE 061001 002000 002030 000030
PRO MOVIE 061001 002000 002035 000035

And there are some other overlaps, as well. I don't know what you would
want to do in these cases.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

In the sample data, there are several lines that look like this:

PTyp ClSgn LogDt StrtTm EndTm Dur
PRO MOVIE 061001 002130 002160 000030
PSA MOVIE 061001 002200 002230 000030

Notice the end time in the first line. 2160 is really 2200.

When I ran your code, it added lines like this:


PTyp Cl Sgn Log Dt Strt Tm End Tm Dur
PRO MOVIE 061001 002130 002160 000030
PGR MOVIE 061001 002160 002200 000040


If I understand correctly, the [Strt Tm] & [End Tm] fields are
hhmmss. So having a start time of 2160 and an end time of 2200
doesn't make a lot of sense.

You're absolutely right, Steve. I wasn't even thinking about whether
the "time" fields might be actual representations of 24-hour clock time,
since the original code posted by Kowsy for calculating end times didn't
suggest it. I assumed they were running counts of minutes, but reading
back more carefully through the messages, I see that they must be clock
times.

Well, since you're such a softy :-D, here is a function to run
*before* your code is run that will correct the end times.

You're even more of a softy than I am, it appears.

'---beg code---
Function CheckEndTimes()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngEnd As Long

Set db = CurrentDb

' Open a recordset on the existing records, sorted by start time.
Set rst = db.OpenRecordset( _
"SELECT [End Tm] FROM tblMovie" & _
" WHERE (((Right([End Tm],2))=60)) ORDER BY [Strt Tm];")

With rst
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
lngEnd = CLng(![End tm]) + 40
.Edit
![End tm] = Format(lngEnd, "000000")
.Update
.MoveNext
Loop
End If
' All done. Close the recordset.
.Close

End With

Set rst = Nothing
Set db = Nothing

End Function
'---end code---

Couldn't the same result be achieved by an update query along these
lines:

UPDATE tblMovie
SET [End tm] = Format(CLng([End tm]) + 40, "000000")
WHERE [End tm] Like "*60";

?

Regardless, in the code I previously posted, where I calculate the end
time from the start time and duration like this:

lngEndTime = CLng(![Strt tm]) + CLng(!Dur)

.... the code should really be this:

lngEndTime = CLng(![Strt tm]) + CLng(!Dur)
If lngEndTime Mod 100 >= 60 Then
lngEndTime = lngEndTime + 40
End If

This whole thing would be a lot easier if the times were kept as scalar
minutes. And you don't really need both end time and duration, as
either implies the other. For that matter, you don't *really* either
end time *or* duration, since the set of start times alone implies all
the rest. But queries of that sort are a bit cumbersome.

Thanks for pointing out my error, Steve. Were you out of the newsgroups
for a while? I've seen you posting recently, but it seems to me there
was a while there that I didn't see you.

 

[SteveS]

Good morning Dirk, (well, morning in my time zone)

Couldn't the same result be achieved by an update query along these
lines:

UPDATE tblMovie
SET [End tm] = Format(CLng([End tm]) + 40, "000000")
WHERE [End tm] Like "*60";

?

Yes, but I'm more comfortable using VBA. I am trying to do more in SQL....
and now that I see the above UPDATE line, I have a very red face.... :O

This whole thing would be a lot easier if the times were kept as scalar
minutes. And you don't really need both end time and duration, as
either implies the other. For that matter, you don't *really* either
end time *or* duration, since the set of start times alone implies all
the rest. But queries of that sort are a bit cumbersome.

Yes, I wouldn't have a duration field. I thought the end time field might be
because several people were making entries for future spots and needed to
know when to start the new spot.

One other thought. Might need to have the [Log Dt] in the ORDER BY clause.
Kowsy said he had approximately 6000 records - at a minimum of 30 sec per
spot, that would be a maxium of 2,880 spots (records) per day. (6000 records
= little over 2 days).

Thanks for pointing out my error, Steve. Were you out of the newsgroups
for a while? I've seen you posting recently, but it seems to me there
was a while there that I didn't see you.

Life gets busy- sometimes you're the bug... sometimes you're the windshield.
Lately I've been the bug more often than not.

So not really out... lurking in the background.... reading posts to try and
increase my knowledge. (aka picking the MPVs brains and trying to learn how
to think like a programmer)

Sooo, a big thanks to you (you old softie), the other MVPs and to all the
others that contribute to this NG.

 

[Kowsy]

PTyp Cl Sgn Log Dt Strt Tm End Tm Dur Title/Advertiser
PGR MOVIE 061001 000000 000700 003000 MOVIE1
PRO MOVIE 061001 000700 000730 000030 PRO1
PGR MOVIE 061001 000730 001900 001170
PRO MOVIE 061001 001900 001930 000030 PRO2
PGR MOVIE 061001 001930 002000 000070
PRC MOVIE 061001 002000 002030 000030 PRC1
PRO MOVIE 061001 002030 002105 000035 PRO3
COM MOVIE 061001 002100 002130 000030 COM1
COM MOVIE 061001 002130 002200 000030 COM2
PSA MOVIE 061001 002200 002230 000030 PSA1
PSA MOVIE 061001 002230 002300 000030 PSA2
PGR MOVIE 061001 002300 002500 000200
PRO MOVIE 061001 002500 002530 000030 PRO1
PGR MOVIE 061001 002530 002600 000070
PRO MOVIE 061001 002600 002635 000035 PRO1
PRC MOVIE 061001 002630 002700 000030 PRC1
PRO MOVIE 061001 002700 002800 000100 PRO1
PGR MOVIE 061001 002800 003000 000200
PGR MOVIE 061001 003000 003600 003000 MOVIE2
PRO MOVIE 061001 003600 003635 000035 PRO1
PRO MOVIE 061001 003630 003730 000100 PRO2
PSA MOVIE 061001 003700 003730 000030 PRO3
PGR MOVIE 061001 003730 010000 004170
My concern is
1.when the dur is 000035 then the next row strt tm does not match to
previous row end tm which you can notice in the data given
2.There are so many additional fields in the table.All the field has to be
added and updated with the value equal to previous program "PGR".
In the data shown above those with "blank" in Title/advertiser are the added
"PGR" whose value should be previous "PGR" value.In this case first blank
shud be MOVIE1 and so on for the rest of the blanks and last blank should be
MOVIE2.
Normally PGR block is usally 30 min or 1 hr.So if we have commercials or
promos in between we have to bring a continuity of PGR and the rest of
commercials and promos as both are generated seperately
3.Duration is need to be calculated for PGR and it shud be 24 hr clock time

I am still expreimenting with so many ways.

If you can come up with a solution for the above said problem ,please let me
know

Thanks in advance

:

Good morning Dirk, (well, morning in my time zone)

Couldn't the same result be achieved by an update query along these
lines:

UPDATE tblMovie
SET [End tm] = Format(CLng([End tm]) + 40, "000000")
WHERE [End tm] Like "*60";

?

Yes, but I'm more comfortable using VBA. I am trying to do more in SQL....
and now that I see the above UPDATE line, I have a very red face.... :O

This whole thing would be a lot easier if the times were kept as scalar
minutes. And you don't really need both end time and duration, as
either implies the other. For that matter, you don't *really* either
end time *or* duration, since the set of start times alone implies all
the rest. But queries of that sort are a bit cumbersome.

Yes, I wouldn't have a duration field. I thought the end time field might be
because several people were making entries for future spots and needed to
know when to start the new spot.

One other thought. Might need to have the [Log Dt] in the ORDER BY clause.
Kowsy said he had approximately 6000 records - at a minimum of 30 sec per
spot, that would be a maxium of 2,880 spots (records) per day. (6000 records
= little over 2 days).

Thanks for pointing out my error, Steve. Were you out of the newsgroups
for a while? I've seen you posting recently, but it seems to me there
was a while there that I didn't see you.

Life gets busy- sometimes you're the bug... sometimes you're the windshield.
Lately I've been the bug more often than not.

So not really out... lurking in the background.... reading posts to try and
increase my knowledge. (aka picking the MPVs brains and trying to learn how
to think like a programmer)

Sooo, a big thanks to you (you old softie), the other MVPs and to all the
others that contribute to this NG.

 

[Kowsy]

Hi Dirk and Steve,

You guys are amazing.Such good hearted people helping everyone to solve the
problems.
I have tried out the code.As you guys mentioned the data itself is not pure
as all the fields are defined in text format which makes the whole process
complex.
I am running through various circumstances and experimenting the code which
you guys gave.
Thanks for all the help.
I will let you guys know about my end result soon as i am not 100% getting
on what i wanted

Thanks again

Dirk,

In the sample data, there are several lines that look like this:

PTyp ClSgn LogDt StrtTm EndTm Dur
PRO MOVIE 061001 002130 002160 000030
PSA MOVIE 061001 002200 002230 000030

Notice the end time in the first line. 2160 is really 2200.

When I ran your code, it added lines like this:


PTyp Cl Sgn Log Dt Strt Tm End Tm Dur
PRO MOVIE 061001 002130 002160 000030
PGR MOVIE 061001 002160 002200 000040


If I understand correctly, the [Strt Tm] & [End Tm] fields are
hhmmss. So having a start time of 2160 and an end time of 2200
doesn't make a lot of sense.

You're absolutely right, Steve. I wasn't even thinking about whether
the "time" fields might be actual representations of 24-hour clock time,
since the original code posted by Kowsy for calculating end times didn't
suggest it. I assumed they were running counts of minutes, but reading
back more carefully through the messages, I see that they must be clock
times.

Well, since you're such a softy :-D, here is a function to run
*before* your code is run that will correct the end times.

You're even more of a softy than I am, it appears.

'---beg code---
Function CheckEndTimes()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngEnd As Long

Set db = CurrentDb

' Open a recordset on the existing records, sorted by start time.
Set rst = db.OpenRecordset( _
"SELECT [End Tm] FROM tblMovie" & _
" WHERE (((Right([End Tm],2))=60)) ORDER BY [Strt Tm];")

With rst
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
lngEnd = CLng(![End tm]) + 40
.Edit
![End tm] = Format(lngEnd, "000000")
.Update
.MoveNext
Loop
End If
' All done. Close the recordset.
.Close

End With

Set rst = Nothing
Set db = Nothing

End Function
'---end code---

Couldn't the same result be achieved by an update query along these
lines:

UPDATE tblMovie
SET [End tm] = Format(CLng([End tm]) + 40, "000000")
WHERE [End tm] Like "*60";

?

Regardless, in the code I previously posted, where I calculate the end
time from the start time and duration like this:

lngEndTime = CLng(![Strt tm]) + CLng(!Dur)

.... the code should really be this:

lngEndTime = CLng(![Strt tm]) + CLng(!Dur)
If lngEndTime Mod 100 >= 60 Then
lngEndTime = lngEndTime + 40
End If

This whole thing would be a lot easier if the times were kept as scalar
minutes. And you don't really need both end time and duration, as
either implies the other. For that matter, you don't *really* either
end time *or* duration, since the set of start times alone implies all
the rest. But queries of that sort are a bit cumbersome.

Thanks for pointing out my error, Steve. Were you out of the newsgroups
for a while? I've seen you posting recently, but it seems to me there
was a while there that I didn't see you.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Kowsy]

can you please help me upon this

PTyp Cl Sgn Log Dt Strt Tm End Tm Dur Title/Advertiser
PGR MOVIE 061001 000000 000700 003000 MOVIE1
PRO MOVIE 061001 000700 000730 000030 PRO1
PGR MOVIE 061001 000730 001900 001170
PRO MOVIE 061001 001900 001930 000030 PRO2
PGR MOVIE 061001 001930 002000 000070
PRC MOVIE 061001 002000 002030 000030 PRC1
PRO MOVIE 061001 002030 002105 000035 PRO3
COM MOVIE 061001 002100 002130 000030 COM1
COM MOVIE 061001 002130 002200 000030 COM2
PSA MOVIE 061001 002200 002230 000030 PSA1
PSA MOVIE 061001 002230 002300 000030 PSA2
PGR MOVIE 061001 002300 002500 000200
PRO MOVIE 061001 002500 002530 000030 PRO1
PGR MOVIE 061001 002530 002600 000070
PRO MOVIE 061001 002600 002635 000035 PRO1
PRC MOVIE 061001 002630 002700 000030 PRC1
PRO MOVIE 061001 002700 002800 000100 PRO1
PGR MOVIE 061001 002800 003000 000200
PGR MOVIE 061001 003000 003600 003000 MOVIE2
PRO MOVIE 061001 003600 003635 000035 PRO1
PRO MOVIE 061001 003630 003730 000100 PRO2
PSA MOVIE 061001 003700 003730 000030 PRO3
PGR MOVIE 061001 003730 010000 004170
My concern is
1.when the dur is 000035 then the next row strt tm does not match to
previous row end tm which you can notice in the data given
2.There are so many additional fields in the table.All the field has to be
added and updated with the value equal to previous program "PGR".
In the data shown above those with "blank" in Title/advertiser are the added
"PGR" whose value should be previous "PGR" value.In this case first blank
shud be MOVIE1 and so on for the rest of the blanks and last blank should be
MOVIE2.
Normally PGR block is usally 30 min or 1 hr.So if we have commercials or
promos in between we have to bring a continuity of PGR and the rest of
commercials and promos as both are generated seperately
3.Duration is need to be calculated for PGR and it shud be 24 hr clock time

I am still expreimenting with so many ways.

If you can come up with a solution for the above said problem ,please let me
know

Thanks in advance

:

Good morning Dirk, (well, morning in my time zone)

Couldn't the same result be achieved by an update query along these
lines:

UPDATE tblMovie
SET [End tm] = Format(CLng([End tm]) + 40, "000000")
WHERE [End tm] Like "*60";

?

Yes, but I'm more comfortable using VBA. I am trying to do more in SQL....
and now that I see the above UPDATE line, I have a very red face.... :O

This whole thing would be a lot easier if the times were kept as scalar
minutes. And you don't really need both end time and duration, as
either implies the other. For that matter, you don't *really* either
end time *or* duration, since the set of start times alone implies all
the rest. But queries of that sort are a bit cumbersome.

Yes, I wouldn't have a duration field. I thought the end time field might be
because several people were making entries for future spots and needed to
know when to start the new spot.

One other thought. Might need to have the [Log Dt] in the ORDER BY clause.
Kowsy said he had approximately 6000 records - at a minimum of 30 sec per
spot, that would be a maxium of 2,880 spots (records) per day. (6000 records
= little over 2 days).

Thanks for pointing out my error, Steve. Were you out of the newsgroups
for a while? I've seen you posting recently, but it seems to me there
was a while there that I didn't see you.

Life gets busy- sometimes you're the bug... sometimes you're the windshield.
Lately I've been the bug more often than not.

So not really out... lurking in the background.... reading posts to try and
increase my knowledge. (aka picking the MPVs brains and trying to learn how
to think like a programmer)

Sooo, a big thanks to you (you old softie), the other MVPs and to all the
others that contribute to this NG.

 

[SteveS]

Given the code Dirk provided, you should be able modify it to do what you
need to do. Have you tried modifying the code?

I do see several errors you need to address in your data. (I am assuming
that the times in the fields [strt tm], [end tm] and [dur] are in "hhmmss"
format.)

1) As I noted before, you need to ensure that you have no invalid times; a
time of "002160" is an example. This should be "002200". These can be fixed
using the update query provided by Dirk.

BTW, you reall y should NOT be storing the duration. (Maybe you have a valid
reason for doing so.)

Your data shows why you shouldn't store the duration. . Look at the line
where Title/advertiser is "Movie1". If you compare the start time and the end
time, the duration should be 7 minutes, yet you have 30 minutes in the "Dur"
field. What should it be? 7 minutes or 30 minutes?

If you know the start time and the end time you can calculate the duration.

Which brings us to your example where the duration is "000035" (the
Title/advertiser is "PRO3"). What do you want to happen? Should the end time
be reduced by 5 seconds and the dur changed to 000030? Or should the next
line start time be increased by 5 seconds? If the start time, "002100", is
changed to "002105", what should happen to the duration? Increase the
duration or change the end time? If you change the end time, then the next
line start time is wrong. What now? Change the ent time or the duration? And
continue this for the rest of the data.

This is why storing the results of a calculation is a bad ides.


2) No one can write code if they don't know the names of all the fields. You
need to do this part.


3) I am assuming you mean for the new lines that should be added if there
are gaps in the end times / start times. See #1 above.


If this is *really* urgent, I would suggest finding and paying a programmer.
If you have time to do it yourself, try writing the code and if you have
problems, post a question (like you have done).


I hate to do this, but I have to get back to my job......


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

can you please help me upon this

PTyp Cl Sgn Log Dt Strt Tm End Tm Dur Title/Advertiser
PGR MOVIE 061001 000000 000700 003000 MOVIE1
PRO MOVIE 061001 000700 000730 000030 PRO1
PGR MOVIE 061001 000730 001900 001170
PRO MOVIE 061001 001900 001930 000030 PRO2
PGR MOVIE 061001 001930 002000 000070
PRC MOVIE 061001 002000 002030 000030 PRC1
PRO MOVIE 061001 002030 002105 000035 PRO3
COM MOVIE 061001 002100 002130 000030 COM1
COM MOVIE 061001 002130 002200 000030 COM2
PSA MOVIE 061001 002200 002230 000030 PSA1
PSA MOVIE 061001 002230 002300 000030 PSA2
PGR MOVIE 061001 002300 002500 000200
PRO MOVIE 061001 002500 002530 000030 PRO1
PGR MOVIE 061001 002530 002600 000070
PRO MOVIE 061001 002600 002635 000035 PRO1
PRC MOVIE 061001 002630 002700 000030 PRC1
PRO MOVIE 061001 002700 002800 000100 PRO1
PGR MOVIE 061001 002800 003000 000200
PGR MOVIE 061001 003000 003600 003000 MOVIE2
PRO MOVIE 061001 003600 003635 000035 PRO1
PRO MOVIE 061001 003630 003730 000100 PRO2
PSA MOVIE 061001 003700 003730 000030 PRO3
PGR MOVIE 061001 003730 010000 004170
My concern is
1.when the dur is 000035 then the next row strt tm does not match to
previous row end tm which you can notice in the data given
2.There are so many additional fields in the table.All the field has to be
added and updated with the value equal to previous program "PGR".
In the data shown above those with "blank" in Title/advertiser are the added
"PGR" whose value should be previous "PGR" value.In this case first blank
shud be MOVIE1 and so on for the rest of the blanks and last blank should be
MOVIE2.
Normally PGR block is usally 30 min or 1 hr.So if we have commercials or
promos in between we have to bring a continuity of PGR and the rest of
commercials and promos as both are generated seperately
3.Duration is need to be calculated for PGR and it shud be 24 hr clock time

I am still expreimenting with so many ways.

If you can come up with a solution for the above said problem ,please let me
know

Thanks in advance

:

Good morning Dirk, (well, morning in my time zone)

Couldn't the same result be achieved by an update query along these
lines:

UPDATE tblMovie
SET [End tm] = Format(CLng([End tm]) + 40, "000000")
WHERE [End tm] Like "*60";

?

Yes, but I'm more comfortable using VBA. I am trying to do more in SQL....
and now that I see the above UPDATE line, I have a very red face.... :O


This whole thing would be a lot easier if the times were kept as scalar
minutes. And you don't really need both end time and duration, as
either implies the other. For that matter, you don't *really* either
end time *or* duration, since the set of start times alone implies all
the rest. But queries of that sort are a bit cumbersome.

Yes, I wouldn't have a duration field. I thought the end time field might be
because several people were making entries for future spots and needed to
know when to start the new spot.

One other thought. Might need to have the [Log Dt] in the ORDER BY clause.
Kowsy said he had approximately 6000 records - at a minimum of 30 sec per
spot, that would be a maxium of 2,880 spots (records) per day. (6000 records
= little over 2 days).


Thanks for pointing out my error, Steve. Were you out of the newsgroups
for a while? I've seen you posting recently, but it seems to me there
was a while there that I didn't see you.

Life gets busy- sometimes you're the bug... sometimes you're the windshield.
Lately I've been the bug more often than not.

So not really out... lurking in the background.... reading posts to try and
increase my knowledge. (aka picking the MPVs brains and trying to learn how
to think like a programmer)

Sooo, a big thanks to you (you old softie), the other MVPs and to all the
others that contribute to this NG.