Data from memo field

[rajeevs]

Hi All
I have a table with one field as memo. This has so many lines of data.
I like to find data from a particular line (say n th line). Pls help with a function which i can use in the query. I have little knowledge about the instr.
But i dont know how to check the line number.
Thank you


Eg:

line1
line2
line3
line4
line5
line6 etc

[mshmyob]

You could use the instr or instrrev functions.

Memo fields are not really set up as line number but the above functions will allow you to search for a specific string.

But if you put numbers before each line of data you could then use the functions above to find the starting and ending points of each line and then extract the data.

Hi All
I have a table with one field as memo. This has so many lines of data.
I like to find data from a particular line (say n th line). Pls help with a function which i can use in the query. I have little knowledge about the instr.
But i dont know how to check the line number.
Thank you


Eg:

line1
line2
line3
line4
line5
line6 etc

[missinglinq]

Cardinal Rule of Memo Fields

Thy shall not enter any data that will ever need to be manipulated in any way!

And you've obviously violated this rule! Memo fields are designed to hold memos, i.e. notes, they are not designed simply as a place to cram bunches of data in so that you don't have to move from field-to-field when doing data entry. This is, I expect, going to be a mare's nest to sort out, if it can, in fact, be sorted out!

There's a few things we probably need to know to try to get you out of this:

1. What kind of data does this field hold, i.e. interview notes, recipes, driving directions?
2. Exactly how are you defining a "line" here? Do you mean a line as it appears when viewing your form, or have you done something during data entry to create a new line, such as hitting <Enter> or <Ctrl> + <Enter>?
3. Exactly what is this data you're trying to retrieve? Is there anything uniquely identifiable about the data, especially vis a vis how the line begins? Does it always start with the words "In the beginning" or "It was a dark and rainy night?" Does it always have a date in it, or a Currency mark?

Linq ;0)>

[mshmyob]

As a followup here is some code to extract data from line in a memo field.

First in my example I have a memo field that has multiple lines like so

1. This is line 1
2. This is line 2
3. This is line 3

Notice I need some kind of identifier to identify the start of each line. I would recommend you find something very UNIQUE (not what I used)

The code to extract line 1 and 2 is as follow. You can expand on it by using loops, case statements etc.

' what line number do you want to search for
vLine = 1
' get the starting position of the 1st occurance of the value stored in vLine
vStart = InStr(1, txtMemo, vLine)
' figure out where the next line starts
vEnd = InStr(1, txtMemo, vLine + 1)
'display the line number on the form
Me.txtLine1 = Mid(txtMemo, vStart + 2, vEnd - vStart)
'same as above but for line 2    
vLine = 2
vStart = InStr(1, txtMemo, vLine)
vEnd = InStr(1, txtMemo, vLine + 1)
Me.txtLine2 = Mid(txtMemo, vStart + 2, vEnd - vStart)
 

As you can see if searching for say Line 1 if the number 2 appears in line 1 then everything will be messed up. But it gives you an idea of how it can be done.

Hope this helps - please feel free to ask for more clarification.

[rajeevs]

Hi
I like to give more details about my query
The data is actually stored in the memo field of ms access table.
the data will look like below mentioned
always start and end with brackets
The lines starts with -
I want to extract certain data from this field and make it as one record with different fields
like field 1 as ALPHANUMERIC maximum 10 or 12
second field as data from the next line
third field as from next line like that
Hope it is clear pls help
Thanks in advance

(XXX-ALPHANUMERIC maximum 10 or 12/XX
-alphanumeric max 5/X-XXXXXXXX/X
-xxxxxxxx
-alphanumeric any number LIKEXXX 110 XXXDCXXXXXXV
XXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-xxxxxxxx XXXX XXXX
-XXX/XXXXXXXX XXXXXXXX ETC
XXXXXXXXXXXXXXX XXXX
-XXXXXXXXXXXXX
XXXXXXXXX
XXXXXXX)

[mshmyob]

Ok here is some sample code to find any line in a memo field regardless of line identifiers. The only requirement is that the end of each line has a CLF. Obviously you would redo this into a LOOP statement.

I tested it with the Memo data you supplied and it works fine. If you need any more clarification please do not hesitate to ask.

' get the 1st line
VStart = InStr(1, txtMemo, "(")
' figure out where the line ends by looking for a CLF
Vend = InStr(1, txtMemo, Chr(10))
'display the line number on the form
Me.txtLine1 = Mid(txtMemo, VStart, Vend - VStart)
 
' this is sample to get line 2
VStart = Vend + 1
' figure out where the line ends by looking for a CLF
Vend = InStr(VStart, txtMemo, Chr(10))
Me.txtLine2 = Mid(txtMemo, VStart, Vend - VStart)
 

Hi
I like to give more details about my query
The data is actually stored in the memo field of ms access table.
the data will look like below mentioned
always start and end with brackets
The lines starts with -
I want to extract certain data from this field and make it as one record with different fields
like field 1 as ALPHANUMERIC maximum 10 or 12
second field as data from the next line
third field as from next line like that
Hope it is clear pls help
Thanks in advance

(XXX-ALPHANUMERIC maximum 10 or 12/XX
-alphanumeric max 5/X-XXXXXXXX/X
-xxxxxxxx
-alphanumeric any number LIKEXXX 110 XXXDCXXXXXXV
XXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-xxxxxxxx XXXX XXXX
-XXX/XXXXXXXX XXXXXXXX ETC
XXXXXXXXXXXXXXX XXXX
-XXXXXXXXXXXXX
XXXXXXXXX
XXXXXXX)