Create Expression Using IIf, Or

[Guest]

I need to display 1 of 3 different dates in a "Due Date" field on a form.
I have an expression which shows me Group A Due Date if the Group is A and
Group B Due Date if the Group is B (these due dates are calculated controls).
I also need to include a Special due date, which is not calculated, but is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to include the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the Special Due Date
is different than the GroupADueDate or the GroupBDueDate, then display
Special Due Date", I don't get the Special Due Date or I get a weird date
(12/29/1899)!

What's wrong?
Thanks
LDB

 

[Douglas J. Steele]

It sounds to me like you should always be showing the Special Due Date!

Special Due Date is either going to be the same as [GROUPADUEDATE] or
different. If it's the same, it doesn't matter which date you show. If it's
different, you want to show Special Due Date.

 

[Guest]

In some cases the Special Due Date is blank, which would then require either
A or B due date. The problem too is that I'm using a query based on these
dates, so that the user can determine the due date(s) no matter what the
group or special due date is by entering parameters "Begin Date" and "End
Date". The user needs to retrieve only the records with due dates matching
the parameters, whether it's A, B, or Special.

Thanks

It sounds to me like you should always be showing the Special Due Date!

Special Due Date is either going to be the same as [GROUPADUEDATE] or
different. If it's the same, it doesn't matter which date you show. If it's
different, you want to show Special Due Date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to display 1 of 3 different dates in a "Due Date" field on a form.
I have an expression which shows me Group A Due Date if the Group is A and
Group B Due Date if the Group is B (these due dates are calculated
controls).
I also need to include a Special due date, which is not calculated, but is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to include the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the Special Due
Date
is different than the GroupADueDate or the GroupBDueDate, then display
Special Due Date", I don't get the Special Due Date or I get a weird date
(12/29/1899)!

What's wrong?
Thanks
LDB

 

[Douglas J. Steele]

Assuming that Special Due Date is a field in the underlying recordset, try:

