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.

 

[JerryKlmns]

lars7

Since you play with VBA, I would create a function to return the result of the logic.

Function QPoints (lGScore1 As Long, lFGScore2 As Long, lFScore1 As Long, lFScore2 As Long, lFWin1 As Long, lFWin2 As Long) As Long

QPoints = 0
Select Case True
  Case lGScore1 > lGScore2
     If lFWin1 = 1 Then QPoints = 2
  Case lGScore1 < lGScore2
     If FWin2 = 1 Then QPoints = 2
  Case lGScore1 & "" & lGScore2 = lFScore1l & "" & lFScore2
     QPoints = 5
End Select
End Function

 

[lars7]

Hi Jerry,
Could you tell me how would I get it to show in my query.

 

[JerryKlmns]

Select QPoints([GScore1],[GScore2],[FScore1],[FScore2],[FWin1],[FWin2]) From YourTable

 

[Remou]

With a Case Else perhaps?

 

[lars7]

Hi Jerry,
Sorry I should have explained this better. The if statement was from "qryGameScore" here is the full SQL:

SELECT tblGameScore.GGameNo, tblGameScore.GPlayerId, [FScore1] & "" & [FScore2] AS QScore, [GScore1] & "" & [GScore2] AS QPreScore, tblfixtures.FScore1, tblfixtures.FScore2, tblGameScore.GScore1, tblGameScore.GScore2, tblfixtures.FWin1, tblfixtures.FWin2, IIf([GScore1]>[GScore2],IIf([FWin1]=1,2)) & IIf([GScore2]>[GScore1],IIf([FWin2]=1,2) & IIf([GScore1] & "" & [GScore2]=[FScore1] & "" & [FScore2],5)) AS QPoints
FROM tblGameScore INNER JOIN tblfixtures ON (tblGameScore.GGameNo = tblfixtures.GameNo) AND (tblGameScore.GGameNo = tblfixtures.GameNo) AND (tblGameScore.GGameNo = tblfixtures.GameNo);

 

[JerryKlmns]

Query

SELECT G.GGameNo, 
       G.GPlayerId, 
       [FScore1] & "" & [FScore2] AS QScore, 
       [GScore1] & "" & [GScore2] AS QPreScore, 
       F.FScore1, 
       F.FScore2, 
       G.GScore1, 
       G.GScore2, 
       F.FWin1, 
       F.FWin2,
QPoints([GScore1], [GScore2], [FScore1], [FScore2], [FWin1], [FWin2]) As myQPoints
FROM tblGameScore As G INNER JOIN tblfixtures As F ON (G.GGameNo = F.GameNo);

In a module place the function

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

QPoints = 0
Select Case True
  Case lGScore1 > lGScore2
     If lFWin1 = 1 Then QPoints = 2
  Case lGScore1 < lGScore2
     If FWin2 = 1 Then QPoints = 2
  Case lGScore1 & "" & lGScore2 = lFScore1l & "" & lFScore2
     QPoints = 5
End Select
End Function

 

[lars7]

Hi Jerry,
I tried it but myQPoints in saying "#ERROR", everything else in the query is showing ok.

 

[JerryKlmns]

Press Ctrl+G and type this
?QPoints (5, 3, 0, 0, 1, 0)
Should return 2

?QPoints (1, 3, 0, 0, 1, 0)
Should return 2

?QPoints (3, 3, 3, 3, 1, 0)
Should return 5

If your fields are NOT numeric (Long) change the declarations in the function

 

[lars7]

Hi,
All the fields are Number and the results of the test are below.


?QPoints (5, 3, 0, 0, 1, 0) <--- Returns 2

?QPoints (1, 3, 0, 0, 1, 0) <--- Returns 0

?QPoints (3, 3, 3, 3, 1, 0) <--- Returns 0

 

[JerryKlmns]

Some typos

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

