Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / March 2007

Tip: Looking for answers? Try searching our database.

Need Help with Coding!urgent

Thread view: 
Enable EMail Alerts  Start New Thread
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.