We help IT Professionals succeed at work.

Problems with null in an SQL

PBLack
PBLack asked
on
Hello Experts

I am trying to figure out why my SQL is returning EOF = False and throwing an error when there is no value in the table that has a woodcat = Logs and Diameter >= 24

In other words this query, based on the current table I have, should return EOF = True and I do not know why it does not.

Please help.

Thanks

PBLack

The code below throws the following error:

Runtime Error. Invalid use of Null

Dim db as database
Dim strVol as string
Dim recVol as Recordset
Dim logs24 as Double
logs24=0

Set db = CurrentDb()

strVol = "SELECT Sum(VOLHA) AS TOTVOL FROM tblMTally WHERE woodcat = 'Logs' AND ta_dbh >= 24;"
Set recVol = db.OpenRecordset(strVol, dbOpenDynaset)

With recVol
    If .EOF = False Then
        .MoveFirst
        logs24 = Nz(CDbl(!TOTVOL), 0) 'error thrown on this line
    End If
End With
Comment
Watch Question

Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Try this:


Dim db as DAO.database
Dim strVol as string
Dim recVol as DAO.Recordset
Dim logs24 as Double
logs24=0

Set db = CurrentDb()

strVol = "SELECT Sum(VOLHA) AS TOTVOL FROM tblMTally WHERE woodcat = 'Logs' AND ta_dbh >= 24;"
Set recVol = db.OpenRecordset(strVol, dbOpenDynaset)

With recVol
    If .RecordCount = 0 Then
        ' No Records
        ' Exit ??
    Else
        .MoveFirst
         logs24 = Nz(CDbl(!TOTVOL), 0) 'error thrown on this line
    End If
End With

mx
Try applying NZ before CDBL.
logs24 = CDbl(Nz(!TOTVOL, 0)) 'error thrown on this line

Open in new window

Top Expert 2011
Commented:
I think your issue is that CDdbl(...) the value is null and you are using cdDBL converter.

How about this:

 CDbl(Nz(!TOTVOL), 0)

Author

Commented:
Yours is not working either and I see why from your code. The recordcount is showing 1 but I just confirmed manually (114 records in table) that there is no record with woodcat 'Logs' and ta_dbh >= 24.

I am perplexed.

Author

Commented:
Yep that is is. Split the points evenly. It has been a long day. Thanks Experts!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
PBlack,

it is a lot easier to read these threads if you indicate who you are talking to when you enter a reply.

Author

Commented:
@fved sorry dude. I originally replied without seeing the other comments. Duly noted and wont happen again.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.