We help IT Professionals succeed at work.
Get Started

Problems with null in an SQL

PBLack
PBLack asked
on
318 Views
Last Modified: 2012-05-12
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:
This problem has been solved!
Unlock 3 Answers and 7 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE