RE: Update Query - Consecutive Numbers

Tech-Archive recommends: Fix windows errors by optimizing your registry

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES



"Dale Fye" wrote:

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"James Frater" wrote:

Morning Gang,

I look after events and have a do...loop piece of code to create block
bookings (please see at the bottom). As part of this do...loop it gives each
individual event in the block a consecutive "episode" number. So, for one of
my rugby teams who book the rugby pitch in block of 7 weeks they get 8
consecutive episodes:

Episode Date
1 18/01/2009
2 25/01/2009
3 01/02/2009
4 08/02/2009
5 15/02/2009
6 22/02/2009
7 01/03/2009
8 08/03/2009

However, people often cancel an indivdual event or, more often than not,
request additional event. So the Rugby team has their block of 8 events, but
add in a few more events.

Episode Date
1 18/01/2009
2 25/01/2009
3 01/02/2009
04/02/2009
4 08/02/2009
5 15/02/2009
16/02/2009
19/02/2009
6 22/02/2009
7 01/03/2009
8 08/03/2009

So, what do I need to do to update the episode numbers to run from 1-11.
I've tried an UPDATE SQL piece of code (please see below) but can't get it to
work, any ideas?

Regards

JAMES


UPDATE SQL code
===============
Private Sub btnEpisodeRefresh_Click()

Do
intcounter = 0
intcounter = intcounter + 1

DoCmd.RunSQL "UPDATE tblEvent" & _
"SET [episode] = intcounter " & _
"WHERE [block] = " & Me.blockID
Exit Do
Loop
End Sub




Do...loop code
=============
Set dbs = CurrentDb()
Set rstprogramme = dbs.OpenRecordset("tblEvent", dbOpenDynaset)

intcounter = 0
intstartdate = DateAdd("d", -7, Me![startdate])

Do While intcounter <= Me![TotalWeeks]

intcounter = intcounter + 1
intstartdate = DateAdd("d", 7, intstartdate)

rstEvent.AddNew
rstEvent!Episode = intcounter
rstEvent!EventName = Me![EventName]
rstEvent!EventStart = intstartdate
rstEvent!SportType = Me![sporttypeid]
rstEvent!EventLoc = Me![LocationName]
rstEvent!EventBlock = Me![BlockID]
rstEvent!EventOn = Me![StartTime]
rstEvent!EventOff = Me![EndTime]
rstEvent.Update

If intcounter = Me![TotalWeeks] + 1 Then
Exit Do
End If
Loop

.

Relevant Pages

  • RE: Update Query - Consecutive Numbers
    ... individual event in the block a consecutive "episode" number. ... I've tried an UPDATE SQL piece of code but can't get it to ... intcounter = intcounter + 1 ... intstartdate = DateAdd ...
    (microsoft.public.access.queries)
  • Update Query - Consecutive Numbers
    ... individual event in the block a consecutive "episode" number. ... I've tried an UPDATE SQL piece of code but can't get it to ... intcounter = intcounter + 1 ... intstartdate = DateAdd ...
    (microsoft.public.access.queries)
  • RE: Update Query - Consecutive Numbers
    ... I modified the criteria string inside the DCOUNT. ... I'm afraid all the codes are still giving each episode the DCount figure ... intcounter = intcounter + 1 ...
    (microsoft.public.access.queries)
  • RE: Update Query - Consecutive Numbers
    ... You shouldn't need to do any kind of looping to update the Episode field. ... my rugby teams who book the rugby pitch in block of 7 weeks they get 8 ... intcounter = intcounter + 1 ... intstartdate = DateAdd ...
    (microsoft.public.access.queries)
  • RE: Update Query - Consecutive Numbers
    ... all of the BlockIDs: ... same episode number of 11. ... intcounter = intcounter + 1 ... intstartdate = DateAdd ...
    (microsoft.public.access.queries)