IIf Statement not working 2

[lars7]

Hi,
Can anyone help with this.
I can't get the following "if statement" to work the way I want it to. I would like to put a 2 in the column but not if the last part of the statement is true, then I would like a 5.

QPoints: IIf([GScore1]>[GScore2],IIf([FWin1]=1,2)) & IIf([GScore2]>[GScore1],IIf([FWin2]=1,2) & IIf([GScore1] & "" & [GScore2]=[FScore1] & "" & [FScore2],5))

I got it earlier to put the 5 in but it also put the 2 but at the moment it has just the 2. I tried "or" and "and" but with no luck.

 

[lars7]

Hi Jerry,

Some more progress.

The correct prediction for a draw is giving a 5 and the non correct a 2.

If the result is a win and a draw is predicted it is giving a 2. :-(

I am going home now but will be online again in an hour or so.

 

[lars7]

Hi There,

I have tried a multitude of combinations to get this to work with no success does anyone have any suggestions or alternatives I could try, this is last pices of the puzzel I would hate to be stuck now.

Public Function QPoints(lGScore1 As Long, lGScore2 As Long, lFScore1 As Long, lFScore2 As Long) As Long

Select Case True
Case lGScore1 > lGScore2 And lFScore1 > lFScore2
QPoints = 2
Case lGScore1 < lGScore2 And lFScore1 < lFScore2
QPoints = 2
Case lGScore1 = lGScore2 And lFScore2 = lFScore2 And lGScore1 <> lFScore2
QPoints = 2
Case lGScore1 = lGScore2 And lFScore2 = lFScore2 And lGScore1 = lFScore2
QPoints = 5
Case Else
QPoints = 0
End Select
End Function

 

[PHV]

What about this ?
QPoints: IIf([FScore1]=[GScore1] AND [FScore2]=[GScore2], 5, IIf(Sgn([FScore1]-[FScore2])=Sgn([GScore1]-[GScore2]), 2, 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jebry]

Hi!

I guess I am slow. What are GScore1 and GScore2 and FScore1 and FScore2? How many QPoints are awarded by result?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[lars7]

Thanks to everyone for the help with this.

Hi PHV,

I thought this could be done with an "If". What is Sgn? <---(retorical question) your stars on it's with with my thanks.

 

[PHV]

What is Sgn?
When in the debug window (Alt+F11) type sgn and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Amendment

Public Function QPoints(lGScore1 As Long, lGScore2 As Long, lFScore1 As Long, lFScore2 As Long) As Long

Select Case True
    Case lGScore1 > lGScore2 And lFScore1 > lFScore2
        QPoints = 2
    Case lGScore1 < lGScore2 And lFScore1 < lFScore2
        QPoints = 2
    Case lGScore1 = lGScore2 And lFScore1 = lFScore2 And lGScore1 <> lFScore1
        QPoints = 2
    Case lGScore1 = lGScore2 And lFScore1 = lFScore2 And lGScore1 = lFScore1
        QPoints = 5
    Case Else
        QPoints = 0
End Select

End Function

 

[lars7]

Hi Jerry,

Almost there now.

Correct score draw 5

draw 2

correct Score 2 :-(

correct result 2

I tried many combinations but have had no success.

Thanks for the help with this . I like the idea of this and could use it or a variation of elsewhere.

 

[JerryKlmns]

I thought that you said in your 20 Jun 06 9:22 post that

they will receive 2 points if they predict the right winner ie: actual result 3-0 predicted result 2-0, or if they predict a 1-1 draw when the actual result is 2-2.

That's why I posted again, otherwise PHV's (once again) got that right.

 

[lars7]

Hi Jerry,

yes but there is 2 criteria for getting 5 points ie:

correct score 3-0 predicted score 3-0 = 5 points

correct result 3-0 predicted result 2-0 = 2 points

or for a draw,

correct score 3-3 predicted score 3-3 = 5 points

correct result 3-3 predicted result 2-2 = 2 points

Sorry for any confusion caused.

PHV'S works great but I would like this to work too as I could use something like this elsewhere and I like to learn new things.

 

[JerryKlmns]

Public Function QPoints(lGScore1 As Long, lGScore2 As Long, lFScore1 As Long, lFScore2 As Long) As Long

Select Case True
Case (lGScore1 - lGScore2) * (lFScore1 - lFScore2)>0
QPoints = 2
Case (lGScore1 = lGScore2) AND (lFScore1 = lFScore2)
If lGScore1 = lFScore1 Then
QPoints = 5
Else
QPoints = 2
End If
Case Else
QPoints = 0
End Select

End Function

 

[lars7]

Hi Jerry,

It's still the same everythings great except:

correct score 3-0 predicted score 3-0 = 5 points :-(

 

[JerryKlmns]

Public Function QPoints(lGScore1 As Long, lGScore2 As Long, lFScore1 As Long, lFScore2 As Long) As Long

Select Case True
    [green]' Predicts the correct winner 1st team but not the exact score[/green]
    Case lGScore1 > lGScore2 And lFScore1 > lFScore2 And lGScore1 <> lFScore1
        QPoints = 2 [green]' Gets a beer[/green]
    
    [green]' Predicts the correct winner 2nd team but not the exact score[/green]
    Case lGScore1 < lGScore2 And lFScore1 < lFScore2 And lGScore1 <> lFScore1
        QPoints = 2 [green]' Gets a beer[/green]
    
    [green]' Predicts the correct winner = draw but not the exact score[/green]
    Case lGScore1 = lGScore2 And lFScore1 = lFScore2 And lGScore1 <> lFScore1
        QPoints = 2 [green]' Gets a beer[/green]
    
    [green]' Predicts the exact score[/green]
    Case lGScore1 = lFScore1 And lGScore2 = lFScore2
        QPoints = 5 [green]' Gets a case of beers[/green]
    
    [green]' Bad luck[/green]
    Case Else
        QPoints = 0 [green]'Bud lack[/green]
End Select

End Function

Boy, I need a beer.
(I don't play PRO-PO 'cause I DISLIKE football)
(PRO-PO = Football Score Prediction game)

 

[lars7]

You deserve a beer:

correct score 3-0 predicted score 3-0 = 5 points

correct result 3-0 predicted result 2-0 = 2 points

or for a draw,

correct score 3-3 predicted score 3-3 = 5 points

correct result 3-3 predicted result 2-2 = 2 points

Thanks for hanging in there Jerry this is great.

 

[JerryKlmns]