Calculated Field in Query - Date Format

[Paulo]

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])),""),"")))

 

[Daryl S]

Paulo -

In Query Design, select the entire column and then open the Properties
dialog box. You can enter your format there.

 

[Paulo]

I tried but it doesn't change the outcome. The query is still treating the
result as a string, not as a date.

Paulo -

In Query Design, select the entire column and then open the Properties
dialog box. You can enter your format there.

--
Daryl S

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])),""),"")))

 

[John Spencer]

Replace "" with NULL. If you return (or potentially return) a string as any
of the results then all the results are typed as string. So if you want blank
return NULL and not a zero-length string.

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

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

Paulo -

Try changing the 'else' cases to null rather than "", which implies an empty
string.

--
Daryl S

I tried but it doesn't change the outcome. The query is still treating the
result as a string, not as a date.

Paulo -

In Query Design, select the entire column and then open the Properties
dialog box. You can enter your format there.

--
Daryl S

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])),""),"")))

 

[John W. Vinson]

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.