=IIf([GROUP]="A",IIf([GROUPADUEDATE] <> Me.[Special Due Date], Me.[Special
Due Date], [GROUPADUEDATE]),IIf([GROUP]="B",IIf([GROUPBDUEDATE] <>
Me.[Special Due Date], Me.Special Due Date], [GROUPBDUEDATE[), ????))

I've left ???? because I didn't know what you wanted if it wasn't group A or
B.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In some cases the Special Due Date is blank, which would then require
either
A or B due date. The problem too is that I'm using a query based on these
dates, so that the user can determine the due date(s) no matter what the
group or special due date is by entering parameters "Begin Date" and "End
Date". The user needs to retrieve only the records with due dates
matching
the parameters, whether it's A, B, or Special.

Thanks

It sounds to me like you should always be showing the Special Due Date!

Special Due Date is either going to be the same as [GROUPADUEDATE] or
different. If it's the same, it doesn't matter which date you show. If
it's
different, you want to show Special Due Date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to display 1 of 3 different dates in a "Due Date" field on a
form.
I have an expression which shows me Group A Due Date if the Group is A
and
Group B Due Date if the Group is B (these due dates are calculated
controls).
I also need to include a Special due date, which is not calculated, but
is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to include the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the Special Due
Date
is different than the GroupADueDate or the GroupBDueDate, then display
Special Due Date", I don't get the Special Due Date or I get a weird
date
(12/29/1899)!

What's wrong?
Thanks
LDB

 

[Guest]

The Group is always either A or B, but the user can insert a special due
date, regardless of group type. In this case, I need for the user to be able
to select on the special due date in this field I'm trying to build. So that
if the name of the calculated field i'm creating with this expression isn
"DUE DATE", I need the expression to give the user the right due date - A, B,
or Special when they enter the earliest beginning date and the latest due
date for the orders due within the dates they specifiy.
Thanks

Assuming that Special Due Date is a field in the underlying recordset, try:

=IIf([GROUP]="A",IIf([GROUPADUEDATE] <> Me.[Special Due Date], Me.[Special
Due Date], [GROUPADUEDATE]),IIf([GROUP]="B",IIf([GROUPBDUEDATE] <>
Me.[Special Due Date], Me.Special Due Date], [GROUPBDUEDATE[), ????))

I've left ???? because I didn't know what you wanted if it wasn't group A or
B.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In some cases the Special Due Date is blank, which would then require
either
A or B due date. The problem too is that I'm using a query based on these
dates, so that the user can determine the due date(s) no matter what the
group or special due date is by entering parameters "Begin Date" and "End
Date". The user needs to retrieve only the records with due dates
matching
the parameters, whether it's A, B, or Special.

Thanks

It sounds to me like you should always be showing the Special Due Date!

Special Due Date is either going to be the same as [GROUPADUEDATE] or
different. If it's the same, it doesn't matter which date you show. If
it's
different, you want to show Special Due Date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to display 1 of 3 different dates in a "Due Date" field on a
form.
I have an expression which shows me Group A Due Date if the Group is A
and
Group B Due Date if the Group is B (these due dates are calculated
controls).
I also need to include a Special due date, which is not calculated, but
is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to include the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the Special Due
Date
is different than the GroupADueDate or the GroupBDueDate, then display
Special Due Date", I don't get the Special Due Date or I get a weird
date
(12/29/1899)!

What's wrong?
Thanks
LDB

 

[Douglas J. Steele]

I can't tell from what you're saying whether my suggested solution works for
you or not.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Group is always either A or B, but the user can insert a special due
date, regardless of group type. In this case, I need for the user to be
able
to select on the special due date in this field I'm trying to build. So
that
if the name of the calculated field i'm creating with this expression isn
"DUE DATE", I need the expression to give the user the right due date - A,
B,
or Special when they enter the earliest beginning date and the latest due
date for the orders due within the dates they specifiy.
Thanks

Assuming that Special Due Date is a field in the underlying recordset,
try:

=IIf([GROUP]="A",IIf([GROUPADUEDATE] <> Me.[Special Due Date],
Me.[Special
Due Date], [GROUPADUEDATE]),IIf([GROUP]="B",IIf([GROUPBDUEDATE] <>
Me.[Special Due Date], Me.Special Due Date], [GROUPBDUEDATE[), ????))

I've left ???? because I didn't know what you wanted if it wasn't group A
or
B.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In some cases the Special Due Date is blank, which would then require
either
A or B due date. The problem too is that I'm using a query based on
these
dates, so that the user can determine the due date(s) no matter what
the
group or special due date is by entering parameters "Begin Date" and
"End
Date". The user needs to retrieve only the records with due dates
matching
the parameters, whether it's A, B, or Special.

Thanks

:

It sounds to me like you should always be showing the Special Due
Date!

Special Due Date is either going to be the same as [GROUPADUEDATE] or
different. If it's the same, it doesn't matter which date you show. If
it's
different, you want to show Special Due Date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to display 1 of 3 different dates in a "Due Date" field on a
form.
I have an expression which shows me Group A Due Date if the Group is
A
and
Group B Due Date if the Group is B (these due dates are calculated
controls).
I also need to include a Special due date, which is not calculated,
but
is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to include
the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the Special
Due
Date
is different than the GroupADueDate or the GroupBDueDate, then
display
Special Due Date", I don't get the Special Due Date or I get a weird
date
(12/29/1899)!

What's wrong?
Thanks
LDB

 

[Guest]

The suggestion didn't work. There is no other group case besides A or B, so
the expression should only give the special due date if that date is
different from the group due dates.

Thanks

I can't tell from what you're saying whether my suggested solution works for
you or not.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Group is always either A or B, but the user can insert a special due
date, regardless of group type. In this case, I need for the user to be
able
to select on the special due date in this field I'm trying to build. So
that
if the name of the calculated field i'm creating with this expression isn
"DUE DATE", I need the expression to give the user the right due date - A,
B,
or Special when they enter the earliest beginning date and the latest due
date for the orders due within the dates they specifiy.
Thanks

Assuming that Special Due Date is a field in the underlying recordset,
try:

=IIf([GROUP]="A",IIf([GROUPADUEDATE] <> Me.[Special Due Date],
Me.[Special
Due Date], [GROUPADUEDATE]),IIf([GROUP]="B",IIf([GROUPBDUEDATE] <>
Me.[Special Due Date], Me.Special Due Date], [GROUPBDUEDATE[), ????))

I've left ???? because I didn't know what you wanted if it wasn't group A
or
B.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


In some cases the Special Due Date is blank, which would then require
either
A or B due date. The problem too is that I'm using a query based on
these
dates, so that the user can determine the due date(s) no matter what
the
group or special due date is by entering parameters "Begin Date" and
"End
Date". The user needs to retrieve only the records with due dates
matching
the parameters, whether it's A, B, or Special.

Thanks

:

It sounds to me like you should always be showing the Special Due
Date!

Special Due Date is either going to be the same as [GROUPADUEDATE] or
different. If it's the same, it doesn't matter which date you show. If
it's
different, you want to show Special Due Date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to display 1 of 3 different dates in a "Due Date" field on a
form.
I have an expression which shows me Group A Due Date if the Group is
A
and
Group B Due Date if the Group is B (these due dates are calculated
controls).
I also need to include a Special due date, which is not calculated,
but
is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to include
the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the Special
Due
Date
is different than the GroupADueDate or the GroupBDueDate, then
display
Special Due Date", I don't get the Special Due Date or I get a weird
date
(12/29/1899)!

What's wrong?
Thanks
LDB

 

[Guest]

Sorry, forgot to mention that the user is entering the special due date,
which we are trying to get away from - the group due dates are calculated and
do not allow any edits. In some cases the special due date is necessary, so
I have to leave it available for their use.

It sounds to me like you should always be showing the Special Due Date!

Special Due Date is either going to be the same as [GROUPADUEDATE] or
different. If it's the same, it doesn't matter which date you show. If it's
different, you want to show Special Due Date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to display 1 of 3 different dates in a "Due Date" field on a form.
I have an expression which shows me Group A Due Date if the Group is A and
Group B Due Date if the Group is B (these due dates are calculated
controls).
I also need to include a Special due date, which is not calculated, but is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to include the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the Special Due
Date
is different than the GroupADueDate or the GroupBDueDate, then display
Special Due Date", I don't get the Special Due Date or I get a weird date
(12/29/1899)!

What's wrong?
Thanks
LDB

 

[Douglas J. Steele]

What didn't work about it? Did you get a compile error or a runtime error?
If so, what was the error?

If you're saying you will never have anything other than A or B, there's
really no need to check for group B, so you could simplify to:

=IIf([GROUP]="A",IIf([GROUPADUEDATE] <> Me.[Special Due Date], Me.[Special
Due Date], [GROUPADUEDATE]),IIf([GROUPBDUEDATE] <> Me.[Special Due Date],
Me.[Special Due Date], [GROUPBDUEDATE[))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The suggestion didn't work. There is no other group case besides A or B,
so
the expression should only give the special due date if that date is
different from the group due dates.

Thanks

I can't tell from what you're saying whether my suggested solution works
for
you or not.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Group is always either A or B, but the user can insert a special
due
date, regardless of group type. In this case, I need for the user to
be
able
to select on the special due date in this field I'm trying to build.
So
that
if the name of the calculated field i'm creating with this expression
isn
"DUE DATE", I need the expression to give the user the right due date -
A,
B,
or Special when they enter the earliest beginning date and the latest
due
date for the orders due within the dates they specifiy.
Thanks


:

Assuming that Special Due Date is a field in the underlying recordset,
try:

=IIf([GROUP]="A",IIf([GROUPADUEDATE] <> Me.[Special Due Date],
Me.[Special
Due Date], [GROUPADUEDATE]),IIf([GROUP]="B",IIf([GROUPBDUEDATE] <>
Me.[Special Due Date], Me.Special Due Date], [GROUPBDUEDATE[), ????))

I've left ???? because I didn't know what you wanted if it wasn't
group A
or
B.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


In some cases the Special Due Date is blank, which would then
require
either
A or B due date. The problem too is that I'm using a query based on
these
dates, so that the user can determine the due date(s) no matter what
the
group or special due date is by entering parameters "Begin Date" and
"End
Date". The user needs to retrieve only the records with due dates
matching
the parameters, whether it's A, B, or Special.

Thanks

:

It sounds to me like you should always be showing the Special Due
Date!

Special Due Date is either going to be the same as [GROUPADUEDATE]
or
different. If it's the same, it doesn't matter which date you show.
If
it's
different, you want to show Special Due Date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to display 1 of 3 different dates in a "Due Date" field on
a
form.
I have an expression which shows me Group A Due Date if the Group
is
A
and
Group B Due Date if the Group is B (these due dates are
calculated
controls).
I also need to include a Special due date, which is not
calculated,
but
is
an actual field in the underlying table.
Here's what I have so far, and it works fine until I try to
include
the
special due date:
=IIf([GROUP]="A",[GROUPADUEDATE],IIf([GROUP]="B",[GROUPBDUEDATE]))

When I try to include the Special due date to say "IIf the
Special
Due
Date
is different than the GroupADueDate or the GroupBDueDate, then
display
Special Due Date", I don't get the Special Due Date or I get a
weird
date
(12/29/1899)!

What's wrong?
Thanks
LDB