I have a query that has a song column and data calcuated about that song ... if the song isnt in the list, I want to return the value of "0". I thought i was getting an error because it was NULL but actually I get an error when it cant find the request. SO how do i get around this and make the output 0 when there isnt that particular song in the query?

thanks
joe

Build expression (inside of query) using IIF.
Syntax:
IIF(Expression, True, False)
Example:
IIF([Calculation] > 0, [Calculation], 0)

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I have a query that has a song column and data calcuated about that song ... if the song isnt in the list, I want to return the value of "0". I thought i was getting an error because it was NULL but actually I get an error when it cant find the request. SO how do i get around this and make the output 0 when there isnt that particular song in the query?

thanks
joe


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Viktor Farcic

TalkZone Manager

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Build expression (inside of query) using IIF.
Syntax:
IIF(Expression, True, False)
Example:
IIF([Calculation] &gt; 0, [Calculation], 0)
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I'm not exactly sure how to do this. Do you mean my Access query or my UD query? Let me explain my problem more indepth.

my access query is:

SELECT SONG_MASTER.SONG_ID, SONG_MASTER.SONG_NAME, Count(MASTER.SONG_ID) AS timesplayed
FROM SONG_MASTER LEFT JOIN MASTER ON SONG_MASTER.SONG_ID = MASTER.SONG_ID
WHERE (((MASTER.SET_NUMBER)="E1" Or (MASTER.SET_NUMBER)="E2" Or (MASTER.SET_NUMBER) Is Null))
GROUP BY SONG_MASTER.SONG_ID, SONG_MASTER.SONG_NAME;

the SONG_MASTER is a list of every song. Every entry in MASTER is a song which is played at a show with set number info and order played. This query counts only songs that have been played as an encore. The weird thing is that the results of the query is every song minus those that have been entered in MASTER but have not been played as an encore. So if i have 3 songs (song1, song2, and song3) entered into SONG_MASTER. Song1 and song2 have been entered into MASTER. Song 3 has not. Song1 has been played as an encore 3 times. Then the query will show song1 with 3 times played and song3 with zero times played. Song2 will not even be in the data base. So when ever i look up a detailed page about song2, I revieve an error that says:

<font color=red>Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/stats/indvidual_song_detail.asp, line 196 </font id=red>

I hope this is a better explination. It was late last night and I couldnt think.

If your first response is still that answer can you elaborate on how to use the IIF function?

Thanks for all your time
Joe



Edited by - jhm4021 on 31 Jan 2002 21:45:55

I suppose you're making this directly in Access. If so, SONG_ID in SQL should look like:
IIf([MASTER]![SONG_ID]&gt;0([MASTER]![SONG_ID],0) AS SongID

In plain words: If SONG_ID is bigger then 0 then display SONG_ID else display 0.

Other way to solve this is to place this code before calling recordset:
&lt;% On Error Resume Next %&gt;
This can be tricky and I don't advise it.

Viktor Farcic

TalkZone Manager

thanks alot ... you have been a great help


joe