Need help with complicated if statement

[annysjunkmail]

Hi Group,
Would appreciate if someone could help me with a complicated if
statement.
Here the set up...(I manage as awards database for applicants)
Applicants must select at least 1 target and can have up to 7 targets
(each target is a separate record)
Particular emphasis is placed on two of those targets, i.e. Female and
<25 years of age.
Therefore if Female is not selected then it means that the applicant is
a male and if <25 is not selected then it means that the person is >25
years of age.
Therefore, I am trying to code the logic to show either one of the
following 4 situations which could arise...

1 Female <25
2 Female >25
3 Male <25
4 Male >25

Here's some sample code...obviously I am missing some clever code to
make the age work correctly if you know what I mean.

KeyTarget:
IIf([TargetGroupName]="Female","Female",IIf([TargetGroupName]<>"Female","Male"))

Could someone help me?

Thanks
Tony

 

[Gina via AccessMonster.com]

IIf([TargetGroupName]="Female", IIf([TargetGroupAge] < 25, "Female <25",
"Female >25"), IIf([TargetGroupAge] < 25, "Male <25", "Male >25"))

I'm note sure if that is what you wanted, but it should result in the four
groupings that you gave.

One question for you, what if the person is 25?

Gina

Hi Group,
Would appreciate if someone could help me with a complicated if
statement.
Here the set up...(I manage as awards database for applicants)
Applicants must select at least 1 target and can have up to 7 targets
(each target is a separate record)
Particular emphasis is placed on two of those targets, i.e. Female and
<25 years of age.
Therefore if Female is not selected then it means that the applicant is
a male and if <25 is not selected then it means that the person is >25
years of age.
Therefore, I am trying to code the logic to show either one of the
following 4 situations which could arise...

1 Female <25
2 Female >25
3 Male <25
4 Male >25

Here's some sample code...obviously I am missing some clever code to
make the age work correctly if you know what I mean.

KeyTarget:
IIf([TargetGroupName]="Female","Female",IIf([TargetGroupName]<>"Female","Male"))

Could someone help me?

Thanks
Tony

 

[annysjunkmail]

Hi Gina,

Thank you for your reply.
There is no TargetGroupAge field.
All targets appear in the field TargetGroupName. I am trying to
combine these 2 targets to achieve the logic set out earlier, i.e. if
Female is not selected but <25 is then this means that the applicant is
Male and > 25.

