Prev: fuzzy matching help please in an Access 2003 query
Next: compare two tables for mismatch data
From: Paulo on 26 Mar 2010 09:41
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])),""),"")))
From: Daryl S on 26 Mar 2010 10:09
Paulo -

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

--
Daryl S


"Paulo" 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])),""),"")))
From: Paulo on 26 Mar 2010 10:48
I tried but it doesn't change the outcome. The query is still treating the
result as a string, not as a date.


"Daryl S" wrote:

> Paulo -
>
> In Query Design, select the entire column and then open the Properties
> dialog box. You can enter your format there.
>
> --
> Daryl S
>
>
> "Paulo" 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])),""),"")))
From: John Spencer on 26 Mar 2010 12:18
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

Paulo 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])),""),"")))
From: Daryl S on 26 Mar 2010 12:27
Paulo -

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

--
Daryl S


"Paulo" wrote:

> I tried but it doesn't change the outcome. The query is still treating the
> result as a string, not as a date.
>
>
> "Daryl S" wrote:
>
> > Paulo -
> >
> > In Query Design, select the entire column and then open the Properties
> > dialog box. You can enter your format there.
> >
> > --
> > Daryl S
> >
> >
> > "Paulo" 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])),""),"")))
 |  Next  |  Last
Pages: 1 2
Prev: fuzzy matching help please in an Access 2003 query
Next: compare two tables for mismatch data