Nested IIf Statement

[Tara]

I'm trying to create a query that requires a lengthy nested IIIf statement.
So lengthy in fact that I'll have to split the nested statement into 2 fields
and then combine them later on. I know there is a way to do this with code
(and I know that doing it with code is the best way), but I have no idea
where to start. Here's the SQL for the query currently - I didn't post the
entire nested IIf statement in order to save space:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS Name,
tblClients.StreetAddress, tblClients.City, tblClients.State, tblClients.Zip,
tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS CurrentAge,
IIf([CurrentAge]<-30,"TGY Becoming A Parent",IIf([CurrentAge] Between -30 And
-15,"TGY Getting Ready",IIf([CurrentAge] Between -14 And 15,"TGY
Newborn",IIf([CurrentAge] Between 15 And 45,"TGY Months 1 &
2",IIf([CurrentAge] Between 46 And 75,"TGY Months 2 & 3",IIf([CurrentAge]
Between 76 And 105,"TGY Months 3 & 4",IIf([CurrentAge] Between 106 And
135,"TGY Months 4 & 5"))))))) AS Packet
FROM tblClients;

 

[Graham Mandeno]

Hi Tara

I think it would be better to use a table-driven solution. A simple setup
would require a table with three fields:

MinAge MaxAge PacketName
-300 -30 "TGY Becoming A Parent"
-30 -15 "TGY Getting Ready"
-14 15 "TGY Newborn"
.... etc

Then you can use a DLookup in your query:

SELECT ...
DLookup( "PacketName", "tblPackets",
[CurrentAge] & " between MinAge and MaxAge") as Packet
....

This would be much easier to maintain as well, if you change the packets
that are available or the applicable ages.

 

[Graham Mandeno]

[Funny - it says my last reply was deleted from the server! I'll try
again...]

Hi Tara

I think it would be better to use a table-driven solution. A simple setup
would require a table with three fields:

MinAge MaxAge PacketName
-300 -30 "TGY Becoming A Parent"
-30 -15 "TGY Getting Ready"
-14 15 "TGY Newborn"
.... etc

Then you can use a DLookup in your query:

SELECT ...
DLookup( "PacketName", "tblPackets",
[CurrentAge] & " between MinAge and MaxAge") as Packet
....

This would be much easier to maintain as well, if you change the packets
that are available or the applicable ages.

 

[Tara]

Hi Graham. Thanks for the suggestion, I appreciate the help. I tried it but
I'm getting an error message. I must still have something wrong...Think you
could look at this for me again? Here's the error message:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

Here's the SQL now:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS
ClientName, tblClients.StreetAddress, tblClients.City, tblClients.State,
tblClients.Zip, tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS
CurrentAge, DLookup( "Packet", "tblPacketInfo",[CurrentAge] & " Between
MinAge and MaxAge") as Packet
tblClients.WithdrawnPktProgram, tblClients.CompletedPktProgram
FROM tblClients
WHERE (((tblClients.WithdrawnPktProgram)=No) AND
((tblClients.CompletedPktProgram)=No));

[Funny - it says my last reply was deleted from the server! I'll try
again...]

Hi Tara

I think it would be better to use a table-driven solution. A simple setup
would require a table with three fields:

MinAge MaxAge PacketName
-300 -30 "TGY Becoming A Parent"
-30 -15 "TGY Getting Ready"
-14 15 "TGY Newborn"
.... etc

Then you can use a DLookup in your query:

SELECT ...
DLookup( "PacketName", "tblPackets",
[CurrentAge] & " between MinAge and MaxAge") as Packet
....

This would be much easier to maintain as well, if you change the packets
that are available or the applicable ages.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to create a query that requires a lengthy nested IIIf
statement.
So lengthy in fact that I'll have to split the nested statement into 2
fields
and then combine them later on. I know there is a way to do this with code
(and I know that doing it with code is the best way), but I have no idea
where to start. Here's the SQL for the query currently - I didn't post
the
entire nested IIf statement in order to save space:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS Name,
tblClients.StreetAddress, tblClients.City, tblClients.State,
tblClients.Zip,
tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS CurrentAge,
IIf([CurrentAge]<-30,"TGY Becoming A Parent",IIf([CurrentAge] Between -30
And
-15,"TGY Getting Ready",IIf([CurrentAge] Between -14 And 15,"TGY
Newborn",IIf([CurrentAge] Between 15 And 45,"TGY Months 1 &
2",IIf([CurrentAge] Between 46 And 75,"TGY Months 2 & 3",IIf([CurrentAge]
Between 76 And 105,"TGY Months 3 & 4",IIf([CurrentAge] Between 106 And
135,"TGY Months 4 & 5"))))))) AS Packet
FROM tblClients;

 

