How to show white space in arrays if data does not exist Ms access VBA Collection

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Dear All;
I need some help to construct a better VBA filter in Ms Access on the Arrays, I remember one helped me here, but unfortunately, I have misplaced the code. The objectives of the code are listed below:
(1)      If no data found in the record source, then both the object and value pair should disappear in the Json string (The string should show white space or blank ONLY) I do NOT want it to return either (null or “”) never.
(2)      If data is found in the record source, then both the object and value pair found should show in the Json string , example (
“States in Africa”,53

Open in new window

)

Current Code:

dict.Add fld.Name, rs.fields(fld.Name).value

Open in new window


Required amendments:

dict.Add fld.Name, iif(rs.fields(fld.Name).Value = Null,Me.remove, rs.fields(fld.Name).Value)

Open in new window


The problem here is that the me.remove doesn’t work! That is where I have a problem.
Your help will be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Just change your record source query. It should only return the data to be exported. Thus a simple

WHERE NOT IsNull(statesColumn)

Open in new window

should be sufficient.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial