Solved How to return a 0.0 in MS ACCESS VBA

N

nector

Member
Local time
Today, 20:57
Joined
Jan 21, 2020
Messages
455
I want to return a 0.0 if this co.de below has a zero value, the zero should not be a string but a number.

Code: 
Round(Nz(Me.txtclassC1, 0), 4)
 
0 and 0.0 are the same values, which to the computer is simply 0. So, if you want to see 0.0 on display, you could try using the Format property of the control where you're displaying it.
 
These are values required in Json , we have to comply
 
Understood. But the difference is that you have a computational number (because you used "Round" on it) that is true zero. It will never be anything else internally. But you can change the FORMAT of HOW you display that. The control wherein you show the value of that number can have a format string like #.# to assure a particular number of decimal places. You could also look into conditional formatting, perhaps.
 
nector said:
These are values required in Json , we have to comply
Click to expand...
You have a JSON output that requires that specific format? Does it apply to all values? I.e. 2.2 or 3.1 or 1222.3?
Or is it just that you want to format 0 the same way?

And how about even numbers like 4 or 12 or 9876? Do they also need to be formatted with a single decimal?
 
These are values required in Json , we have to comply
Click to expand...
When inserting/converting into the JSON string, you can format the number as you wish.

If you use the JsonConverter from Tim Hall:
github.com

VBA-JSON/JsonConverter.bas at master · VBA-tools/VBA-JSON

JSON conversion and parsing for VBA. Contribute to VBA-tools/VBA-JSON development by creating an account on GitHub.
github.com github.com

With special processing for the decimal numbers, something like this could be done:
Code: 
Dim JsonItems As Dictionary
Set JsonItems = New Dictionary

With JsonItems
   .Add "X", CInt(0)
   .Add "Y", CDbl(0)
End With

Debug.Print JsonConverter.ConvertToJson(JsonItems)
Result: {"X":0,"Y":0.0}
 
Last edited:
Sorry sir

This is not work see the amended cod below , and yes i'm using the module for Tim Hall:

