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

Data from memo field

100+
P: 171
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
Feb 10 '08 #1
Share this Question
Share on Google+
5 Replies


mshmyob
Expert 100+
P: 903
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
Feb 10 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
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)>
Feb 10 '08 #3

mshmyob
Expert 100+
P: 903
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.

Expand|Select|Wrap|Line Numbers
  1. ' what line number do you want to search for
  2. vLine = 1
  3. ' get the starting position of the 1st occurance of the value stored in vLine
  4. vStart = InStr(1, txtMemo, vLine)
  5. ' figure out where the next line starts
  6. vEnd = InStr(1, txtMemo, vLine + 1)
  7. 'display the line number on the form
  8. Me.txtLine1 = Mid(txtMemo, vStart + 2, vEnd - vStart)
  9. 'same as above but for line 2    
  10. vLine = 2
  11. vStart = InStr(1, txtMemo, vLine)
  12. vEnd = InStr(1, txtMemo, vLine + 1)
  13. Me.txtLine2 = Mid(txtMemo, vStart + 2, vEnd - vStart)
  14.  
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.
Feb 10 '08 #4

100+
P: 171
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)
Feb 13 '08 #5

mshmyob
Expert 100+
P: 903
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.

Expand|Select|Wrap|Line Numbers
  1. ' get the 1st line
  2. VStart = InStr(1, txtMemo, "(")
  3. ' figure out where the line ends by looking for a CLF
  4. Vend = InStr(1, txtMemo, Chr(10))
  5. 'display the line number on the form
  6. Me.txtLine1 = Mid(txtMemo, VStart, Vend - VStart)
  7.  
  8. ' this is sample to get line 2
  9. VStart = Vend + 1
  10. ' figure out where the line ends by looking for a CLF
  11. Vend = InStr(VStart, txtMemo, Chr(10))
  12. Me.txtLine2 = Mid(txtMemo, VStart, Vend - VStart)
  13.  
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)
Feb 13 '08 #6

Post your reply

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