Text to Date Format

[cbjames]

I have two fields; one for [Date] the other for [Time]. Unfortantly the
fields were imported as "TEXT" since there were all formsts of entries made
(i.e. &PM could be any of the following: 700, 7:00; 19:00, 7PM, etc...). We
now need to do some calculations of these two fields. I can not for the live
of me come up with code to convert multiple text entries to a true Date
Format. I am thinking the only answer is to manually re-key. Does nayone have
a "Magic" bullet??

 

[Jeff Boyce]

Access is somewhat forgiving in what it will accept and turn into a
Date/Time value. Have you tried creating a query on that field, and adding
another field something like:

AnotherField: CDate([YourTextDateField]

If that works, you might be able to concatenate the [TextDate] and the
[TextTime] into a single (query) field, then try the CDate() function on
that.

If all that works, you could then add a new field in your table definition
and use the above steps to come up with what you'd need to update with.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[rose]

I have two fields; one for [Date] the other for [Time]. Unfortantly the
fields were imported as "TEXT" since there were all formsts of entries
made
(i.e. &PM could be any of the following: 700, 7:00; 19:00, 7PM, etc...).
We
now need to do some calculations of these two fields. I can not for the
live
of me come up with code to convert multiple text entries to a true Date
Format. I am thinking the only answer is to manually re-key. Does nayone
have
a "Magic" bullet??

 

[cbjames]

CDate figure many of them, but some were just too weird. This did help a
bunch though. Thanks!!

Access is somewhat forgiving in what it will accept and turn into a
Date/Time value. Have you tried creating a query on that field, and adding
another field something like:

AnotherField: CDate([YourTextDateField]

If that works, you might be able to concatenate the [TextDate] and the
[TextTime] into a single (query) field, then try the CDate() function on
that.

If all that works, you could then add a new field in your table definition
and use the above steps to come up with what you'd need to update with.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two fields; one for [Date] the other for [Time]. Unfortantly the
fields were imported as "TEXT" since there were all formsts of entries
made
(i.e. &PM could be any of the following: 700, 7:00; 19:00, 7PM, etc...).
We
now need to do some calculations of these two fields. I can not for the
live
of me come up with code to convert multiple text entries to a true Date
Format. I am thinking the only answer is to manually re-key. Does nayone
have
a "Magic" bullet??