Dealing with json

[CJ_London]

your result here

? 63
> 62


10
< 60
x 120

indicated it is chr(10). So the split should be working

try this code

Dim strA() As String
 strA = Split(result, Chr(10)) 

msgbox ubound(strA) 'should be 1
msgbox strA(1) 'should be  "<xml><result>200 OK</result><credit>2114.0000</credit></xml>" tho' credit value may differ

'it would really help and save a lot of time if you provide the text of the error message - it would be something like 'syntax error - missing parameter'. As it is see the missing comma highlighted in red
if ubound(strA)=1 then msgbox iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1), instr(strA(1),"<Credit>")+8))) 'should be 2114 or whatever value

because of the val function, it will pick up any numeric value after <credit>

[Micron]

because of the val function, it will pick up any numeric value after <credit>

Val will stop as soon as it encounters the decimal point, no? Thus 2114.546 will be truncated to "2114"?

[control1]

Val will stop as soon as it encounters the decimal point, no? Thus 2114.546 will be truncated to "2114"?

That'll be fine, it will only ever be a full number anyway.

@Ajax - when I've included that code and ran it - the first message box returns "2" when you said it should be 1 - not sure why? The second message box returned <xml><result>200 OK</result><credit>2114.0000</credit></xml>as you expected

Cheers,
Leigh

[CJ_London]

Val will stop as soon as it encounters the decimal point

no because it is a valid 'numeric number'

in the immediate window

?val("345.23Abc")
345.23

but currency symbol are not valid so

?val("£345.23Abc")
0

@Control1

the first message box returns "2" when you said it should be 1 - not sure why?

just looked at the characters returned and there is a chr(10) right at the end so the third element (they start from 0) would be null. So modify your code from

if ubound(strA)=1 then.....

to

if ubound(strA)>=1 then.....

[Micron]

They lie!
https://www.techonthenet.com/access/...umeric/val.php

[CJ_London]

where do they lie? They say

For example:
Dim LNumber As Double

LNumber = Val ("56.2 tables")In this example, the variable called LNumber would now contain the value of 56.2.

[Micron]

Note
The Val function will stop reading the string once it encounters the first non-numeric character. This does not include spaces.

Underlining is my formatting.
(you read more of it than I)

[CJ_London]

didn't even see that bit - so inconsistent then

[Micron]

So that the code that you provided does work - unfortunately I can't test to see if it still works once the returned value is hundreds rather than thousands

Perhaps you can test as I did.

result = "<xml><result>200 OK</result><credit>215414.0000</credit></xml>"
startPos = InStr(1, result, "<credit>", 2) + 8
endPos = InStr(1, result, "</credit>", 2)
MsgBox Mid(result, startPos, (endPos - startPos))
I simply changed the bold value to several different values on both sides of the decimal and got what looked like the right answer every time. An explanation of what's going on should help.
startPos is the position wherever <credit> begins. +8 moves the start point to the first character after <credit> because <credit> is 8 characters long.
endPos is the position of wherever </credit> begins. Subtract start from end and you should get the length of what's in between
Mid(result, startPos, (endPos - startPos)) is a portion of result, from beginning at startPos and taking (endPos - startPos) characters.
For the value 215414.000, start is 37, end is 47; end - start is 10 thus msgbox = Mid(result, 37, 10). A debug.Print outputs 215414.000

Can you not test it against a copy of a table of historical data?
I haven't been following Ajax's method (no offence intended) so I can't begin to suggest which way I'd go.

[CJ_London]

I can't begin to suggest which way I'd go

both methods are valid - it's really up to the OP which way they want to go