Parsing field into two fields

[Lewis]

For future reference, someone please tell me how to search these forums so I can avoid wasting your time explaining something for the nth time. I tried the Custom Search at the upper right, but it seemed to navigate away from the forum. So, short of that knowledge I'm posting the following...



Access2007 WinXP...
Data comes to me in the form of a concatenated textdate field like this...

Red11/11/2012
White4/20/2011
Blue8/3/2012

As you can see, there are variable lengths for both text and date, but I need a way to parse them into two values with a query. I understand Left, Right, Mid, Trim, etc, but I don't know all the symbols to identify text and numeric in the string. I've searched online for the answers to other parsing questions using these in the formula, with success. Clearly while I'm using a model from someones example to solve my specific problem, I'm missing some of the logic to resolve this one. It's my own fault for not examining the formulas to full understanding, but at some point it makes my head spin trying to keep it all straight. So, I come crawling to you to help with this single problem. I will put the formula in a query, but I'm not sure what the syntax is to signal where the string splits.

For simplicity's sake, assume the field name is ConcNameDate and the fields I need are Name and Date1. It would be great to get some help here. TIA

L

[June7]

No intrinsic function to find digits buried in a string. Need custom function to find position of first digit then parse at that position.
Google: vba first integer in string
Review
http://stackoverflow.com/questions/3...igit-in-string
http://bytes.com/topic/access/answer...-number-string

In example x represents the ConcNameDate field.

Name: Right(x, InStr(x, GetPosition(x))-1)

Date1: CDate(Mid(x, InStr(x, GetPosition(x))))

[Lewis]

Ok, I can see it. I was trying to avoid the vba solution because it would fit so conveniently in my query.

What if the date part was always "today"? I only expressed it as variable length because come Jan 1st it would lose a digit. Or, what if I had a table of the Name part (which I do). Is there a way to parse that?

If not, I'll go the vba route.

Thanks for the reply.
L

[June7]

Okay, that offers consistency. I can work with that.

Left(x, InStr(x, Date())-1)

How many records are involved? This query using the NamePart table works with the small data sample you posted.
Table1 is table with NameDate value and Table2 has the NamePart.
SELECT Table1.NameDate, Table2.NamePart, IIf(InStr([NameDate],[NamePart])>0,[NamePart],Null) AS [Name] FROM Table1, Table2;

BTW, use the Advanced Search to search within forum. Single Content Type search seems to work best.

[Lewis]

The Name part table currently has 120 records, which while can grow, will likely never exceed 150. I get the NameDate part from the website, by Ctrl-A, pasting to a text file, which is linked to the database. When linked, the file has approximately 500 records. However, because I can identify the position of the portion I need (NameDate) I can actually reduce it to a single record for the sake of parsing it.

I'm assuming that the number of records involved here is relatively very small and the above solution will produce fruit. I'll try it in the next couple hours. Thank you very much for your time on this, greatly appreciated.
L
oh, and thanks for the "advanced search" tip. I was looking for a text box to click in (I may be myopic ;-)

[Lewis]

Yes, this worked very nicely...

SELECT Table1.NameDate, Table2.NamePart, IIf(InStr([NameDate],[NamePart])>0,[NamePart],Null) AS [Name] FROM Table1, Table2;

It produced 121 records, but adding "is not null" reduced it to 1.

Thank you very much for the help. Without this solution, there was occasion to have bad data introduced, which required manual correction. With this solution, it is no longer possible for the errors. Thanks again!!!