Solved Getting the key and value from Json collection with VBA Microsoft Access (1 Viewer)

[nectorch]

I need help to get the value from a Json string using VBA MS Access, I'm able to get the first two line correct but failed on the last two lines , the problem is how to move into the collection

Private Sub Cmdgetweather_Click()
Dim Json As Object

Set Json = JsonConverter.ParseJson(GetWeather)
  'i = 1
  On Error Resume Next
 'Process data.
   
      Me.txtlongitude = Json("coord")("lon")
      Me.txtlatitude = Json("coord")("lat")
      Me.txtmain = Json("coord")("weather")("main")
      Me.txtdescription = Json("coord")("weather")("description")
   
Set Json = Nothing
End Sub

Here the raw Json data where I want to grab the last two pieces of information:

{"coord":{"lon":28,"lat":-15},"weather":[{"id":804,"main":"Clouds","description":"overcast clouds","icon":"04n"}],"base":"stations","main":{"temp":299.87,"feels_like":299.81,"temp_min":299.87,"temp_max":299.87,"pressure":1009,"humidity":40,"sea_level":1009,"grnd_level":887},"visibility":10000,"wind":{"speed":2.36,"deg":113,"gust":9.52},"clouds":{"all":95},"dt":1734888973,"sys":{"country":"ZM","sunrise":1734838572,"sunset":1734885427},"timezone":7200,"id":904264,"name":"Mungule Court","cod":200}

 

[nectorch]

I'm getting type missmatch here

 

[cheekybuddha]

At a guess, try:

Me.txtdescription = Json("weather")(0)("description")

 

[Edgar_]

I'm getting type missmatch here

If you visualize the JSON, you'll see that both weather and coord come from the root object. It should be:
Json("weather")(0)("main")

Similarly, if you want to get description, it has to be:
Json("weather")(0)("description")

Here's a view of your object:

asd

 

[nectorch]

It say out of range

 

[cheekybuddha]

Ok, try 1 instead of 0

 

[nector]

Many thanks Cheekybuddha your method has worked very well it's unbelievable.

Many thanks to you

 

[nector]

My apologies people I forgot the last field required as well, its called humidity

Me.txthumitity = Json("main")(1)("humidity")

This the last field required for the weather pattern, sorry to trouble you again

{
"coord": {
"lon": 28,
"lat": -15
},
"weather": [
{
"id": 803,
"main": "Clouds",
"description": "broken clouds",
"icon": "04d"
}
],
"base": "stations",
"main": {
"temp": 300.86,
"feels_like": 300.39,
"temp_min": 300.86,
"temp_max": 300.86,
"pressure": 1007,
"humidity": 37,
"sea_level": 1007,
"grnd_level": 885
},
"visibility": 10000,
"wind": {
"speed": 4.15,
"deg": 7,
"gust": 8.41
},
"clouds": {
"all": 83
},
"dt": 1734966997,
"sys": {
"country": "ZM",
"sunrise": 1734925002,
"sunset": 1734971855
},
"timezone": 7200,
"id": 904264,
"name": "Mungule Court",
"cod": 200
}

 

[cheekybuddha]

It's easier if you use indentation to visualise your JSON structure:

{
  "coord": {
    "lon": 28,
    "lat": -15
  },
  "weather": [
    {
      "id": 803,
      "main": "Clouds",
      "description": "broken clouds",
      "icon": "04d"
    }
  ],
  "base": "stations",
  "main": {
    "temp": 300.86,
    "feels_like": 300.39,
    "temp_min": 300.86,
    "temp_max": 300.86,
    "pressure": 1007,
    "humidity": 37,
    "sea_level": 1007,
    "grnd_level": 885
  },
  "visibility": 10000,
  "wind": {
    "speed": 4.15,
    "deg": 7,
    "gust": 8.41
  },
  "clouds": {
    "all": 83
  },
  "dt": 1734966997,
  "sys": {
    "country": "ZM",
    "sunrise": 1734925002,
    "sunset": 1734971855
  },
  "timezone": 7200,
  "id": 904264,
  "name": "Mungule Court",
  "cod": 200
}

So, humidity is a property of the 'main' property's value which is an object.

With the VBA JSON library you are using, you should be able to access it with:

Me.txtHumidity = Json("main")("humidity")

The reason you needed (1) to access the weather property's description value is because that property is an array of objects, and you must access each array member by index (even if there is only one array member as is the case here).

 

[nectorch]

cheekybuddha​

Many thanks to you, you clearly demonstrated professional knowledge in coding the sky is the limit for you

Many thanks