How to get blank or white space in VBA/Json

Dear All;

I need your help on how to return a white space if the collection has null string NOT “”,. I have tried using Nz function to archive that, but still nothing is working.
Please note the attached database is a fake one , all I need to understand the concept which I can use for live and well-structured database.

Option Compare Database
Option Explicit
Private Sub CmdSales_Click()

  Dim coll As VBA.Collection
  Dim poll As VBA.Collection
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Set db = CurrentDb
  Set qdf = db.QueryDefs("Qry1")
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()
Set qdf = Nothing
  Set coll = New VBA.Collection
  Set poll = New VBA.Collection
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
     For Each fld In rs.fields
      poll.Add Nz(DLookup("Secta", "Qry1", "INV =" & Me.CboInv), "")
        Next fld
         coll.Add poll
      rs.MoveNext
    Loop
  End If
  rs.Close
  Set fld = Nothing
  Set rs = Nothing
  Set db = Nothing
  MsgBox JsonConverter.ConvertToJson(coll, Whitespace:=3)
  Set coll = Nothing
End Sub

Open in new window

I do not want to see "" instead I need to an Empty space or blank

Training.accdb

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Thank you so much ste5an, that is what I was looking for.

Regards

Chris

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Once again thank you so much Ste5an for a clear solution , now my json string confirmed correct .

{
   "PosVendor": "Nector Prime Accounting Solutions",
   "PosSerialNumber": "745604789630",
   "IssueTime": "20200114055810",
   "TransactionTyp": 0,
   "PaymentMode": 0,
   "SaleType": "",
   "LocalPurchaseOrder": "",
   "Cashier": "Admin Manager",
   "BuyerTPIN": "",
   "BuyerName": "",
   "BuyerTaxAccountName": "",
   "BuyerAddress": "",
   "BuyerTel": "",
   "OriginalInvoiceCode": "",
   "OriginalInvoiceNumber": "",
   "Memo": "",
   "Items": [
      {
         "ItemID": 1,
         "Description": "Nshima",
         "BarCode": 74,
         "Quantity": 1,
         "UnitPrice": 27.5,
         "Discount": 0,
         "Taxable": [
            "A",
            "T"
         ],
         "Total": 31.9,
         "IsTaxInclusive": false,
         "RRP": 0
      },
      {
         "ItemID": 2,
         "Description": "Coffee",
         "BarCode": 76,
         "Quantity": 1,
         "UnitPrice": 0,
         "Discount": 0,
         "Taxable": [
            "A"
         ],
         "Total": 0,
         "IsTaxInclusive": true,
         "RRP": 0
      }
   ]
}

Open in new window