Sort a Text Field Chronological

[Juan4412]

This may not be possible but thought I would ask. I have a field that is a text field (called UserInfo), and it shows a name and a date for example, Jose 08142012 then the next entry would be George 22211. However some entries may not actually have a date at the end they may just be a name. Is it possible for me to sort UserInfo chronologically so that all the dates are sorted in ascending order? I was thinking maybe to use an access function to strip out the date and place that in a field by itself and then sort that ascending, but IDK how to do that either! haha. All input is greatly appreciated.


EDIT:


I thought I would get creative and try:

Right(UserInfo, 6)

but now I see that sometimes there are hyphens or dashes in the date, so that throws my entire theory. Is there a way to return anything after a space?

[June7]

This is bad data structure. Is this data you receive periodically from external source? How many records are involved?

The two parts can be split.

To get the name part:
Left(UserInfo, InStr(UserInfo," "))

To get the date part:
Mid(UserInfo, InStr(UserInfo," ")+1)

Programmatically converting the date string to a date value would require complex VBA function because of the inconsistent structure.

[Juan4412]

It is a linked SQL Table into access there are about 40,000 records in the table, and yes it was received from an outside source. I was thinking adding a new field to the table with the two split so it would be easier to sort (and you just confirmed it). now as far as the query goes, it *should* return less than 1k results due to extra parameters being added into the query.

Also the Mid Function above works perfectly, thank you thank you!