Solved How to concatenate a Json String in Ms Access VBA

[nector]

I need to concatenate a Json string in Ms Access VBA so that its easier to read not what I have below:

Here is the pair requiring concatenating or shoetening:

"taxAmtRvat", Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'A'"), 4), 0) + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'B'"), 4), 0) + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'C1'"), 4), 0) + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'C2'"), 4), 0) + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'C3'"), 4), 0) + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'D'"), 4), 0) + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'RVAT'"), 4), 0) + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "And [TaxClassA] = 'E'"), 4), 0) + DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices)

 

[Josef P.]

Suggestion: Let's leave Json as a concept aside for now and just look at how to calculate the value in a readable way.

Initial situation written down in a more readable way:

Dim taxAmtRvatValue As Currency

taxAmtRvatValue = _
       Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And [TaxClassA] = 'A'"), 4), 0) _
     + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And [TaxClassA] = 'B'"), 4), 0) _
     + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & "´And [TaxClassA] = 'C1'"), 4), 0) _
     + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And [TaxClassA] = 'C2'"), 4), 0) _
     + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And [TaxClassA] = 'C3'"), 4), 0) _
     + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And [TaxClassA] = 'D'"), 4), 0) _
     + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And [TaxClassA] = 'RVAT'"), 4), 0) _
     + Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And [TaxClassA] = 'E'"), 4), 0) _
     + DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices)

Is the line with + DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices) correct?

Simplification:

Const TaxClassCriteria As String = "[TaxClassA] in ('A','B','C1','C2','C3','D','RVAT','E')"
Dim taxAmtRvatValue As Currency

taxAmtRvatValue = _
       Nz(Round(DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices & " And " & TaxClassCriteria), 4), 0) _
     + DSum("Taxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices)

Is that already clear enough?
Note: However, the result may not be exactly the same because only the sum of all filter-related values is rounded and not for each filter group.

OT Note: Round is mathematical and not commercial rounding, if you would need that.
Round(2.5,0) = 2 and not 3; Round(3.5, 0) = 4

 

[nector]

Thank you so much Josep I built up your Idea by assigning a variable to string and then use that variable in Json.