Access - Split Memo Field into multiple rows

[AdrianJ1980]

Hi,

I apologise for not providing a code basis for my question, but i really am stuck.

Does anyone know how i can split a memo field, such as below, into separate records. (The data comes from the body of daily system generated emails that the database is linked to)

Single Field:
AUS 123 $450 $396 -4 N
AUS 456 $2560 $2860 300 N
AUS 789 $987 $2 -985 P

and so on....there can be up to 500 lines in each memo field, and a field for each day for the past couple of years. There are specific spaces between each value a carriage return at the end of each row.

What i ultimately need to do is separate each line into a different field, from which i can split using instr and mid etc.

Unfortunately some other software is not an option due to the system controls in place by my employer.

I'm not expecting a solution, but a point in the right direction would be greatly appreciated. I've already spent a day on it, so even if someone says not possible, that would be excellent.

Thanks

Adrian

[Denburt]

Without any code it is difficult for us to know what you are having difficulty with. You seem to have some knowledge of the instr() function so I am not sure this will help but hey I am throwing it out here.

Debug.Print Mid("YourAUSMemoFieldToPullAUSFrom", InStr("YourAUSMemoFieldToPullAUSFrom", "AUS"), InStr(InStr("YourAUSMemoFieldToPullAUSFrom", "AUS") + 1, "YourAUSMemoFieldToPullAUSFrom", "AUS") - InStr("YourAUSMemoFieldToPullAUSFrom", "AUS"))
 

Or:

Dim i As Integer
i = InStr("YourAUSMemoFieldToPullAUSFrom", "AUS")
Debug.Print Mid("YourAUSMemoFieldToPullAUSFrom", i, InStr(i + 1, "YourAUSMemoFieldToPullAUSFrom", "AUS") - i)
 

Hope this helps.

[brightshadow]

If the field is only delimited by spaces and CRs, then shouldn't you be able to just take the entire memo field, split it by the CRs [Chr(13)] and then split the results by the spaces [Chr(32)]?