Prev: fuzzy matching help please in an Access 2003 query
Next: compare two tables for mismatch data
From: John W. Vinson on 26 Mar 2010 13:03
On Fri, 26 Mar 2010 06:41:01 -0700, Paulo <Paulo(a)discussions.microsoft.com>
wrote:

>I have the calculated field below in a query. I was expecting to get the
>results in the query (for this field) formatted as a date. But this is not
>happening. Can anyone explain why, and suggest solutions?
>
>Thanks in advance,
>
>Paulo
>
>
>Relevant_Date: IIf([Payment Terms]![Counting
>From]="Shipment",IIf(IsDate([Orders]![Ex-Factory2]),CDate(DateAdd("d",[Payment
>Terms]![Payment Days],[Orders]![Ex-Factory2])),""),IIf([Payment
>Terms]![Counting From]="Invoice",IIf(IsDate([Orders]![DC Received
>Date]),CDate(DateAdd("d",[Payment Terms]![Payment Days],[Orders]![ETA to
>DC])),""),IIf([Payment Terms]![Counting
>From]="Delivery",IIf(IsDate([Orders]![DC Received
>Date]),CDate(DateAdd("d",[Payment Terms]![Payment Days],[Orders]![DC Received
>Date])),""),"")))

If one of the values returned by an IIF is a text string - e.g. "" - then
Access will return all results as strings. You're also trying too hard -
DateAdd() already returns a date, so CDate(DateAdd()) is using honey as a
sause to sugar!

Try

Relevant_Date: IIf([Payment Terms]![Counting From] = "Shipment",
IIf(IsDate([Orders]![Ex-Factory2]),
DateAdd("d",[Payment Terms]![Payment Days], [Orders]![Ex-Factory2]),
Null),
IIf([Payment Terms]![Counting From]="Invoice",
IIf(IsDate([Orders]![DC Received Date]),
DateAdd("d",[Payment Terms]![Payment Days],[Orders]![ETA to DC]),Null),

and so on.
--

John W. Vinson [MVP]
First  |  Prev  | 
Pages: 1 2
Prev: fuzzy matching help please in an Access 2003 query
Next: compare two tables for mismatch data