The IF Staement if suitable to use

[Michael to Ed]

we have about 100 different Players, all held in a Table.
i would like to make a Query with an IF statement if thats possible.
In my database i have various Tables; Players, Course, Game, ect.
and would like to make a Report with the following. each player has a
different
'Value' say, for example 15.5.
If the player Gender is 'male' and is playing on the 'North' course then
apply the
formula as *1.202. If is female then apply a different Formula, say *1.502.
if playing on the 'South' course and is a 'male' apply a slightly different
formula say *1.225 and if is 'female' a *1.236 formula. having worked all
that
out, to apply a Discount or weighting Value held in the GameName (in
GameTable).
hope this is not too complicated!
i have placed this question with ED in the General Listing, but thought it
may be better placed here

Michael

 

[Douglas J. Steele]

You could use a series of nested IIf statements, although it can get a
little harry keeping track of all of the conditions!

For the 2 dimensions you've got below (Gender and Course), you'd use
something like:

[Value] * IIf([Gender] = 'Male', IIf([Course] = 'North', 1.202, 1.225),
IIf([Course] = 'North', 1.502, 1.236))

Another option, of course, is to create your own function, and call that
function in your query. Again, for the dimensions above, you'd have
something like:

Function CalculateMultiplier(Gender As Variant, Course As Variant) As Single

If IsNull(Gender) Or IsNull(Course) Then
CalculateMultiplier = 1
Else
If Gender = "Male" Then
If Course = "North" Then
CalculateMultiplier = 1.202
Else
CalculateMultiplier = 1.225
End If
Else
If Course = "North" Then
CalculateMultiplier = 1.502
Else
CalculateMultiplier = 1.236
End If
End If
End If

End Function

(I used Variant as the data type for the parameters just in case there's a
chance that the field might be Null)

 

[Michael to Ed]

Many thanks
i will work on that over the next few days

You could use a series of nested IIf statements, although it can get a
little harry keeping track of all of the conditions!

For the 2 dimensions you've got below (Gender and Course), you'd use
something like:

[Value] * IIf([Gender] = 'Male', IIf([Course] = 'North', 1.202, 1.225),
IIf([Course] = 'North', 1.502, 1.236))

Another option, of course, is to create your own function, and call that
function in your query. Again, for the dimensions above, you'd have
something like:

Function CalculateMultiplier(Gender As Variant, Course As Variant) As Single

If IsNull(Gender) Or IsNull(Course) Then
CalculateMultiplier = 1
Else
If Gender = "Male" Then
If Course = "North" Then
CalculateMultiplier = 1.202
Else
CalculateMultiplier = 1.225
End If
Else
If Course = "North" Then
CalculateMultiplier = 1.502
Else
CalculateMultiplier = 1.236
End If
End If
End If

End Function

(I used Variant as the data type for the parameters just in case there's a
chance that the field might be Null)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

we have about 100 different Players, all held in a Table.
i would like to make a Query with an IF statement if thats possible.
In my database i have various Tables; Players, Course, Game, ect.
and would like to make a Report with the following. each player has a
different
'Value' say, for example 15.5.
If the player Gender is 'male' and is playing on the 'North' course then
apply the
formula as *1.202. If is female then apply a different Formula, say
*1.502.
if playing on the 'South' course and is a 'male' apply a slightly
different
formula say *1.225 and if is 'female' a *1.236 formula. having worked all
that
out, to apply a Discount or weighting Value held in the GameName (in
GameTable).
hope this is not too complicated!
i have placed this question with ED in the General Listing, but thought it
may be better placed here

Michael