Solved invalid use of null

[FahadTiger]

Hi Expert..
I used module to sum Amount according to CustomerNumber And CurrencyType
its working Good and give me the Result im main form of saleInvoice
but..when there are No Records in the table give me Error "invalid use of null".. in "TotalAmount = rs!TotalAmount"
How can I FIX that

Function SumAmount(CustomerNumber As Long, CurrencyType As String) As Currency
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim TotalAmount As Currency

TotalAmount = 0
Set db = CurrentDb

strSQL = "SELECT Sum(Amount) AS TotalAmount FROM saleDetails WHERE CustomerID = " & CustomerNumber & " AND CurrencyType = '" & CurrencyType & "'"
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
        TotalAmount = rs!TotalAmount
End If
rs.Close
db.Close
Set db = Nothing
SumAmount = TotalAmount
End Function

 

[Gasman]

Uze the NZ() function?
However, I would probably looking to see how it became null.

 

[tvanstiphout]

This doesn't make sense to me yet. If you set CustomerNumber to -1 (presumably non-existing) then the query would return 0 records and EOF would be true.
Something else must be going on.
Did you put a breakpoint in this function and step through? If this is an Access back-end, did you rapair & compact it?
Is Amount a required field? It probably should be.

BTW, you could have done this with a one-liner call to the DSum function.

 

[Gasman]

This doesn't make sense to me yet. If you set CustomerNumber to -1 (presumably non-existing) then the query would return 0 records and EOF would be true.
Something else must be going on.
Did you put a breakpoint in this function and step through? If this is an Access back-end, did you rapair & compact it?
Is Amount a required field? It probably should be.

BTW, you could have done this with a one-liner call to the DSum function.

No, I just tried it. It passes the EOF test and gets to the line.

Function SumAmount() ' CustomerNumber As Long, CurrencyType As String) As Currency
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim TotalAmount As Currency

TotalAmount = 0
Set db = CurrentDb

strSQL = "SELECT Sum(TestTransactions.Amount) AS TotalAmount FROM TestTransactions WHERE TestTransactions.ID=10000"
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
        TotalAmount = rs!TotalAmount
End If
rs.Close
db.Close
Set db = Nothing
SumAmount = TotalAmount
End Function

so NZ() might be the way to go, or a DCount() first to see if any exist?

 

[Josef P.]

... then the query would return 0 records and EOF would be true.

Not for Sum(...) from without Group By.
In this case, EOF will never occur.

SELECT Sum(Amount) AS TotalAmount FROM saleDetails WHERE 1=0
=> 1 Record with Null

vs.
SELECT Sum(Amount) AS TotalAmount FROM saleDetails WHERE 1=0 Group by 0

 

[FahadTiger]

this is my sample.. when No currency type in Euro gave me Error

 

[pbaldy]

Doesn't look like you tried what was suggested, though I would add an Else:

  If Not rs.EOF Then
    TotalAmount = Nz(rs!TotalAmount, 0)
  Else
    TotalAmount = 0
  End If

 

[arnelgp]

as suggested, use:

Nz(rsObject!TotalField, 0)

 

[FahadTiger]

as suggested, use:

Nz(rsObject!TotalField, 0)

Thank you All for response ...I dont Know why Im not use NZ
Its perfect Now