Accessing the chars in a string

[KidSensation]

I have a date field in my table that comes in as text in the format
'4282007' and I need to be able to convert this string to an actual
date. Since it's not a valid date expression CDate() will not work on
it. So my thought was to insert '\'s into the strings and they will
then be valid. However, I'm finding it quite impossible to either use
string indexes to access individual chars or split the string into an
array. Is there anyway to do this in Access 2003? or should I just
look for a new way to do this? Thanks.

 

[Jason Lepack]

The problem I foresee is when you come across this:

2122007

What is this date?
2/12/2007
21/2/2007

Or is the date supposed to be 8 digits long and you've lost your
leading zeros?
If this is the case then you could use:
=IF(LEN(A1)=7,"0"&LEFT(A1,1) & "/" & MID(A1,2,2),LEFT(A1,2)& "/" &
MID(A1,3,2) )& "/" & RIGHT(A1,4)

Cheers,
Jason Lepack

 

[Jason Lepack]

I have a date field in my table that comes in as text in the format
'4282007' and I need to be able to convert this string to an actual
date. Since it's not a valid date expression CDate() will not work on
it. So my thought was to insert '\'s into the strings and they will
then be valid. However, I'm finding it quite impossible to either use
string indexes to access individual chars or split the string into an
array. Is there anyway to do this in Access 2003? or should I just
look for a new way to do this? Thanks.

Woops, I meant to use dashes instead of slashes.

 

[KidSensation]

They'll all be 8 long, I forgot to enter that. I'll try your code and
see what happens. Thanks.

 

[KidSensation]

The problem I foresee is when you come across this:

2122007

What is this date?
2/12/2007
21/2/2007

Or is the date supposed to be 8 digits long and you've lost your
leading zeros?
If this is the case then you could use:
=IF(LEN(A1)=7,"0"&LEFT(A1,1) & "/" & MID(A1,2,2),LEFT(A1,2)& "/" &
MID(A1,3,2) )& "/" & RIGHT(A1,4)

Cheers,
Jason Lepack



- Show quoted text -

Worked great...Thanks!

 

[Albert D. Kallal]

The following function should do the trick:

Public Function MyDate(v As Variant) As Date

If IsNull(v) Then Exit Function

If Len(v) = 8 Then
MyDate = DateSerial(Right(v, 4), Mid(3, 2), Left(v, 2))
End If

If Len(v) = 7 Then
MyDate = DateSerial(Right(v, 4), Mid(3, 2), Left(v, 1))
End If


End Function

If you paste the above into a standard code module, then you can use the
expression

=([YouTextfiedlName]) anywhere in the system to produce a valid date. You
can even build a update query..and send the results to a another date field.