IFF One Date = Another Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been trying to get the below query to work. It only pulls a couple of
dates.

Any suggestions on how to fix it?

IIf(([PullDate] = [ReceiptDate]), IIf(([PullDate] = [RVPD]), IIf(([PullDate]
= [VPD]), IIf(([PullDate] = [PRDueDate]), IIf(([PullDate] = [ArrivalDate]),
[ArrivalDate]), [PRDueDate]), [VPD]), [RVPD]), [ReceiptDate]), “ â€)
 
Hi Duane,

The formula only displays 5 dates in the field rows. One is about 30 rows
down and the other four are about 700 rows down. All the other field rows are
blank.

Can problems occur when returning two differend data types from a single
expression

Duane Hookom said:
You might want to explain what you mean by "only pulls a couple of dates".
Also, your expression might return a date or might return a space. I try to
avoid returning two differend data types from a single expression. You might
want to replace " " with Null.

--
Duane Hookom
Microsoft Access MVP


Nanette said:
I've been trying to get the below query to work. It only pulls a couple of
dates.

Any suggestions on how to fix it?

IIf(([PullDate] = [ReceiptDate]), IIf(([PullDate] = [RVPD]), IIf(([PullDate]
= [VPD]), IIf(([PullDate] = [PRDueDate]), IIf(([PullDate] = [ArrivalDate]),
[ArrivalDate]), [PRDueDate]), [VPD]), [RVPD]), [ReceiptDate]), “ â€)
Click to expand...
Click to expand...
 
The logic in your IIf() is off. I think you want something like:
IIf([PullDate] = [ReceiptDate], [ReceiptDate], IIf([PullDate] = [RVPD],
[RVPD], IIf([PullDate] = [VPD], [VPD], IIf([PullDate] = [PRDueDate],
[PRDueDate], IIf([PullDate] = [ArrivalDate], [ArrivalDate], Null)))))

If this was the databasedesign NG, I would probably comment on normalizing
your table structure...
--
Duane Hookom
Microsoft Access MVP


Nanette said:
Hi Duane,

The formula only displays 5 dates in the field rows. One is about 30 rows
down and the other four are about 700 rows down. All the other field rows are
blank.

Can problems occur when returning two differend data types from a single
expression

Duane Hookom said:
You might want to explain what you mean by "only pulls a couple of dates".
Also, your expression might return a date or might return a space. I try to
avoid returning two differend data types from a single expression. You might
want to replace " " with Null.

--
Duane Hookom
Microsoft Access MVP


Nanette said:
I've been trying to get the below query to work. It only pulls a couple of
dates.

Any suggestions on how to fix it?

IIf(([PullDate] = [ReceiptDate]), IIf(([PullDate] = [RVPD]), IIf(([PullDate]
= [VPD]), IIf(([PullDate] = [PRDueDate]), IIf(([PullDate] = [ArrivalDate]),
[ArrivalDate]), [PRDueDate]), [VPD]), [RVPD]), [ReceiptDate]), “ â€)
Click to expand...
Click to expand...
Click to expand...
 
Yes!!!

KARL DEWEY said:
Are you just want the latest date, whatever it is, and it's label?

Nanette said:
Hi Karl,

I'm trying to determine which date is being used at the Pull Date (from this
I'll put it into a report, showing only specific dates from each row of
data-this query is based on another query). Does this make sense? The dates
are based on a specific order. First we receive an arrival date, then a
PRDueDate, then a VPD, then a RVPD and finally a DeliveryDate.

KARL DEWEY said:
What are you wanting for a result? Your query is checking all dates against
the PullDate] but what do you expect to get in the output? Cascading IIF's
will only result in one of two outputs - not all of the dates.
Maybe you want this --
Receipt: IIf(([PullDate] = [ReceiptDate]), [ReceiptDate],"")
RVPD : IIf(([PullDate] = [RVPD]),[RVPD],"")
VPD : IIf(([PullDate] = [VPD]), [VPD],"")
PR Due : IIf(([PullDate] = [PRDueDate]), [PRDueDate], "")
Arrival : IIf(([PullDate] = [ArrivalDate]), [ArrivalDate],"")


