Marshall
Thank you very much for this fix. I also checked out
Predicates on google, this will be a valuable tool.
Regarding the rational behind my attempts to get around
the problem I had - tiredness and blind aggravation are
my only excuses OH and chuck in a bit of ignorance too.
Thanks again.
Regards Jason Willis
>-----Original Message-----
>Jason wrote:
>
>>I am developing a lapscoring database. The riders
number
>>is taken, thier previous lap is looked up to determine
>>how many laps they have done
>>
>>Its really weird, I tried using DAO - findlast()
method,
>>it looks up the last record in the table with a
memberID
>>as the criteria, seems to work good until about lap 5 -
>>stops finding the last record - sometimes. I use it to
>>increment the next lap i.e. the next lap = the last lap
>>for that member + 1.
>>
>>The problem seems inconsistent. I tried using a query
for
>>the data source, tried using "DLAST" method on the
table
>>direct - same problem. I even moved the table out of
>>Access into MYSQL - same problem. Had a bit more luck
>>using "DMAX" but I still don't trust it.
>>
>>
>>Code below:
>> Set rstLapScore = CurrentDb.OpenRecordset
("select
>>* from tblLapScore where raceid = " _
>> & intRaceID & "and
>>RiderNumber = '" & strNumber & "'", dbOpenDynaset)
>>
>> 'Set rstLapScore = CurrentDb.OpenRecordset
>>("qryLapScoreRider", dbOpenDynaset)
>>
>> With rstLapScore
>>'>>>>>>>>>>>>>>>>>>>>>>>>>>> problem is below
>> intLap = Nz(DMax
>>("Lap", "tblLapScore", "RiderNumber = '" & strNumber
>>& "'"), 0) + 1
>>
>> 'add rider's lap
>> .AddNew
>> !RaceID = intRaceID
>> !SignOnID = intSignOnID
>> !RiderNumber = strNumber
>> !lap = intLap
>> .Update
>>
>> End With
>
>
>Not sure I follow all the attempts above, but you should
be
>aware that Last and DLast refer to the last record found
in
>an unordered bag of records (i.e a table) so they are
nearly
>usles for this purpose. The easiest way to get the
latest
>lap is to use the TOP predicate and an ORDER BY clause:
>
>strSQL = "SELECT TOP 1 table,* " _
> & "FROM tblLapScore " _
> & "WHERE raceid = " & intRaceID _
> & " And RiderNumber = '"
& strNumber & "' " _
> & "ORDER BY lap DESC"
>Set rstLapScore = CurrentDb.OpenRecordset(strSQL, _
>
dbOpenDynaset)
>With rstLapScore
> If .RecordCount > 0 Then
> intLap = !Lap + 1
> Else
> intLap = 1
> End If
> .AddNew
> . . .
>--
>Marsh
>MVP [MS Access]
>.
>
|
Similar Threads
