By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Access - Split Memo Field into multiple rows

P: 13

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.


Dec 18 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. Debug.Print Mid("YourAUSMemoFieldToPullAUSFrom", InStr("YourAUSMemoFieldToPullAUSFrom", "AUS"), InStr(InStr("YourAUSMemoFieldToPullAUSFrom", "AUS") + 1, "YourAUSMemoFieldToPullAUSFrom", "AUS") - InStr("YourAUSMemoFieldToPullAUSFrom", "AUS"))
Expand|Select|Wrap|Line Numbers
  1. Dim i As Integer
  2. i = InStr("YourAUSMemoFieldToPullAUSFrom", "AUS")
  3. Debug.Print Mid("YourAUSMemoFieldToPullAUSFrom", i, InStr(i + 1, "YourAUSMemoFieldToPullAUSFrom", "AUS") - i)
Hope this helps.
Dec 21 '07 #2

P: 10
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)]?
Dec 22 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.