:

I've been trying to get the below query to work. It only pulls a couple of
dates.

Any suggestions on how to fix it?

IIf(([PullDate] = [ReceiptDate]), IIf(([PullDate] = [RVPD]), IIf(([PullDate]
= [VPD]), IIf(([PullDate] = [PRDueDate]), IIf(([PullDate] = [ArrivalDate]),
[ArrivalDate]), [PRDueDate]), [VPD]), [RVPD]), [ReceiptDate]), “ â€)
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane,

I'll give it a try in the morning. I think my head is about to burst!

Duane Hookom said:
The logic in your IIf() is off. I think you want something like:
IIf([PullDate] = [ReceiptDate], [ReceiptDate], IIf([PullDate] = [RVPD],
[RVPD], IIf([PullDate] = [VPD], [VPD], IIf([PullDate] = [PRDueDate],
[PRDueDate], IIf([PullDate] = [ArrivalDate], [ArrivalDate], Null)))))

If this was the databasedesign NG, I would probably comment on normalizing
your table structure...
--
Duane Hookom
Microsoft Access MVP


Nanette said:
Hi Duane,

The formula only displays 5 dates in the field rows. One is about 30 rows
down and the other four are about 700 rows down. All the other field rows are
blank.

Can problems occur when returning two differend data types from a single
expression

Duane Hookom said:
You might want to explain what you mean by "only pulls a couple of dates".
Also, your expression might return a date or might return a space. I try to
avoid returning two differend data types from a single expression. You might
want to replace " " with Null.

--
Duane Hookom
Microsoft Access MVP


:

I've been trying to get the below query to work. It only pulls a couple of
dates.

Any suggestions on how to fix it?

IIf(([PullDate] = [ReceiptDate]), IIf(([PullDate] = [RVPD]), IIf(([PullDate]
= [VPD]), IIf(([PullDate] = [PRDueDate]), IIf(([PullDate] = [ArrivalDate]),
[ArrivalDate]), [PRDueDate]), [VPD]), [RVPD]), [ReceiptDate]), “ â€)
Click to expand...
Click to expand...
Click to expand...
 
Yes!!! this confirms my comment "If this was the databasedesign NG, I would
probably comment on normalizing your table structure..."

--
Duane Hookom
Microsoft Access MVP


Nanette said:
Yes!!!

KARL DEWEY said:
Are you just want the latest date, whatever it is, and it's label?

Nanette said:
Hi Karl,

I'm trying to determine which date is being used at the Pull Date (from this
I'll put it into a report, showing only specific dates from each row of
data-this query is based on another query). Does this make sense? The dates
are based on a specific order. First we receive an arrival date, then a
PRDueDate, then a VPD, then a RVPD and finally a DeliveryDate.

:

What are you wanting for a result? Your query is checking all dates against
the PullDate] but what do you expect to get in the output? Cascading IIF's
will only result in one of two outputs - not all of the dates.
Maybe you want this --
Receipt: IIf(([PullDate] = [ReceiptDate]), [ReceiptDate],"")
RVPD : IIf(([PullDate] = [RVPD]),[RVPD],"")
VPD : IIf(([PullDate] = [VPD]), [VPD],"")
PR Due : IIf(([PullDate] = [PRDueDate]), [PRDueDate], "")
Arrival : IIf(([PullDate] = [ArrivalDate]), [ArrivalDate],"")


:

I've been trying to get the below query to work. It only pulls a couple of
dates.

Any suggestions on how to fix it?

IIf(([PullDate] = [ReceiptDate]), IIf(([PullDate] = [RVPD]), IIf(([PullDate]
= [VPD]), IIf(([PullDate] = [PRDueDate]), IIf(([PullDate] = [ArrivalDate]),
[ArrivalDate]), [PRDueDate]), [VPD]), [RVPD]), [ReceiptDate]), “ â€)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top