Error msg in Query

[JimO]

I have a query to track NBA game progress by quarter using option buttons, one for each qtr as well as 1 for the half an another for a completed game. 1= 1st qtr, 2= 2nd qtr 3= halftime, 4 = 3rd qtr, 5 = 4th qtr and 0 = game complete. I can get the formula to work in a form but if I apply it to a query I get an error if 3 or 0 are selected.

The formula that works is:

=IIf([Qtr]=1,1,IIf([Qtr]=2,2,IIf([Qtr]=4,3,IIf([Qtr]=5,4,IIf([Qtr]=3,"H",IIf([Qtr]=0,"C"))))))

As applied in the query:

GmQtr:IIf([Qtr]=1,1,IIf([Qtr]=2,2,IIf([Qtr]=4,3,IIf([Qtr]=5,4,IIf([Qtr]=3,"H",If([Qtr]=0,"C"))))))

And the complete SQL is:

SELECT NBAScoringTbl.ScoringID, NBAScoringTbl.Season, NBATbl.Conference, NBATbl.Division, NBAScoringTbl.Game, NBATbl.Nickname, NBAScoringTbl.Win, NBAScoringTbl.Loss, 
NBAScoringTbl.Team, [Win] & "-" & [Loss] AS Record, [Win]+[Loss] AS GmPlayed, NBATbl.City, NBAScoringTbl.Scheduled, NBAScoringTbl.GmTime, NBATbl.Qtr, 
IIf([Qtr]=1,1,IIf([Qtr]=2,2,IIf([Qtr]=4,3,IIf([Qtr]=5,4,IIf([Qtr]=3,"H",IIf([Qtr]=0,"C")))))) AS GmQtr
FROM NBATbl INNER JOIN NBAScoringTbl ON NBATbl.NBAID = NBAScoringTbl.Team
ORDER BY NBAScoringTbl.Game, NBATbl.Nickname, NBAScoringTbl.Win DESC , NBAScoringTbl.Loss;

Any ideas where I went wrong?

Jim O

[ranman256]

You don't need IIFs in a query.
use a translation table and join it to the query.

[JimO]

Thank you for you response.

As a learner I don't really understand quite what you are saying, I am not familiar with a translation table. Is it a sort of junction table? Do I create a table with one field being the number 0-5 and another field with the desired output?

ID Number Output
1 1 1
2

[ssanfu]

Looks like it might be a syntax error

GmQtr:IIf([Qtr]=1,1,IIf([Qtr]=2,2,IIf([Qtr]=4,3,IIf([Qtr]=5,4,IIf([Qtr]=3,"H", If([Qtr]=0,"C"))))))

Should be "IIF"


Alternatives to the IIF() function:
(probably the best choice) is the SWITCH() function

GmQtr:Switch([Qtr]= 1, 1, [Qtr]= 2, 2,  [Qtr]= 3, "H", [Qtr]= 4, 3, [Qtr]= 5, 4,[Qtr]= 0, "C")

Or
the CHOOSE() function

GmQtr:GmQtr: Choose([Qtr],1,2,"H",3,4,"C")

If you want to try the CHOOSE() function, you would have to make a change:
1= 1st qtr
2= 2nd qtr
3= halftime
4 = 3rd qtr
5 = 4th qtr
6 = game complete.

[JimO]

Thank you very much for you input.

I will play around with it for a while, in the meantime I will mark this as solved.

Thanks
Jim O