IIF Formula using dates

[Charess]

I would like to convert my birth dates into generation groups. Here is what I
have:

Expr2: IIf([DOB]>=#1/1/2003#,"Gen Z",IIf([DOB]>=#1/1/1984#,"Gen
Y",IIf([DOB]>=#1/1/1965#,"GenX",IIf([DOB]>=#1/1/1945#,"Baby
Boomers","Traditionalist"))))

It does not work.

Any advice?

Thank you.

 

[Jerry Whittle]

Consider if someone was born in 1944. They would be a member of all your
generations as your IIf statement is written. IIf's don't stop at the first
right answer; they keep going to the end. Therefore you need some Between
Date1 And Date2 statements which will get really uqly quick.

Look up Case statements in VBA Help. They are much easier to understand and
can be called upon in a query.

 

[Douglas J. Steele]

Sorry, I'm not sure you're right, Jerry. IIf statements do stop at the first
right answer. Remember, an IIf statement has three parts: a Boolean
Statement, what to return if the boolean statement is True and what to
return if the boolean statement is False. It can only return one value! Even
so, someone born in 1944 should fall out as "Traditionalist" since his/her
date of birth would be less than all of the constants in the equation.

Charess: what does "does not work" mean in this context? I copied-and-pasted
your code into a sample VBA function, and it worked fine.

That assumes that [DOB] is a Date field (as opposed to a date being stored
in a Text field). It also assumes that none of the DOB values are Null.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Consider if someone was born in 1944. They would be a member of all your
generations as your IIf statement is written. IIf's don't stop at the
first
right answer; they keep going to the end. Therefore you need some Between
Date1 And Date2 statements which will get really uqly quick.

Look up Case statements in VBA Help. They are much easier to understand
and
can be called upon in a query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I would like to convert my birth dates into generation groups. Here is
what I
have:

Expr2: IIf([DOB]>=#1/1/2003#,"Gen Z",IIf([DOB]>=#1/1/1984#,"Gen
Y",IIf([DOB]>=#1/1/1965#,"GenX",IIf([DOB]>=#1/1/1945#,"Baby
Boomers","Traditionalist"))))

It does not work.

Any advice?

Thank you.

 

[Charess]

I have this as a statment in a query and my query pulls of course from a
table. the control property is listed as text...maybe that is my problem. I
will change it to a date/time property and see if that works. Thank you.

Sorry, I'm not sure you're right, Jerry. IIf statements do stop at the first
right answer. Remember, an IIf statement has three parts: a Boolean
Statement, what to return if the boolean statement is True and what to
return if the boolean statement is False. It can only return one value! Even
so, someone born in 1944 should fall out as "Traditionalist" since his/her
date of birth would be less than all of the constants in the equation.

Charess: what does "does not work" mean in this context? I copied-and-pasted
your code into a sample VBA function, and it worked fine.

That assumes that [DOB] is a Date field (as opposed to a date being stored
in a Text field). It also assumes that none of the DOB values are Null.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Consider if someone was born in 1944. They would be a member of all your
generations as your IIf statement is written. IIf's don't stop at the
first
right answer; they keep going to the end. Therefore you need some Between
Date1 And Date2 statements which will get really uqly quick.

Look up Case statements in VBA Help. They are much easier to understand
and
can be called upon in a query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I would like to convert my birth dates into generation groups. Here is
what I
have:

Expr2: IIf([DOB]>=#1/1/2003#,"Gen Z",IIf([DOB]>=#1/1/1984#,"Gen
Y",IIf([DOB]>=#1/1/1965#,"GenX",IIf([DOB]>=#1/1/1945#,"Baby
Boomers","Traditionalist"))))

It does not work.

Any advice?

Thank you.

 

[Douglas J. Steele]

You can coerce your text dates into real dates using the CDate function:

Expr2: IIf(CDate([DOB])>=#1/1/2003#,"Gen
Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen
Y",IIf(CDate([DOB])>=#1/1/1965#,"GenX",IIf(CDate([DOB])>=#1/1/1945#,"Baby
Boomers","Traditionalist"))))

If there's a chance that DOB might be Null, try

Expr2: IIf(IsNull([DOB]), Null, IIf(CDate([DOB])>=#1/1/2003#,"Gen
Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen
Y",IIf(CDate([DOB])>=#1/1/1965#,"GenX",IIf(CDate([DOB])>=#1/1/1945#,"Baby
Boomers","Traditionalist")))))

Another possibility might be to use the Switch function:

Expr2: IIf(IsNull([DOB]), Null, Switch(CDate([DOB])>=#1/1/2003#,"Gen
Z",CDate([DOB])>=#1/1/1984#,"Gen
Y",CDate([DOB])>=#1/1/1965#,"GenX",CDate([DOB])>=#1/1/1945#,"Baby
Boomers",CDate([DOB])<#1/1/1945#"Traditionalist"))
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have this as a statment in a query and my query pulls of course from a
table. the control property is listed as text...maybe that is my problem.
I
will change it to a date/time property and see if that works. Thank you.

Sorry, I'm not sure you're right, Jerry. IIf statements do stop at the
first
right answer. Remember, an IIf statement has three parts: a Boolean
Statement, what to return if the boolean statement is True and what to
return if the boolean statement is False. It can only return one value!
Even
so, someone born in 1944 should fall out as "Traditionalist" since
his/her
date of birth would be less than all of the constants in the equation.

Charess: what does "does not work" mean in this context? I
copied-and-pasted
your code into a sample VBA function, and it worked fine.

That assumes that [DOB] is a Date field (as opposed to a date being
stored
in a Text field). It also assumes that none of the DOB values are Null.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Consider if someone was born in 1944. They would be a member of all
your
generations as your IIf statement is written. IIf's don't stop at the
first
right answer; they keep going to the end. Therefore you need some
Between
Date1 And Date2 statements which will get really uqly quick.

Look up Case statements in VBA Help. They are much easier to understand
and
can be called upon in a query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I would like to convert my birth dates into generation groups. Here is
what I
have:

Expr2: IIf([DOB]>=#1/1/2003#,"Gen Z",IIf([DOB]>=#1/1/1984#,"Gen
Y",IIf([DOB]>=#1/1/1965#,"GenX",IIf([DOB]>=#1/1/1945#,"Baby
Boomers","Traditionalist"))))

It does not work.

Any advice?

Thank you.