CDATE Problem

[mattc66 via AccessMonster.com]

I have used the below in a query just fine. To format data that comes in text
from a legacy system. The text is formated YYMMDD.

cdate(mid(yymmdd,3,2) & "/" & right(yymmdd,2) & "/" & left(yymmdd,2))

What I would like to do is use the same syntax in a form's control source
using the Expression Builder. When I do that I get Error# in the box.

Help...

 

[Duane Hookom]

I would probably use:
=DateSerial(2000 + Val(Left([yymmdd],2)), Val(Mid([yymmdd],3,2)),
Val(Right([yymmdd],2)))

Make sure the name of the control is NOT the name of a field in the form's
record source.

 

[mattc66 via AccessMonster.com]

That worked, but what if the fld is empty? I am getting an #Error now if the
fld is empty. I tried using the IIF statement.

IIF ([yymmdd]=null,"",=DateSerial(2000 + Val(Left([yymmdd],2)), Val(Mid(
[yymmdd],3,2)), Val(Right([yymmdd],2))))

I would probably use:
=DateSerial(2000 + Val(Left([yymmdd],2)), Val(Mid([yymmdd],3,2)),
Val(Right([yymmdd],2)))

Make sure the name of the control is NOT the name of a field in the form's
record source.

I have used the below in a query just fine. To format data that comes in
text

[quoted text clipped - 6 lines]

Help...

 

[Duane Hookom]

What do you want displayed if the field is null? Keep in mind that testing
for Null by using "=Null" will not work. You can use
=IIf( IsNull(....), Null, ....)

=IIf(IsNull([yymmdd]),Null,DateSerial(2000 + Val(Left([yymmdd],2)),
Val(Mid([yymmdd],3,2)), Val(Right([yymmdd],2))))


--
Duane Hookom
MS Access MVP

That worked, but what if the fld is empty? I am getting an #Error now if
the
fld is empty. I tried using the IIF statement.

IIF ([yymmdd]=null,"",=DateSerial(2000 + Val(Left([yymmdd],2)), Val(Mid(
[yymmdd],3,2)), Val(Right([yymmdd],2))))

I would probably use:
=DateSerial(2000 + Val(Left([yymmdd],2)), Val(Mid([yymmdd],3,2)),
Val(Right([yymmdd],2)))

Make sure the name of the control is NOT the name of a field in the form's
record source.

I have used the below in a query just fine. To format data that comes in
text

[quoted text clipped - 6 lines]

Help...