parsing an imported field

[crgowo]

Before i start i just want to say this is my very first post ok....
I need help parsing a field up that only has a hard return between each data in the field I want to parse. I want to exported data form a “memo” field within a sybase database and I want to import into an access database. The problem we are having is that within the sybase system it automatically assigns a date to when the memo was created. But instead of creating a new memo for each entry they just append to the first memo related to the subject. Basically they could have multiple memos inside a memo. So they hit “enter” and type the date “space bar” then continue with their comment. Is there any way to parse a field that is on multiple lines because they hit enter or that has a hard return to use as the separator. thanks for your help.

 

[FoFa]

You should be able to use INSTR to find the Carraige return (chr(13)) and use a std parsing routine.

 

[crgowo]

im not to sure i know what you mean. Can you explain alittle more Ive never done a parse expression. Thanks

btw im using access 97

 

[The_Doc_Man]

OK, my old days as a teacher come to the fore yet again.

Your reading assignment in the Help files is to find the following topics: InStr & Mid & Len functions, and VB Constants. You will find that vbCR is a constant equivalent to a hard carriage return. vbLF is a line feed.

Mid is a function that you call to extract part of a string. You name the string, the starting point of extraction, and the length of extraction.

InStr is a function that you use to search within a string for a specific substring from a specific starting point. Parsing involves keeping track of where the last search started so your next search can begin where you left off.

Len is a function to count the number of characters in a string without regard to what those characters are.

You can use the InStr to find the first occurrence of vbCR (or vbLF). The first time, you start from character position 1. BUT... you get back a position from InStr. You can use this position and Mid function to extract a part of the string. The NEXT search should start 1 character beyond where the last one stopped, which is to say 1 more than the value returned from the InStr.

When computing the string length and position, just remember that string numbering starts at the left from 1 and a zero-length string is empty. So to extract the 4th character of a string, the position is 4 and the length is 1. But the stuff you get from InStr is two positions, both of which contain that 1-based numbering system. So if you compute the difference in positions between the starting point of the InStr scan and the returned value, you have to add 1.

Suppose you search and find that the second character is vbCR. To get everything from the start to the end of that implied line, you want to extract starting from 1 and with length 2 - even though the computed length APPEARS to be position 2 - position 1 = 1 character. Since both of them were 1-based, you took out too many 1s. So put 1 back.

OK, the last trick for this is that if the last fragment DOESN'T end with vbCR or vbLF, your scan needs to test for the case where InStr fails but the scan start isn't at the end of the string yet. This is why you need to know the Len function.

Now, the collect name for a process that decomposes a string into its parts this way is Parsing. Good luck!

 

[crgowo]

Im reading up on that now. Thanks for your help. Ill let you know how it went.