Querying a Memo Field

[jcbnc]

Hello. I am pretty new to Access and could really use some help.

I am in the process of converting an old 2003 Database to a 2010 Database and making some major modifications. In the old Quote table there is a field (memo) that has multiple line items (i.e. 1)....2).....3)....etc) and I wanted to know if there was an easy way to pull out each line item and make them a separate record? And if you could make your suggestions simple, that would be much appreciated

 

[John Big Booty]

If the items in your Memo field use a consistent identifier, you should be able to use some of the available text manipulation functions. Start by looking at Instr() and Mid() amongst others.

 

[jcbnc]

Most of the line items start with a number and then a ) (i.e. 1), 2)) Can you please provide me an example of how to use what you suggested above?

 

[Brianwarnock]

Although John is correct on the functions to use I don't believe that this can be achieved by queries but that code would need to be written, although I could do this for Excel I'm sorry but my record handling in Access VBa is not up to it these days.

Anybody writing this code would need to know
1) if there is only 1 item would it still be numbered
2) will there always be at least 1 item
3) the phrase "most of the line items" suggests an impossible task unless you can clarify

Brian

 

[jcbnc]

Thank you for your response. I am very limited in my coding skills, though very much willing to learn. See the response to your line items, below:

1) This response will cover both 1) and 3)...All the quotes have at least 1 line item, however, when there is only 1 it is not numbered like the rest of them are...which is OK because I can move those individually.

2) I do not need the numbers as the new form I am putting them in will have a field to capture which line item it is.

Any suggestions would be greatly appreciated.

jcbnc