SubQuery

[Les Isaacs]

Hi All
I'm fairly new to subqueries - but thought I was getting there until now

I have

RankSum: DSum("[tblSavedResults]![Athlete_category_rank]","[tblSavedResults]","[tblSavedResults]![Athlete_person_number]= " & [tblSavedResultss]![Athlete_person_number] & " ")

I've aliased 'tblSavedResults' as 'tblSavedResultss'.
I was getting a syntax error until wrapped
[tblSavedResultss]![Athlete_person_number]
between the
" &
and the
& "

This works, except that it gives the result as a string rather than as an integer result as I expected. This means that the subsequent sort is no good. I have tried putting a Val() around the whole thing, but that gave me data type mismatch error :banghead:
'Athlete_category_rank' is definitely an integer field in table 'tblSavedResults'.
Hope someone can help - thanks in advance.
Les

 

[plog]

Placing Val() around the Dsum should work. Multiplying the Dsum by 1 would work as well. Both these should yield numeric values:

RankSum: DSum("[tblSavedResults]![Athlete_category_rank]","[tblSavedResults]","[tblSavedResults]![Athlete_person_number]= " & [tblSavedResultss]![Athlete_person_number])*1
 
RankSum: Val(DSum("[tblSavedResults]![Athlete_category_rank]","[tblSavedResults]","[tblSavedResults]![Athlete_person_number]= " & [tblSavedResultss]![Athlete_person_number]))

I also removed the trailing " " from the last argument of your Dsum--its unnecessary when comparing numbers.

 

[Les Isaacs]

Hi plog
Thanks for your reply - and apologies for the delay in my response - I've been away.
Placing Val() around the Dsum didn't work - I got a syntax error.
Multiplying by 1 did work though - so many thanks for that tip.
Les