[Tara]

Scratch that last post Graham. I was missing a comma - momentary brain
lapse. However, I'm still getting an error message. It says it can't find
the field CurrentAge. I'm guessing it's because CurrentAge isn't in
tblPacketInfo - it's a calculated field within the query. What would the
solution be in this circumstance?

Thanks for any additional help!

Hi Graham. Thanks for the suggestion, I appreciate the help. I tried it but
I'm getting an error message. I must still have something wrong...Think you
could look at this for me again? Here's the error message:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

Here's the SQL now:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS
ClientName, tblClients.StreetAddress, tblClients.City, tblClients.State,
tblClients.Zip, tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS
CurrentAge, DLookup( "Packet", "tblPacketInfo",[CurrentAge] & " Between
MinAge and MaxAge") as Packet
tblClients.WithdrawnPktProgram, tblClients.CompletedPktProgram
FROM tblClients
WHERE (((tblClients.WithdrawnPktProgram)=No) AND
((tblClients.CompletedPktProgram)=No));

[Funny - it says my last reply was deleted from the server! I'll try
again...]

Hi Tara

I think it would be better to use a table-driven solution. A simple setup
would require a table with three fields:

MinAge MaxAge PacketName
-300 -30 "TGY Becoming A Parent"
-30 -15 "TGY Getting Ready"
-14 15 "TGY Newborn"
.... etc

Then you can use a DLookup in your query:

SELECT ...
DLookup( "PacketName", "tblPackets",
[CurrentAge] & " between MinAge and MaxAge") as Packet
....

This would be much easier to maintain as well, if you change the packets
that are available or the applicable ages.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to create a query that requires a lengthy nested IIIf
statement.
So lengthy in fact that I'll have to split the nested statement into 2
fields
and then combine them later on. I know there is a way to do this with code
(and I know that doing it with code is the best way), but I have no idea
where to start. Here's the SQL for the query currently - I didn't post
the
entire nested IIf statement in order to save space:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS Name,
tblClients.StreetAddress, tblClients.City, tblClients.State,
tblClients.Zip,
tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS CurrentAge,
IIf([CurrentAge]<-30,"TGY Becoming A Parent",IIf([CurrentAge] Between -30
And
-15,"TGY Getting Ready",IIf([CurrentAge] Between -14 And 15,"TGY
Newborn",IIf([CurrentAge] Between 15 And 45,"TGY Months 1 &
2",IIf([CurrentAge] Between 46 And 75,"TGY Months 2 & 3",IIf([CurrentAge]
Between 76 And 105,"TGY Months 3 & 4",IIf([CurrentAge] Between 106 And
135,"TGY Months 4 & 5"))))))) AS Packet
FROM tblClients;

 

[Graham Mandeno]

Hi Tara

Yes, I see CurrentAge is a calculated field. Sometimes they can be used in
other expressions and sometimes not. Despite having worked with Access
since version 1.0, I've never quite been able to work out exactly when it is
and isn't allowed!

However, you should be able to substitute the DateDiff expression for
[CurrentAge]:

DLookup( "Packet", "tblPacketInfo",
DateDiff("d",[EDC/DOB],Date()) & " Between MinAge and MaxAge")
as Packet

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Scratch that last post Graham. I was missing a comma - momentary brain
lapse. However, I'm still getting an error message. It says it can't
find
the field CurrentAge. I'm guessing it's because CurrentAge isn't in
tblPacketInfo - it's a calculated field within the query. What would the
solution be in this circumstance?

Thanks for any additional help!

Hi Graham. Thanks for the suggestion, I appreciate the help. I tried it
but
I'm getting an error message. I must still have something wrong...Think
you
could look at this for me again? Here's the error message:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

