separate strings

  • Thread starter Thread starter Lydia
  • Start date Start date
L

Lydia

Hi,

I am importing data stored as text string file in an excel file. I have
trouble retrieving this field: "COVERAGE CODE ""E"", EMISSIONS WARRANTY
CVERED TO 100,026 MILES ACCORDING TO VIS AND SI", This is a whole string and
supposed to go into one field.

When my procedure finds " at the beginning of the string, I will try to find
", as the ending postion of the string. However in this case, it searches
until ""E"", Is there a way to make sure I can find all the way through the
end of the string, which is SI", ?

I construct my search string as strQuoteComma=chr$(34) & chr$(44)

Is there a way that I can construct the search string so that only either a
letter or a digit together with ", is found?


Thanks.

Lydia
 
Lydia,

Try searching backward through the string. Here's a function that begins at
the next to last character of the input string and steps backward while
looking for the QuoteComma.



Function fnSearch(strInput as String) as Integer

Dim strQuoteComma as String
Dim x as Integer

strQuoteComma=chr$(34) & chr$(44)

For x = Len(strInput) - 1 To 1 Step -1
If Mid(strInput, x, 2) = strQuoteComma Then
fnSearch = x + 1 'The comma is second character in our
string.
Exit Function
End If

Next x

'If we are here, quote/comma not found
strQuoteComma = 0


End Function
 
Scott,

Thanks for your reply. It is an idea.

Sorry I didn't make myself very clear. The string I showed in my previous
email is only a part of a very long string, which I didn't paste here, and
there may be more ", in fields yet to follow. So the search backward function
won't return the right position of ", for this part of text string.

Do you have more ideas?

Thanks.

Yingzi
--
Access/VB Programmer



Scott Lichtenberg said:
Lydia,

Try searching backward through the string. Here's a function that begins at
the next to last character of the input string and steps backward while
looking for the QuoteComma.



Function fnSearch(strInput as String) as Integer

Dim strQuoteComma as String
Dim x as Integer

strQuoteComma=chr$(34) & chr$(44)

For x = Len(strInput) - 1 To 1 Step -1
If Mid(strInput, x, 2) = strQuoteComma Then
fnSearch = x + 1 'The comma is second character in our
string.
Exit Function
End If

Next x

'If we are here, quote/comma not found
strQuoteComma = 0


End Function
Click to expand...
 
Hi Yingzi,

I'm having a problem figuring out the logical rule is that you use to break
apart your field. As the program reads through the input string, it is
looking for the quote/comma combination. When we find it, how do we know
whether or not to break the field? Once you define the rule, we can
probably figure out some code to do it.

Let me know.

Scott

Lydia said:
Scott,

Thanks for your reply. It is an idea.

Sorry I didn't make myself very clear. The string I showed in my previous
email is only a part of a very long string, which I didn't paste here, and
there may be more ", in fields yet to follow. So the search backward
function
won't return the right position of ", for this part of text string.

Do you have more ideas?

Thanks.

Yingzi
Click to expand...
 
Hi Lydia,

You are going to have to write some code that inspects the string one
character at a time, starting from the beginning and paying attention to what
came before. For example, if the current and prior characters are quote
symbols, then you need to convert both of them into one quote symbol, unless
the prior character was the starting quote delimiter. And if the character
is a comma, you need to know if you are in the middle of processing a string
or if you have completed processing a string.

Hope this helps,

Clifford Bass
 
Hi Scott,

Sorry I haven't gotten chance to reply your email. I pasted the whole
string. They are mostly separated by comma. The string I showed you before is
where I get trouble with.

I have no problem coding anything. I really need some idea and some simple
search function that returns the right ", not "",

I can use [a-z-0-9]", to do search in the table and get right result.
However I can't use it in Instr function. This is where I hope I can get some
help with.

Thanks.

Hopefully, I am clear with my explanation.


2007,1GKFK16367R207479,Yukon
XL,ARL,GMC,06/05/2006,06/30/2006,06/20/2008,07/18/2008,1,J6380,"SENSOR,
OXYGEN (CATALYST MONITORING) - REPLACE",,111,1-WG,SERVICE ENGINE
SOON,,,1-OB,OBDII Code
used,,44201,71134,005762,13-10253/00000158827,"PLATINUM CHEVROLET,
INC.",1,MT,US,(406)322-5222,EUR,0.00,27.81,0.00,0.00,27.81,1,"COVERAGE CODE
""E"", EMISSIONS WARRANTY CVERED TO 100,026 MILES ACCORDING TO VIS AND
SI",,,,,000000000000000000000000000000,ZREG_POL,733646144,25,LC9,FLEXIBLE
FUEL (GAS/ALC) 8 CYL 5.3L SFI ALUM CYL DEACTIVATION GM,M30,AUTO 4 SPD
HMD 4L60-E ELECTRONIC
 
Back
Top