How to get blank or white space in VBA/Json

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 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


Customers-maintenance.pngTraining.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
[..] all I need to understand the concept which I can use for live and well-structured database.
I really doubt that, cause you didn't take any of my advice seriously. You're still mangling multiple things in single methods, making them unnecessary hard to understand and debug.

Your queries should return the data you want to export...


But when you want to stick to your "code", e.g.

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

  Dim Secta As String

  Secta = Nz(DLookup("Secta", "Qry1", "INV =" & Me.CboInv), "")
  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 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
         If Len(Trim(Secta)) > 0 Then
           poll.Add Secta
         End If
        Next fld

      coll.Add poll
      rs.MoveNext
    Loop
  End If

  rs.Close
  MsgBox JsonConverter.ConvertToJson(coll, Whitespace:=3)
  Set prm = Nothing
  Set qdf = Nothing
  Set fld = Nothing
  Set rs = Nothing
  Set db = Nothing
  Set poll = Nothing
  Set coll = Nothing

End Sub

Open in new window

btw, your DLookup() returns a invariant in relation to the loops, so you can call it once and reuse the value. The D-aggregate functions are pretty slow.
Thank you so much ste5an, that is what I was looking for.

Regards

Chris
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

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