Good question about the age of the applicant - if they are 25. I
understand (as I didn't write the targets) that <25 also means 25 or
under, but this does affect the results of my query

Hope this clarifies

Tony

 

[Gina via AccessMonster.com]

Can you show me a couple of examples of what a target field would look like?

 

[annysjunkmail]

Here you go Gina..

ApplicationID Target
1 Female
1 Create jobs
1 Economic renewal
2 Create jobs
2 <25
2 Unemployed
3 Economic renewal
3 Female
3 <25
4 Unemployed
4 Training

For my request this would translate into the following (I am only
interested in the Female and <25 logic).

ApplicationID KeyTarget
1 Female >25
2 Male <25
3 Female <25
4 Male >25

It's pretty convoluted but this is the way that they have been set up
and this is how I need to report (see my dilema!)

Thanks again
Tony

 

[Gina via AccessMonster.com]

The only way that I can see to do this is through code. I'm not sure when you
would be running it, but if you call this function passing it an
ApplicationID it will return a KeyTarget. You will just need to correct the
table name in the OpenRecordset call - I just made one up to test it.

Public Function GetKeyTarget(lngApplicationID As Long) As String
Dim db As Database
Dim rs As Recordset
Dim blnFemale As Boolean
Dim blnUnder25 As Boolean

'get recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select ApplicationID, Target FROM
ApplicationTargets Where ApplicationID = " & lngApplicationID)

'boolean values to flag KeyTarget
blnFemale = False
blnUnder25 = False

'loop through recordset
While Not rs.EOF
If Trim(rs("Target")) = "Female" Then
blnFemale = True
ElseIf Trim(rs("Target")) = "<25" Then
blnUnder25 = True
End If

rs.MoveNext
Wend

If blnFemale = True And blnUnder25 = True Then
GetKeyTarget = "Female <25"
ElseIf blnFemale = True And blnUnder25 = False Then
GetKeyTarget = "Female >25"
ElseIf blnFemale = False And blnUnder25 = True Then
GetKeyTarget = "Male <25"
ElseIf blnFemale = False And blnUnder25 = False Then
GetKeyTarget = "Male >25"
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Function

 

[annysjunkmail]

Gina,
I never knew it would be so complicated.
I will test it at work tomorrow and will let you know how I get on
Thanks very much for your expertise and hard work
Tony

 

[annysjunkmail]

Hi Gina,

Back in work again. Have tested your code.
I keep getting run-time error 3464. Data type mismatch in criteria
expression.
I think this is being caused as the ApplicationID field is a text
primary field. Any further suggestions?
Tony

 

[Gary Walter]

Hi Tony,

Another tack might be:
(untested)

qryTestTargets:

SELECT
ApplicationID,
Sum([Target]="Female") As IsFemale,
Sum([Target]="<25") As IsLT25
FROM
yourtable
GROUP BY
ApplicationID;

Look at results of this query
to see if you get what I think
you should.....

good luck,

gary

 

[Gary Walter]

Hi Tony,

Another tack might be:
(untested)

qryTestTargets:

SELECT
ApplicationID,
Sum([Target]="Female") As IsFemale,
Sum([Target]="<25") As IsLT25
FROM
yourtable
GROUP BY
ApplicationID;

Look at results of this query
to see if you get what I think
you should.....

good luck,

gary

Here you go Gina..

ApplicationID Target
1 Female
1 Create jobs
1 Economic renewal
2 Create jobs
2 <25
2 Unemployed
3 Economic renewal
3 Female
3 <25
4 Unemployed
4 Training

For my request this would translate into the following (I am only
interested in the Female and <25 logic).

ApplicationID KeyTarget
1 Female >25
2 Male <25
3 Female <25
4 Male >25

Hi Tony,

Just to be clear, I think for your example data
the query "qryTestTargets" result would be:

ApplicationID IsFemale IsLT25
1 -1 0
2 0 -1
3 -1 -1
4 0 0

You could then "IIF" the query results,

or you could create a lookup table (say "tblTarget")
and join on IsFemale and IsLT25...

tblTarget:

IsFemale IsLT25 KeyTarget
0 0 "Male >25"
0 -1 "Male <=25"
-1 0 "Female >25"
-1 -1 "Female <=25"

SELECT
Q.ApplicationID,
Q.IsFemale,
Q.IsLT25,
t.KeyTarget
FROM
qryTestTargets As Q
INNER JOIN
tblTarget As t
ON
Q.IsFemale = t.IsFemale
AND
Q.IsLT25 = t.IsLT25;

good luck,

gary

 

[annysjunkmail]

Gary,
Thank you for your interest and approach.
I have tried your first method and it works (partly) as suggested.

I then wrote the 'IIF' statement as suggested by Gina (KeyTarget:
IIf([IsFemale]=True,IIf([IsLT25]=True,"Female <25","Female

25"),IIf([IsLT25]=True,"Male <25","Male >25"))) but now it returns incorrect logic if there is more than 1 target.

Here is an actual example taken from the database

ApplicationID IsFemale IsLT25 TargetGroupName
015021 0 -1 People< 25 years of age
015021 -1 0 Female
015414 -1 0 Female
015821 -1 0 Female
015871 0 0 None
016004 0 -1 People< 25 years of age
016033 0 0 Farmers
016033 0 0 Members of farm Families
016033 -1 0 Female
016193 -1 0 Female
016252 0 0 Farmers
016426 0 0 Farmers
016426 -1 0 Female
016470 0 0 Members of farm Families
016749 0 0 Members of farm Families
018360 0 0 Members of farm Families
019485 0 0 Farmers
019594 0 0 Farmers
019594 0 0 Members of farm Families
019617 0 0 Members of farm Families
019617 0 -1 People< 25 years of age
019617 -1 0 Female

This is the returned report...

ApplicationID Expr2
015021 Female >25
015021 Male <25
015414 Female >25
015821 Female >25
015871 Male >25
016004 Male <25
016033 Female >25
016033 Male >25
016193 Female >25
016252 Male >25
016426 Female >25
016426 Male >25
016470 Male >25
016749 Male >25
018360 Male >25
019485 Male >25
019594 Male >25
019617 Female >25
019617 Male <25
019617 Male >25


....you can see how it applies logic incorrectly where a application has
multiple targets and where it is neither a Female or <25. What I think
I need is something which looks at overall targets and report on it
this way

....maybe my IIF statement is wrong???

All suggestions would be gratefully appreciated

Tony

 

[Gina via AccessMonster.com]

Hi Tony,

I modified Gary's query (which was quite clever!) to include the logic for
the iif statement. If you change the table name, you should be able to run it
against your data with the desired result. I tried it with the sample data
you gave me yesterday.

SELECT
ApplicationID,
iif(Sum([Target]="Female"), iif(Sum([Target]="<25"), "F <25", "F >25"), iif
(Sum([Target]="<25"), "M <25", "M >25")) As KeyTarget
FROM
ApplicationTargets
GROUP BY
ApplicationID;

Gina

Gary,
Thank you for your interest and approach.
I have tried your first method and it works (partly) as suggested.

I then wrote the 'IIF' statement as suggested by Gina (KeyTarget:
IIf([IsFemale]=True,IIf([IsLT25]=True,"Female <25","Female

25"),IIf([IsLT25]=True,"Male <25","Male >25"))) but now it returns incorrect logic if there is more than 1 target.

Here is an actual example taken from the database

ApplicationID IsFemale IsLT25 TargetGroupName
015021 0 -1 People< 25 years of age
015021 -1 0 Female
015414 -1 0 Female
015821 -1 0 Female
015871 0 0 None
016004 0 -1 People< 25 years of age
016033 0 0 Farmers
016033 0 0 Members of farm Families
016033 -1 0 Female
016193 -1 0 Female
016252 0 0 Farmers
016426 0 0 Farmers
016426 -1 0 Female
016470 0 0 Members of farm Families
016749 0 0 Members of farm Families
018360 0 0 Members of farm Families
019485 0 0 Farmers
019594 0 0 Farmers
019594 0 0 Members of farm Families
019617 0 0 Members of farm Families
019617 0 -1 People< 25 years of age
019617 -1 0 Female

This is the returned report...

ApplicationID Expr2
015021 Female >25
015021 Male <25
015414 Female >25
015821 Female >25
015871 Male >25
016004 Male <25
016033 Female >25
016033 Male >25
016193 Female >25
016252 Male >25
016426 Female >25
016426 Male >25
016470 Male >25
016749 Male >25
018360 Male >25
019485 Male >25
019594 Male >25
019617 Female >25
019617 Male <25
019617 Male >25

...you can see how it applies logic incorrectly where a application has
multiple targets and where it is neither a Female or <25. What I think
I need is something which looks at overall targets and report on it
this way

...maybe my IIF statement is wrong???

All suggestions would be gratefully appreciated

Tony

 

[annysjunkmail]

SUCCESS!!!

Thanks very much Gina and Gary - you have made my life so much easier!
I appreciate your time and expertise

Tony