|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 27th, 2006, 09:39 PM
posted to microsoft.public.access.queries
|
|||
|
|||
IFF One Date = Another Date
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]), “ ”) 
|
|
#2
December 27th, 2006, 10:58 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
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" wrote: 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]), “ ”)
|
|
#3
December 27th, 2006, 11:02 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
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],"") "Nanette" wrote: 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]), “ ”)
|
|
#4
December 27th, 2006, 11:28 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
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" wrote: 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" wrote: 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]), “ ”)
|
|
#5
December 27th, 2006, 11:39 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
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" wrote: 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],"") "Nanette" wrote: 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]), “ ”)
|
|
#6
December 27th, 2006, 11:57 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
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" wrote: 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" wrote: 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" wrote: 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]), “ ”)
|
|
#7
December 28th, 2006, 12:12 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
Are you just want the latest date, whatever it is, and it's label?
"Nanette" wrote: 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" wrote: 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],"") "Nanette" wrote: 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]), “ ”)
|
|
#8
December 28th, 2006, 12:55 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
Yes!!!
"KARL DEWEY" wrote: Are you just want the latest date, whatever it is, and it's label? "Nanette" wrote: 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" wrote: 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],"") "Nanette" wrote: 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]), “ ”)
|
|
#9
December 28th, 2006, 12:56 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
Thanks Duane,
I'll give it a try in the morning. I think my head is about to burst! "Duane Hookom" wrote: 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" wrote: 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" wrote: 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" wrote: 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]), “ ”)
|
|
#10
December 28th, 2006, 04:04 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
IFF One Date = Another Date
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" wrote: Yes!!! "KARL DEWEY" wrote: Are you just want the latest date, whatever it is, and it's label? "Nanette" wrote: 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" wrote: 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],"") "Nanette" wrote: 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]), “ ”)
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
