IIF delima-Need Help PLEASE!

[DN]

Hope someone is out there on this Saturday. I am in dire need of assistance.
I have asked for help from this list before and have been fortunate to get
great assistance. Here is my query that works great:

SELECT Shows.ShowID, Entries.Place,
IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2
AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum,
Horses.HorseName, OwnersandRiders.[FirstName] & " " & [LastName] AS Owner,
Horses.NWHATRNumber, Horses.Title, Horses.Titles, Classes.NWHAHP,
OwnersandRiders.NWHAMember, Shows.Year
FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN
(Classes INNER JOIN Entries ON Classes.ClassID = Entries.ClassID) ON
Horses.HorseName = Entries.HorseName) ON OwnersandRiders.OwnerRiderID =
Entries.OwnerRiderID) ON (Shows.ShowID = Classes.ShowID) AND (Shows.Name =
Classes.ShowName)
WHERE (((Shows.ShowID)=176) AND ((Classes.NWHAHP)<>"NA") AND
((OwnersandRiders.NWHAMember)="yes") AND ((Shows.Year)="2005"))
ORDER BY Entries.Place, Classes.Entries, Classes.ClassNum;

Now I've been told that the Bonus point is only earned 1 time. This
calculation works if I change this statement IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus to IIf([Entries]>3 And [Place]=1,o.5,0) AS Bonus up
until the entries exceed 13 or more when I change this

IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2

to this

IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],6,5,4,3,2,1),0)),CInt(Nz(Choose([Place],6,5,4,3,2,1),0)),[Points1]+[Bonus])*2

This returns total Points as 12. I need it to return 13. I tried using
6.5 in the place of 6 but that isn't working either.

Can someone PLEASE Help me with this?????

Thanks,
DN

 

[Randy Harris]

This is pretty ugly, but:

iif(IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Ch
oose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2 > 13, 13,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose
([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2)

would do it.

 

[DN]

May be ugly, but it worked. Thanks so much for your help.
DN

This is pretty ugly, but:

iif(IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Ch
oose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2 > 13, 13,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose
([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2)

would do it.

Hope someone is out there on this Saturday. I am in dire need of assistance.
I have asked for help from this list before and have been fortunate to get
great assistance. Here is my query that works great:

SELECT Shows.ShowID, Entries.Place,
IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose
([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2
AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum,
Horses.HorseName, OwnersandRiders.[FirstName] & " " & [LastName] AS Owner,
Horses.NWHATRNumber, Horses.Title, Horses.Titles, Classes.NWHAHP,
OwnersandRiders.NWHAMember, Shows.Year
FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN
(Classes INNER JOIN Entries ON Classes.ClassID = Entries.ClassID) ON
Horses.HorseName = Entries.HorseName) ON OwnersandRiders.OwnerRiderID =
Entries.OwnerRiderID) ON (Shows.ShowID = Classes.ShowID) AND (Shows.Name =
Classes.ShowName)
WHERE (((Shows.ShowID)=176) AND ((Classes.NWHAHP)<>"NA") AND
((OwnersandRiders.NWHAMember)="yes") AND ((Shows.Year)="2005"))
ORDER BY Entries.Place, Classes.Entries, Classes.ClassNum;

Now I've been told that the Bonus point is only earned 1 time. This
calculation works if I change this statement IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus to IIf([Entries]>3 And [Place]=1,o.5,0) AS Bonus up
until the entries exceed 13 or more when I change this

IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose
([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])*2

to this

IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],6,5,4,3,2,1),0)),CInt(Nz(Choose
([Place],6,5,4,3,2,1),0)),[Points1]+[Bonus])*2

This returns total Points as 12. I need it to return 13. I tried using
6.5 in the place of 6 but that isn't working either.

Can someone PLEASE Help me with this?????

Thanks,
DN