QPoints = 0
Select Case True
  Case lGScore1 > lGScore2
     If lFWin1 = 1 Then QPoints = 2
  Case lGScore1 < lGScore2
     If lFWin2 = 1 Then QPoints = 2
  Case lGScore1 & "" & lGScore2 = lFScore1 & "" & lFScore2
     QPoints = 5
End Select
End Function

 

[lars7]

Hi Jerry,
I still have the Error but the test results are different.


?QPoints (5, 3, 0, 0, 1, 0) <--- Returns 2

?QPoints (1, 3, 0, 0, 1, 0) <--- Returns 0

?QPoints (3, 3, 3, 3, 1, 0) <--- Returns 5

 

[JerryKlmns]

For the lines where you see #ERROR, check the values and decide what should the function do in such a case. And even more, post some values that the function errors...

 

[lars7]

Hi Jerry,

The whole line "myQPoints" has "Error", there are 48 records (48 first round games in the world cup). I am using the world cup as the template for this project.

The idea for the query is to compair [FScore1] & "" & [FScore2](the actual score) with [GScore1] & "" & [GScore2](their predicted score) and see if they match which would give the player 5 points, but 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.

The "iif statement" I had put the 2 in but i need the 2 to be replaced with a 5 if that criteria was met which wasn't happening. :-(

I hope this explains it a little more.

 

[jebry]

Hi lars7!

It looks like you are trying to use multiple conditions in an IIf statement. If that is true then the format should be:

IIf(Condition1 And Condition2 Or (Condition3 And Condition4), True result, False result)

You can make your conditions very complicated but you need to put the whole set of conditions in one IIf statement, not in a series of IIf statements.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[lars7]

Hi Jebry,
Yes each part of the statement works fine on it own but I want to combine them in the one column could you suggest anything.

Here is the statements in parts:

IIf([GScore1]>[GScore2],IIf([FWin1]=1,2))

IIf([GScore2]>[GScore1],IIf([FWin2]=1,2)

IIf([GScore1] & "" & [GScore2]=[FScore1] & "" & [FScore2],5))

 

[jebry]

Hi again!

Well, I'm not sure what you are getting at with the third line but the first two can be combined like this:

IIf(([GScore1] > [GScore2] And [FWin1] = 1) Or ([GScore1] < [GScore2] And [FWin2] = 1), 2, 5)

That will combine your first two conditions into one and set the field to 2 if the combo is true and to 5 if it isn't. Like I mentioned, I am not sure what you mean by the third line?

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[JerryKlmns]

lars7

You don't need in your table the fields FWin1, FWin2 since they can be calculated

SELECT G.GGameNo,
       G.GPlayerId,
       [FScore1] & "" & [FScore2] AS QScore,
       [GScore1] & "" & [GScore2] AS QPreScore,
       F.FScore1,
       F.FScore2,
       G.GScore1,
       G.GScore2,
QPoints([GScore1], [GScore2], [FScore1], [FScore2]) As myQPoints
FROM tblGameScore As G INNER JOIN tblfixtures As F ON (G.GGameNo = F.GameNo);

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 [green]'Predicted the winner[/green]
    Case lGScore1 < lGScore2 And lFScore1 < lFScore2
        QPoints = 2 [green]'Predicted the winner[/green]
    Case lGScore1 = lFScore1 And lGScore2 = lFScore2
        QPoints = 5 [green]'Predicted the exact score[/green]
    Case Else
        QPoints = 0 [green]'No luck. Try again HAHAHAHAHAHA[/green]
End Select
End Function

And a worry of mine : You do keep scores like 7 - 0 as lGScore1 =7 lGScore2 = 0 and not lGScore2 = NULL, don't you?!

 

[lars7]

Hi Jerry,

This looks much better just a couple of things to sort:


A correct score gets a 2 not a 5.

A correct score draw gets a 5 but a non correct score draw gets 0.

I also tried the test again but it is not working.

 

[JerryKlmns]

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