Problems witd underquestions like this....

# Thread: Problems witd underquestions like this....

1. Junior Member
Join Date
Dec 1969
Posts
2

## Problems witd underquestions like this....

I&#039;m an beginner pls. help me out with this..<BR><BR>sql = _<BR>" select s.namn, " & _<BR>" avg(cdbl(resultat)/cdbl(serier)) as snitt ," & _<BR>" sum(serier) as totalt_serier," & _<BR>" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = &#039;H&#039;) as snitt_hemma," & _<BR>" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = &#039;B&#039;) as snitt_borta," & _<BR>" sum(iif(H_B=&#039;H&#039;,totalt_serier,0)) as serier_hemma, " & _<BR>" sum(iif(H_B=&#039;B&#039;,totalt_serier,0)) as serier_borta, " & _<BR>" cdbl(sum(poang))/cdbl(sum(serier)) as poang_snitt, " & _<BR>" max(resultat) as basta, " & _<BR>" min(resultat) as samsta from medlemmar as s left outer join resultat as r on r.spelare = s.id" & _<BR>" group by s.namn, id ORDER BY 2 DESC"<BR>set rs = connect.execute(sql)<BR><BR>Error:<BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)<BR>[Microsoft][Drivrutin för ODBC Microsoft Access] Underfrågor kan inte användas i uttrycket (IIf(H_B=&#039;H&#039;,totalt_serier,0)).<BR>/bowling/sql.asp, line 32<BR><BR>Line 32 -&#062; set rs = connect.execute(sql)<BR><BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Doesn't make sense!

You have<BR> sum(serier) as totalt_serier<BR>but then you try to use totalt_serier in the IIF function.<BR><BR>How can the DB engine possibly know what the final value of sum(serier) will be until the entire recordset is processed?<BR><BR>I *think* you wanted to simply use serier, instead of totalt_serier:<BR> " sum(iif(H_B=&#039;H&#039;,serier,0)) as serier_hemma, " & _<BR> " sum(iif(H_B=&#039;B&#039;,serier,0)) as serier_borta, " & _<BR><BR>See if that works.<BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
2

## RE: Doesn't make sense!

It dosn&#039;t work erlier, however not it will work fine. <BR>Thanks !!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•