Help with CDate & Expression type incorrectly error

[Guest]

Ihave the following expressions in my query. I trying to convert a stiring
date into a real date. format is 20050727

This one works fine.

LetDate: CDate(Left([PUBLIC_T_CONT_CRIT_DT].[ACTL_DT],4) & "/" &
Mid([PUBLIC_T_CONT_CRIT_DT].[ACTL_DT],5,2) & "/" &
Right([PUBLIC_T_CONT_CRIT_DT].[ACTL_DT],2))

This one returns an error. Does anyone know why? I've tried typing fied
names in uppercase as in the first expression.


TMBG: CDate(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))

 

[Allen Browne]

To avoid the problems you describe (and the regional settings problems), use
DateSerial() after parsing the 3 parts from the date:

LetDate: DateSerial(Left([ACTL_DT],4),
Mid([ACTL_DT],5,2), Right([ACTL_DT],2))

 

[Guest]

Allen I tried DateSerial without success.

I found my problem. At least I think. I noticed that the data being returned
for the first expression had no blank fileds where for expression two blanks
fields where being returned. I added a IIF statement and it works. I will try
the DateSerical once again. I would perfer using it so I want have problems
with system date. thanks for your help.

To avoid the problems you describe (and the regional settings problems), use
DateSerial() after parsing the 3 parts from the date:

LetDate: DateSerial(Left([ACTL_DT],4),
Mid([ACTL_DT],5,2), Right([ACTL_DT],2))

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

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

Ihave the following expressions in my query. I trying to convert a stiring
date into a real date. format is 20050727

This one works fine.

LetDate: CDate(Left([PUBLIC_T_CONT_CRIT_DT].[ACTL_DT],4) & "/" &
Mid([PUBLIC_T_CONT_CRIT_DT].[ACTL_DT],5,2) & "/" &
Right([PUBLIC_T_CONT_CRIT_DT].[ACTL_DT],2))

This one returns an error. Does anyone know why? I've tried typing fied
names in uppercase as in the first expression.


TMBG: CDate(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))