Iif

[Jones Stanwell]

I need help

So here goes

I have a table wich acts a as a score sheet

With the following layout


Score1 = Home team Score
Score2 - Away Team Scorfe
Score 3 = Home team score when there away
Score4 = Away team score at the ground

What I need help with is an iff statment to add on to the end of this calculation within a query

Sum(([Score 1]+([Score 3]))) this gives me the total for each time they have won 21. as 21 is the maximum score they can get anything less than 21 is a loss.

What I need is if score1 says 21 and score 3 is blank because they did not play that game i still need 21 to show when showing the total.

In english is reads score1+score3, if socre 3 is null then add just score1

Please could some one write it out for me so I could just copy and past it into my query

 

[Peter Reid]

Do you need to use iif?

Have a look at the Nz function

eg
NZ([Score1],0)+NZ([Score3],0)

 

[Jones Stanwell]

well i think i do cause it saying after adding the two fields together if score 3 isnul then use score 1 so basicaly score 1 =21 score 3 = blank so total for would be 21

 

[GohDiamond]

Just like PR said above:

FinalScore:=NZ([Score1],0)+NZ([Score3],0)

Forget the SUM() function and the iif statement, this seems to be just simple addition:
Nz = NonZero function and converts Empty or Null or non numeric values in a field to '0' so that they can be added together with actual numeric values.

goh

 

[Jones Stanwell]

i tried this but it keeps asking me to enter paramater i just want it to add the two coloums together thats why i used sum and then have an exemption on the end to say if score 3 is isnul then just add score 1

 

[Peter Reid]

What parameter was it asking you for?

 

[Jones Stanwell]

score 1 score 3 asking me for a parameter all i want the statment to do is add up the numbers in each coloum score 1 and score 3 and return a total the only problem that arrises is when one home game has been played and not the away match so it should just show 21 as the the total for as its like this 21+0 is 21 thats why i think an iff statment is better

 

[Brianwarnock]

You have all ready been told that NZ is an implicit if it basically replaces Iif(isnull(score1),0,score1). If you have been asked for a parameter it is probably because you have misspelt the field name score 1instead of score1

Brian