Todd MacPherson
asked on
Problems with null in an SQL
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep that is is. Split the points evenly. It has been a long day. Thanks Experts!
PBlack,
it is a lot easier to read these threads if you indicate who you are talking to when you enter a reply.
it is a lot easier to read these threads if you indicate who you are talking to when you enter a reply.
ASKER
@fved sorry dude. I originally replied without seeing the other comments. Duly noted and wont happen again.
ASKER
I am perplexed.