Instr() problem

K

ksteger

I am writing out the code in Microsoft Access to parse out each detail
line returned from an invoice query to QuickBooks. I have this same
code working just fine to parse out Sales Order detail lines but am
encountering a strange problem trying to parse out the Invoice detail
lines.

For Example:
A=any other XML Commands or data
X=<InvoiceLineRet>
Y=</InvoiceLineRet>
J=<Amount>

aaaaaaaaaaaaXaaaaaJaaaaaaYXaaaaaJaaaaaaYaaaaaaaaaa

Here are the positions checked the first time through the loop:
1st X = 1609
2nd X = 1957
1st J = 1842

After finding the first J I go through the loop the second time using
1847 as my starting point. I try to find the next X which is at
position 1957 and it can't find it this time. It found it fine the
first time through the loop though. The code is the exact same only the
starting position is different.

I am also posting the code with comments. The ones beginning with *****
are the ones stating the position and iteration through the loop. Can
anyone see why this code which works fine for sales orders is not
working for invoices.

BegPosition = 1
DoneSearch = "N"

Do Until DoneSearch = "Y"

'***********This point on the first time through the loop BegPosition
= 1 OK
'***********This point on the 2nd time through the loop BegPosition =
1842 OK
BegPosition = InStr(BegPosition, QBResponse, "<InvoiceLineRet>")
OrigBegPos = BegPosition
'***********This point on the first time through the loop BegPosition
= 1609 OK
'***********This point on the 2nd time through the loop BegPosition =
0 - ERROR
'***********The 2nd time through it should have found 1957 which is
'*********** equivelant to NextLineBegPos from the first time
through.

If BegPosition > 0 Then
BegPosition = BegPosition + 16
'***********This point on the first time through the loop BegPosition
= 1625 OK

NextLineBegPos = InStr(BegPosition, QBResponse, "<InvoiceLineRet>")
'***********This point on the first time through the loop
NextLineBegPos = 1957 OK

If Not (NextLineBegPos > 0) Then
NextLineBegPos = InStr(BegPosition, QBResponse, "</InvoiceLineRet>")
End If

' Parse out Line Item
BegPosition = InStr(BegPosition, QBResponse, "<ListID>")
BegPosition = BegPosition + 8
EndPosition = InStr(BegPosition, QBResponse, "</ListID>")
ElementLen = EndPosition - BegPosition
If ElementLen > 0 Then
ItemRefListID = Mid(QBResponse, BegPosition, ElementLen)
BegPosition = EndPosition + 9
End If
TempBegPos = BegPosition
TempEndPos = EndPosition


'Parse out Amount
BegPosition = InStr(BegPosition, QBResponse, "<Amount>")
BegPosition = BegPosition + 8
EndPosition = InStr(BegPosition, QBResponse, "</Amount>")
ElementLen = EndPosition - BegPosition
If BegPosition > NextLineBegPos Then
ItemAmount = "0.00"
BegPosition = TempEndPos
End If
If ElementLen > 0 Then
ItemAmount = Mid(QBResponse, BegPosition, ElementLen)
BegPosition = EndPosition + 9
End If
If BegPosition < TempBegPos Then
ItemAmount = "0.00"
BegPosition = OrigBegPos
End If
If ItemAmount = 0 Then
ItemAmount = "0.00"
End If

xml = "<?xml version=""1.0"" ?>"
xml = xml & "<?qbxml version=""3.0""?>"
xml = xml & "<QBXML>"
xml = xml & "<QBXMLMsgsRq onError=""continueOnError"">"
xml = xml & "<ItemQueryRq> "
xml = xml & "<ListID>" & ItemRefListID & "</ListID>"
xml = xml & "</ItemQueryRq>"
xml = xml & "</QBXMLMsgsRq>"
xml = xml & "</QBXML>"

QBResponse = qbxmlrp.ProcessRequest(ticket, xml)

'Parse out Item Type
BegPosition2 = 1
BegPosition2 = InStr(BegPosition2, QBResponse, "<AccountRef>")
BegPosition2 = InStr(BegPosition2, QBResponse, "<FullName>")
BegPosition2 = BegPosition2 + 10
EndPosition2 = InStr(BegPosition2, QBResponse, "</FullName>")
ElementLen2 = EndPosition2 - BegPosition2

'**********The first time through ItemType returned is Service which
is expected - OK
ItemType = Mid(QBResponse, BegPosition2, ElementLen2)

Else
DoneSearch = "Y"
End If
Loop


Thanks in advance for any and all input.

-Kim
 
Top