Here's the SQL now:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS
ClientName, tblClients.StreetAddress, tblClients.City, tblClients.State,
tblClients.Zip, tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS
CurrentAge, DLookup( "Packet", "tblPacketInfo",[CurrentAge] & " Between
MinAge and MaxAge") as Packet
tblClients.WithdrawnPktProgram, tblClients.CompletedPktProgram
FROM tblClients
WHERE (((tblClients.WithdrawnPktProgram)=No) AND
((tblClients.CompletedPktProgram)=No));

[Funny - it says my last reply was deleted from the server! I'll try
again...]

Hi Tara

I think it would be better to use a table-driven solution. A simple
setup
would require a table with three fields:

MinAge MaxAge PacketName
-300 -30 "TGY Becoming A Parent"
-30 -15 "TGY Getting Ready"
-14 15 "TGY Newborn"
.... etc

Then you can use a DLookup in your query:

SELECT ...
DLookup( "PacketName", "tblPackets",
[CurrentAge] & " between MinAge and MaxAge") as Packet
....

This would be much easier to maintain as well, if you change the
packets
that are available or the applicable ages.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to create a query that requires a lengthy nested IIIf
statement.
So lengthy in fact that I'll have to split the nested statement into
2
fields
and then combine them later on. I know there is a way to do this with
code
(and I know that doing it with code is the best way), but I have no
idea
where to start. Here's the SQL for the query currently - I didn't
post
the
entire nested IIf statement in order to save space:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS
Name,
tblClients.StreetAddress, tblClients.City, tblClients.State,
tblClients.Zip,
tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS CurrentAge,
IIf([CurrentAge]<-30,"TGY Becoming A Parent",IIf([CurrentAge]
Between -30
And
-15,"TGY Getting Ready",IIf([CurrentAge] Between -14 And 15,"TGY
Newborn",IIf([CurrentAge] Between 15 And 45,"TGY Months 1 &
2",IIf([CurrentAge] Between 46 And 75,"TGY Months 2 &
3",IIf([CurrentAge]
Between 76 And 105,"TGY Months 3 & 4",IIf([CurrentAge] Between 106
And
135,"TGY Months 4 & 5"))))))) AS Packet
FROM tblClients;

 

[Tara]

It worked perfectly Graham!

Thank you so much for all your help!

Hi Tara

I think it would be better to use a table-driven solution. A simple setup
would require a table with three fields:

MinAge MaxAge PacketName
-300 -30 "TGY Becoming A Parent"
-30 -15 "TGY Getting Ready"
-14 15 "TGY Newborn"
.... etc

Then you can use a DLookup in your query:

SELECT ...
DLookup( "PacketName", "tblPackets",
[CurrentAge] & " between MinAge and MaxAge") as Packet
....

This would be much easier to maintain as well, if you change the packets
that are available or the applicable ages.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to create a query that requires a lengthy nested IIIf
statement.
So lengthy in fact that I'll have to split the nested statement into 2
fields
and then combine them later on. I know there is a way to do this with code
(and I know that doing it with code is the best way), but I have no idea
where to start. Here's the SQL for the query currently - I didn't post
the
entire nested IIf statement in order to save space:

SELECT tblClients.ClientID, ([ClientFirst] & " " & [ClientLast]) AS Name,
tblClients.StreetAddress, tblClients.City, tblClients.State,
tblClients.Zip,
tblClients.[EDC/DOB], DateDiff("d",[EDC/DOB],Date()) AS CurrentAge,
IIf([CurrentAge]<-30,"TGY Becoming A Parent",IIf([CurrentAge] Between -30
And
-15,"TGY Getting Ready",IIf([CurrentAge] Between -14 And 15,"TGY
Newborn",IIf([CurrentAge] Between 15 And 45,"TGY Months 1 &
2",IIf([CurrentAge] Between 46 And 75,"TGY Months 2 & 3",IIf([CurrentAge]
Between 76 And 105,"TGY Months 3 & 4",IIf([CurrentAge] Between 106 And
135,"TGY Months 4 & 5"))))))) AS Packet
FROM tblClients;