Help with dateAdd

M

Mike Johnson

I have the following expression on a query.

ProjFinDate: IIf([TMBG]=0,"",DateAdd("d",[PresentDays],[TMBG]))

I get the Expression typed wrong or to complex error. does any one have any
ideas. Thanks

Expression for TMBG.

TMBG:
IIf([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT]=0,"",DateSerial(Left([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT],4),Mid([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT],5,2),Right([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT],2)))

Expression for PresentDays.

PresentDays: [BID_DA]+[TOT_ADJ_DA]
 
A

Allen Browne

Your IIf() condition returns a zero-length string, which cannot be a date,
hence the wrong data type.

It will probably help to typecast the result as well, so:
CVDate(IIf([ACTL_DT]=0, Null, DateSerial(...

Depending on what else in happening in your query, you may need to repeat
the entire expression inside the DateAdd() instead of using TMBG there.
 
D

Dale Fye

Mike,

It looks, from your description, that you are defining the field [TMBG] in
the same query that you are trying to define the ProjFinDate. Is that
correct. If so, the easiest way to address this is to create an intermediate
query that defines [TMBG], then use that queryt as the data source for the
query that defines ProjFinDate

HTH
Dale
 
M

Mike Johnson

Thanks, All I had to was to change the "" in my TMBG expression to Null.
Thanks again. Sometimes all you need is another set of eyes and a fresh look.

Allen Browne said:
Your IIf() condition returns a zero-length string, which cannot be a date,
hence the wrong data type.

It will probably help to typecast the result as well, so:
CVDate(IIf([ACTL_DT]=0, Null, DateSerial(...

Depending on what else in happening in your query, you may need to repeat
the entire expression inside the DateAdd() instead of using TMBG there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike Johnson said:
I have the following expression on a query.

ProjFinDate: IIf([TMBG]=0,"",DateAdd("d",[PresentDays],[TMBG]))

I get the Expression typed wrong or to complex error. does any one have
any
ideas. Thanks

Expression for TMBG.

TMBG:
IIf([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT]=0,"",DateSerial(Left([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT],4),Mid([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT],5,2),Right([PUBLIC_T_CONT_KEY_DT_1].[ACTL_DT],2)))

Expression for PresentDays.

PresentDays: [BID_DA]+[TOT_ADJ_DA]
Click to expand...
Click to expand...
 
Top