Code: 
Public Sub CmdCwrite_Click()
Call CmdTotalClasses_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim Company As New Dictionary
Dim strData As String
Dim Json As Object
Dim data As New Dictionary
Dim transactions As Collection
Dim itemCount As Long
Dim i As Long
Dim n As Integer
Dim z As Integer
Dim item As New Dictionary
Dim items As New Collection
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
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 data = New Dictionary
Set transactions = New Collection
Set Company = New Dictionary
        Company.Add "tpin", rs!suptpin.Value
        Company.Add "bhfId", rs!bhfId.Value
        Company.Add "cisInvcNo", rs!cisInvcNo.Value
        Company.Add "orgInvcNo", Nz(rs!OrignalInvoiceNumber.Value, 0)
        Company.Add "custTpin", rs!TPIN.Value
        Company.Add "prcOrdCd", IIf((rs!NewprcOrdCd.Value = "0"), 0, "")
        Company.Add "custNm", rs!Company.Value
        Company.Add "salesTyCd", rs!SalesType.Value
        Company.Add "rcptTyCd", rs!DocCodes.Value
        Company.Add "pmtTyCd", rs!PaymentIDs.Value
        Company.Add "salesSttsCd", "02"
        Company.Add "cfmDt", rs!ActualDate.Value
        Company.Add "salesDt", Format((rs!ShipDate), "yyyymmdd")
        Company.Add "stockRlsDt", IIf(Len(rs!stockreleasing), rs!stockreleasing, Null)
        Company.Add "cnclReqDt", Null
        Company.Add "cnclDt", Null
        Company.Add "rfdDt", Null
        Company.Add "rfdRsnCd", rs!rfdRsnCding.Value
        Company.Add "totItemCnt", Me.txtinternalaudit
        Company.Add "taxblAmtA", IIf((Round(Nz(Me.txtclassA, 0), 2) = ""), CDbl(0), Round(Nz(Me.txtclassA, 0), 2))
        Company.Add "taxblAmtB", IIf((Round(Nz(Me.txtclassB, 0), 2) = ""), CDbl(0), Round(Nz(Me.txtclassB, 0), 2))
        Company.Add "taxblAmtC1", Round(Nz(Me.txtclassC1, 0), 2)
        Company.Add "taxblAmtC2", Round(Nz(Me.txtclassC2, 0), 2)
        Company.Add "taxblAmtC3", Round(Nz(Me.txtclassC3, 0), 2)
        Company.Add "taxblAmtD", Round(Nz(Me.txtclassD, 0), 2)
        Company.Add "taxblAmtRvat", Round(DSum("ReverseBasic", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "taxblAmtE", Round(DSum("Expenses", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "taxblAmtF", Round(DSum("TLTaxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "taxblAmtIpl1", CDbl(0)
        Company.Add "taxblAmtIpl2", CDbl(0)
        Company.Add "taxblAmtTl", IIf((Round(DSum("TLTaxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2) = ""), CDbl(0), Round(DSum("TLTaxable", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2))
        Company.Add "taxblAmtEcm", CDbl(0)
        Company.Add "taxblAmtExeeg", CDbl(0)
        Company.Add "taxblAmtTot", Round(DSum("TaxableTOT", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "taxRtA", 16
        Company.Add "taxRtB", 16
        Company.Add "taxRtC1", 0
        Company.Add "taxRtC2", 0
        Company.Add "taxRtC3", 0
        Company.Add "taxRtD", 0
        Company.Add "taxRtE", 0
        Company.Add "taxRtF", 10
        Company.Add "taxRtIpl1", 0
        Company.Add "taxRtIpl2", 0
        Company.Add "taxRtTl", 0
        Company.Add "taxRtEcm", 0
        Company.Add "taxRtExeeg", 0
        Company.Add "taxRtTot", 0
        Company.Add "taxRtRvat", 16
        Company.Add "taxAmtA", Round(Nz(Me.txtttaxclassA, 0), 2)
        Company.Add "taxAmtB", Round(Nz(Me.txtttaxclassb, 0), 2)
        Company.Add "taxAmtC1", 0
        Company.Add "taxAmtC2", 0
        Company.Add "taxAmtC3", 0
        Company.Add "taxAmtD", 0
        Company.Add "taxAmtE", 0
        Company.Add "taxAmtF", Round(DSum("ServiceCharge", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "taxAmtIpl1", 0
        Company.Add "taxAmtIpl2", 0
        Company.Add "taxAmtTl", Round(DSum("TLLevyTax", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "taxAmtEcm", 0
        Company.Add "taxAmtExeeg", 0
        Company.Add "taxAmtTot", 0
        Company.Add "taxAmtRvat", Round(DSum("ReversVAT", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "totTaxblAmt", Round(Nz(Me.txttotaxableAB, 0), 2)
        Company.Add "totTaxAmt", Round(Nz(Me.txttotaltaxAll, 0), 2) + Round(DSum("ServiceCharge", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2) + Round(DSum("TLLevyTax", "QryJson", "[InvoiceID] =" & Me.CboEsdInvoices), 2)
        Company.Add "totAmt", Round(Nz(Me.txtGrandtotal, 0), 2)
        Company.Add "prchrAcptcYn", rs!prchrAcptcYn.Value
        Company.Add "remark", rs!TheNotes.Value
        Company.Add "regrId", "11999"
        Company.Add "regrNm", rs!CreatedBy.Value
        Company.Add "modrId", "45678"
        Company.Add "modrNm", rs!CreatedBy.Value
        Company.Add "receipt", data
        data.Add "custTpin", rs!TPIN.Value
        data.Add "custMblNo", rs!Phone.Value
        data.Add "rptNo", 0
        data.Add "trdeNm", rs!Company.Value
        data.Add "adrs", rs!Address.Value
        data.Add "topMsg", ""
        data.Add "btmMsg", "Thank you for choosing us"
        data.Add "prchrAcptcYn", rs!prchrAcptcYn.Value
        Company.Add "itemList", transactions
             
    '--- loop over all the items
        itemCount = Me.txtinternalaudit
       
        For i = 1 To itemCount
            Set item = New Dictionary
            transactions.Add item
            item.Add "itemSeq", i
            item.Add "itemCd", rs!itemCd.Value
            item.Add "itemClsCd", rs!itemClsCd.Value
            item.Add "itemNm", rs!ProductName.Value
            item.Add "bcd", Null
            item.Add "pkgUnitCd", "NT"
            item.Add "pkg", 1
            item.Add "qtyUnitCd", "U"
            item.Add "qty", rs!Qty.Value
            item.Add "prc", rs!UnitPrice.Value
            item.Add "splyAmt", rs!Pricing.Value
            item.Add "dcRt", Round(rs!Discount.Value, 2)
            item.Add "dcAmt", Round(rs!Discamount.Value, 2)
            item.Add "isrccCd", Null
            item.Add "isrccNm", Null
            item.Add "isrcRt", Null
            item.Add "isrcAmt", Null
            item.Add "vatCatCd", IIf((rs!TaxClassA.Value = ""), "D", rs!TaxClassA.Value)
            item.Add "iplCatCd", "IPL1"
            item.Add "tlCatCd", "TL"
            item.Add "exciseCatCd", "EXEEG"
            item.Add "taxblAmt", Round(rs!SupplierAmount.Value, 2)
            item.Add "vatAmt", Round(rs!FinalTax.Value, 2) + Round(rs!ReversVAT.Value, 2) + Round(rs!TaxesExpenses.Value, 2)
            item.Add "iplAmt", Null
            item.Add "tlAmt", Round(rs!TLLevyTax.Value, 2)
            item.Add "exciseAmt", Null
            item.Add "totAmt", Round(rs!TotalAmount.Value, 2)

strData = JsonConverter.ConvertToJson(Company, Whitespace:=3)
rs.MoveNext
Next
Loop
n = FreeFile()
Open "C:\Users\necto\Desktop\Testing\test.txt" For Output As #n
Print #n, strData
Close #n



Actual results

Code: 
{
   "tpin": "1002623668",
   "bhfId": "000",
   "cisInvcNo": "CIS001-2692",
   "orgInvcNo": 0,
   "custTpin": "1002623668",
   "prcOrdCd": 0,
   "custNm": "INTER CITY BAKERY",
   "salesTyCd": "N",
   "rcptTyCd": "S",
   "pmtTyCd": "04",
   "salesSttsCd": "02",
   "cfmDt": "20240514205019",
   "salesDt": "20240514",
   "stockRlsDt": "20240514205019",
   "cnclReqDt": null,
   "cnclDt": null,
   "rfdDt": null,
   "rfdRsnCd": null,
   "totItemCnt": 1,
   "taxblAmtA": 100,
   "taxblAmtB": 0,
   "taxblAmtC1": 0,
   "taxblAmtC2": 0,
   "taxblAmtC3": 0,
   "taxblAmtD": 0,
   "taxblAmtRvat": 0,
   "taxblAmtE": 0,
   "taxblAmtF": 0,
   "taxblAmtIpl1": 0,
   "taxblAmtIpl2": 0,
   "taxblAmtTl": 0,
   "taxblAmtEcm": 0,
   "taxblAmtExeeg": 0,
   "taxblAmtTot": 0,
   "taxRtA": 16,
   "taxRtB": 16,
   "taxRtC1": 0,
   "taxRtC2": 0,
   "taxRtC3": 0,
   "taxRtD": 0,
   "taxRtE": 0,
   "taxRtF": 10,
   "taxRtIpl1": 0,
   "taxRtIpl2": 0,
   "taxRtTl": 0,
   "taxRtEcm": 0,
   "taxRtExeeg": 0,
   "taxRtTot": 0,
   "taxRtRvat": 16,
   "taxAmtA": 13.79,
   "taxAmtB": 0,
   "taxAmtC1": 0,
   "taxAmtC2": 0,
   "taxAmtC3": 0,
   "taxAmtD": 0,
   "taxAmtE": 0,
   "taxAmtF": 0,
   "taxAmtIpl1": 0,
   "taxAmtIpl2": 0,
   "taxAmtTl": 0,
   "taxAmtEcm": 0,
   "taxAmtExeeg": 0,
   "taxAmtTot": 0,
   "taxAmtRvat": 0,
   "totTaxblAmt": 100,
   "totTaxAmt": 13.79,
   "totAmt": 100,
   "prchrAcptcYn": "N",
   "remark": null,
   "regrId": "11999",
   "regrNm": "Admin Manager",
   "modrId": "45678",
   "modrNm": "Admin Manager",
   "receipt": {
      "custTpin": "1002623668",
      "custMblNo": "0977352401",
      "rptNo": 0,
      "trdeNm": "INTER CITY BAKERY",
      "adrs": "LUSAKA",
      "topMsg": "",
      "btmMsg": "Thank you for choosing us",
      "prchrAcptcYn": "N"
   },
   "itemList": [
      {
         "itemSeq": 1,
         "itemCd": "ZM2NTU00000013",
         "itemClsCd": "40000013",
         "itemNm": "PACKAGED WATER 1000 ML",
         "bcd": null,
         "pkgUnitCd": "NT",
         "pkg": 1,
         "qtyUnitCd": "U",
         "qty": 1,
         "prc": 100,
         "splyAmt": 100,
         "dcRt": 0,
         "dcAmt": 0,
         "isrccCd": null,
         "isrccNm": null,
         "isrcRt": null,
         "isrcAmt": null,
         "vatCatCd": "A",
         "iplCatCd": "IPL1",
         "tlCatCd": "TL",
         "exciseCatCd": "EXEEG",
         "taxblAmt": 100,
         "vatAmt": 13.79,
         "iplAmt": null,
         "tlAmt": 0,
         "exciseAmt": null,
         "totAmt": 100
      }
   ]
}


Even your code is not return 0.0 but just 0 , the requirement is to return 0.0


Json convertor.png
 
Last edited:
How did you adjust the highlighted lines in ConvertToJson function?
=>
Code: 
    Case VBA.vbInteger, VBA.vbLong
        ' Number (use decimals for numbers)
        ConvertToJson = VBA.Replace(JsonValue, ",", ".")
    Case VBA.vbSingle, VBA.vbDouble, VBA.vbCurrency, VBA.vbDecimal
        ConvertToJson = VBA.Replace(VBA.Format(JsonValue, "0.0"), ",", ".")
 
Code: 
Case VBA.vbInteger, VBA.vbLong
        ' Number (use decimals for numbers)
        ConvertToJson = VBA.Replace(JsonValue, ",", ".")
    Case VBA.vbSingle, VBA.vbDouble, VBA.vbCurrency, VBA.vbDecimal
        ConvertToJson = VBA.Replace(VBA.Format(JsonValue, "0.0"), ",", ".")

I'm now lost , please I just want field amount which has 0 to 0.0 a number not a string
 
Json is a text language. That means there are no objects so there is no numeric vs text value. It is all text. The difference is that actual text values are enclosed in double quotes whereas numeric values are not delimited. Therefore, to solve your problem, use the Format() function as you place the value into the Json string. I'm still not clear on your rules but it looks like 0 is the only case where you want to force a .0. So:
Code: 
If SomeNumericField = 0 Then
    JsonValue = "0.0"
Else
    JsonValue = SomeNumericField
End If
 
I just want field amount which has 0 to 0.0 a number not a string
Click to expand...
You must distinguish between the stored value/data type and the visual representation.
I thought you would want something like that in the JSON string: {"x":0.0}
0.0 is a number in JSON. If it were a string, it would look like this: {"x":"0.0"}
 
Code: 
If SomeNumericField = 0 Then
    JsonValue = "0.0"
Else
    JsonValue = SomeNumericField
End If

But the program keep on rejecting "0.0" it want only 0.0 no double quotes
 
Well somehow we solved this its now working okay, many thanks to your advise
 
You must log in or register to reply here.

Similar threads

N
Solved How to concatenate a Json String in Ms Access VBA
Replies
2
Views
289
nector
N
N
Solved Just seeking an independent check on line counting and referencing in MS Access VBA
Replies
7
Views
388
Josef P.
J
N
Solved Caching recordset in MS Access VBA
2
Replies
26
Views
1,647
tvanstiphout
T
N
Solved Big variation when sending data through Cloud SQL Server Vs MS Access
Replies
10
Views
628
Pat Hartman
P
N
Solved Progress meter for reports & queries in MS Access
Replies
16
Views
719
tvanstiphout
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom