MS Access Forum / Modules / DAO / VBA / March 2007
Need Help with Coding!urgent
|
|
Thread rating:  |
Kowsy - 05 Mar 2007 22:03 GMT 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 - 05 Mar 2007 23:20 GMT > I have the code that looks like below.Actually i have to update the > table iwth certain conditions. [quoted text clipped - 37 lines] > > 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 -----
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
Kowsy - 06 Mar 2007 20:12 GMT 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. [quoted text clipped - 97 lines] > End Function > '----- end of code ----- Kowsy - 07 Mar 2007 14:36 GMT Can anyone please help on this problem. I would really appreciate all your help
Thanks in advance
> Hi dirk, > [quoted text clipped - 147 lines] > > End Function > > '----- end of code ----- Dirk Goldgar - 07 Mar 2007 16:15 GMT > Hi dirk, > [quoted text clipped - 44 lines] > 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.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
Kowsy - 07 Mar 2007 17:01 GMT 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
> > Hi dirk, > > [quoted text clipped - 63 lines] > 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. Dirk Goldgar - 07 Mar 2007 19:31 GMT > 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 [quoted text clipped - 7 lines] > 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.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
SteveS - 08 Mar 2007 02:02 GMT 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
 Signature Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.)
> > Hi Dirk, > > [quoted text clipped - 127 lines] > And there are some other overlaps, as well. I don't know what you would > want to do in these cases. Dirk Goldgar - 08 Mar 2007 06:52 GMT > Dirk, > [quoted text clipped - 15 lines] > 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() [quoted text clipped - 30 lines] > 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.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
SteveS - 08 Mar 2007 19:41 GMT Good morning Dirk, (well, morning in my time zone)
> Couldn't the same result be achieved by an update query along these > lines: [quoted text clipped - 4 lines] > > ? 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.
 Signature Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.)
Kowsy - 08 Mar 2007 20:09 GMT 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) > [quoted text clipped - 38 lines] > Sooo, a big thanks to you (you old softie), the other MVPs and to all the > others that contribute to this NG. Kowsy - 08 Mar 2007 20:14 GMT 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 [quoted text clipped - 83 lines] > > Sooo, a big thanks to you (you old softie), the other MVPs and to all the > > others that contribute to this NG. SteveS - 09 Mar 2007 01:09 GMT 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
 Signature Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.)
> can you please help me upon this > [quoted text clipped - 85 lines] > > > Sooo, a big thanks to you (you old softie), the other MVPs and to all the > > > others that contribute to this NG. Kowsy - 08 Mar 2007 20:13 GMT 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, > > [quoted text clipped - 93 lines] > 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.
|
|
|