Repeat last value on an Empty Record

[DougChas]

Multiple records are returned with each API call. Occasionally a sub-record will be returned empty. If in Record #1 the city name is "New York" and in Record #2 the city name is an empty record < empty record >, "New York" will be returned again as the city name for Record #2.

Json("myData")(Index)("placeOfPerformance")("city")("name"))

sHTTP = "https://api.website.name?limit=1000&api_key=" & MYKEY & "&postedFrom=" & FirstDATE "&postedTo=" & LastDATE Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1") MyRequest.Open "GET", sHTTP MyRequest.Send Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)

[houghtonap]

A few comments about your example code:

1. VBA-JSON uses two different VBA objects to describe JSON Objects and JSON Arrays. It does not work like JavaScript where you can string references together. So your code Json("myData")(Index)("placeofPerformance")("city")("name") is most likely incorrect for what you think the JSON actually looks like in VBA-JSON.
   • See my comment in issue #193.
   • See my comment in issue #186.
2. You probably should read my comment in issue #221 about the async parameter, it is most likely applicable to WinHTTP. Your .Open request is not specifying the async parameter (optional) and it probably defaults to TRUE (async) rather than FALSE (sync) for WinHTTP. The Microsoft documentation for WinHTTP is unclear on the default, MSXML2.XMLHTTP is most likely is using WinHTTP under the hood... so add FALSE as your third parameter to WinHTTP, otherwise catch the async call to avoid race conditions.

[DougChas]

I tried using FALSE ( MyRequest.Open "GET", sHTTP, False ) and unfortunately the Easy button did not work. :-( I used an online parser ( https://jsonparser.org/ ) on today's data. Here is what it looks like.

Json("myData")(Index)("placeOfPerformance")("city")("name"))

Index = 4

################################## object {5} ... myData [11] ... 4 {27} ... placeOfPerformance {4} city {2} code : 00000 name : New York state {2} code : NY name : New York zip : 00000 country {2} code : USA name : UNITED STATES ... ##################################