error 94 when passing the sum of a query

[megatronixs]

Hi all,

I have some code to get the sum results from a table that I want to pass as a variable in the rest of the code.
I have the trouble that when there is a 0 because the query can't sum null values.

    'run query to get the results to sum up later
    DoCmd.SetWarnings False
        DoCmd.OpenQuery "qry_profile_1_1"
    DoCmd.SetWarnings True
    
    Set rsProfile2 = CurrentDb.OpenRecordset("tbl_profile_1_1")
    'Open connection to current Access database
    Set db = CurrentDb()
    'Create SQL statement to retrieve value from tbl_profile_1_1 table
    LSQL = "SELECT Sum(tbl_profile_1_1.CountOfORDERING_INST_NAME) AS SumOfCountOfORDERING_INST_NAME FROM tbl_profile_1_1;"
    Set Lrs = db.OpenRecordset(LSQL)
    LGST = ""
   'Retrieve value if data is found
    If Not IsNull(Lrs) Then
        LGST = Lrs("SumOfCountOfORDERING_INST_NAME")
    End If

I also used this in the last part:

If Not Lrs.RecordCount > 0 Then
      LGST = Lrs("SumOfCountOfORDERING_INST_NAME")
End If

This Error 94 invalid use of Null is really hard to crack and I tried so many combinations that I'm lost.

greetings.

 

[CJ_London]

try

LGST =0
If not Lrs.EOF Then LGST = Lrs!SumOfCountOfORDERING_INST_NAME

 

[JHB]

Have you tried the NZ function?
https://www.techonthenet.com/access/functions/advanced/nz.php

 

[megatronixs]

Hi all,

I started to work with this:

If Lrs.RecordCount > 0 Then
      LGST = Lrs("SumOfCountOfORDERING_INST_NAME")
End If

and at the end I clean up the LGST and it goes fine.

for me this kind of voodoo still.

Greetings.

 

[CJ_London]

be aware that recordcount will return 0 unless you use .movelast first - anb if you use .movelast and there are not records, you will generate an error. Which why I suggested using .eof