How to use " " instead of null in Json/VBA format

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
In formatting   Json we are required Not to show null (But “ “) for any control that has empty value , how do we archive it using VBA code:

Private Sub CmdConertJson_Click()
  On Error GoTo Err_Handler
  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 root As Dictionary
  Set root = New Dictionary
    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim Tax As Collection
    Dim Z As Integer
    Dim i As Long
    Dim j As Long
    Dim t As Long
    Set transactions = New Collection
  Set db = CurrentDb
  Set qdf = db.QueryDefs("QryJson")
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)

Set qdf = Nothing
 rs.MoveFirst
    Do While Not rs.EOF
        Set transaction = New Dictionary
        transaction.Add "BuyerName", Me.BuyerName
        transaction.Add "BuyerTaxAccountName", Me.BuyerTaxAccountName

For example if Me.BuyerName has EMPTY the json should show “ “  NOT null
“BuyerName”, “ ”
“BuyerTaxAccountName”,” ”

Open in new window



Kindly help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
You could try using Chr(34) & Chr(34) which will return "".
transaction.Add "BuyerName", Iif(Me.BuyerName="", Chr(34) & Chr(34), Me.BuyerName)

Open in new window

its still returning the null value.

Regards

Chris
Here is what was required :

Nz(Me.BuyerName, "")

Open in new window


That works very well.

Regards

Chris

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