RE: Update Query - Consecutive Numbers

James,

My bad. The last argument of the Dcount also has to be a string, but the
way I have handled it, it is not.

Lets try:

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', 'tblevent', " _
& "'[eventstart] <= #" & [eventstart] & "# " _
& " AND [block] = " & Me.blockid _
& " and [origination] = -1') " _
& "WHERE [block] = " & Me.blockid _
& " AND [origination] = -1 "

I've added a single quote right after the " in line 3, and another right
after the -1 in line 5.

Hope this works.
--
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 Dale,

Ha ha ha as you can probably guess I'm still a bit 'green' with my code but
am slowly meandering towards an understanding!!!!!

I'm understand the code, but am the error message "Run-time error '2465' -
Microsoft Access can't find the field '|' referred to in your expression"

I currently have strSQL set as a string, that wouldn't be the cause of this
pesky error.

Regards

JAMES



"Dale Fye" wrote:

Try this:

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', 'tblevent', " _
& "[eventstart] <= #" & [eventstart] & "# " _
& " AND [block] = " & Me.blockid _
& " and [origination] = -1) " _
& "WHERE [block] = " & Me.blockid _
& " AND [origination] = -1 "

I wrapped 'tblEvent' in single quotes.

Additionally, I modified the criteria string inside the DCOUNT. To use
DCOUNT in this method, you have to distinguish between the fields inside the
DCOUNT and the values from the fields in the main query (look at the way I
put the second instance of [EventStart] and the reference to me.BlockID
outside of the quotes.

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

Dale,

I'm afraid all the codes are still giving each episode the DCount figure
instead of the consecutive numbers from 1 to DCount

As requested, code below.

Many thanks for all of this.

JAMES

======================
Private Sub btnEpisodeNo_Click()

Dim strSQL As String

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', tblevent," _
& "'[eventstart] <= [eventstart] and [block] = " & Me.blockid &
" and [origination] = " & True & "')" _
& "WHERE [block] = " & Me.blockid & " and [origination] = " &
True & ""
DoCmd.RunSQL strSQL
DoCmd.Requery "frmEventSub"

End Sub




"Dale Fye" wrote:

James,

The following query worked for me, to update all of the Episode values, for
all of the BlockIDs:

UPDATE tbl_Events
SET tbl_Events.Episode = DCount("*","tbl_Events","[StartDate] <= #" &
[tbl_Events].[StartDate] & "# AND [BlockID] = " & [tbl_Events].[BlockID]);

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

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
    ... 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
    ... 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
    ... I'm afraid all the codes are still giving each episode the DCount figure ... Dim strSQL As String ... intcounter = intcounter + 1 ...
    (microsoft.public.access.queries)
  • Re: How to return a result of a SQL query within an Event Procedure (&what am I doing wrong here
    ... works on the textboxes, using the After Update event (this could be ... but I'm not a s/w engineer to know how DCount is set up ... Dim MyCField1 As String 'global variables required otherwise runtime ...
    (microsoft.public.access.formscoding)
  • Re: Dcount return incorrect result for filtered record
    ... I can't see a DCount() in your code, but there is a problem with the filter string. ... open the Immediate Window and see if you can see what's wrong with the filter string. ... Dim strMessage as String ...
    (microsoft.public.access.formscoding)