Seperate the parts of a text

[patnick]

Hi,
I have a table, t_Expr.Data

25/06/2009 Jefferson Marc Inc 24/06/2010 35.00 Cash
25/06/2009 Equitable Motors 24/06/2010 10,000.00 Cash
25/06/2012 General Indian 24/06/2010 35.00 Cash
25/06/2014 Lincoln Trailer Ins 24/06/2010 20,000.00 Cash
25/06/2015 Lincoln Trailer 24/06/2010 10,000.00 Cash

I am looking for a query to seperate the information in different colums of the query:

A: lef([t_Expr.Data];10)
B: ? (should return the names)
C: ? (should return the dates)

Could you please help!
Many thanks in advance!

Patnick

 

[namliam]

look into the MID function as well as the RIGHT and perhaps LEN functions too

Thos will save your day.

 

[JPearson]

It will be tricky since it is not consistant but the best bet is to find something consistant in the rest of the string and work backwards.

At least currently you can use the 2010 as a marker point. You know if you find 2010 inthe string then you can go back 6 spots and thats the begining of the date/end of the name. This can get you the end point for the Mid function on a dynamic level for the name.

The 2010 can also be used to refence the start point for the $ value and use the same method for the . for the end +2 characters.

With that end you can get the final section of payment type.

I believe the InStr function will help here.

 

[boblarson]

Is this imported data? I assume so based on its structure. If it is, why not import it properly to begin with? It looks like it might be possible using a tab delimited format.