Invalid use of Null

[hellfire45]

Good Afternoon. I am getting an error "Invalid use of Null" on my query. I am trying to add a filter where the date is in the future in the Expr1: CDate([HrgDate]) column. This column hrgdate, is being formatted as a string in the database and so I'm trying to convert it to a date and then use >date() to find the values that are in the future. My SQL code is below. Can anybody please lend assistance? Thank you!

SELECT dbo_Entities.EntityID AS [File#], CDate([HrgDate]) AS Expr1


FROM (dbo_Entities LEFT JOIN dbo_FCLIT ON dbo_Entities.EntityNum = dbo_FCLIT.Entitynum) LEFT JOIN dbo_Action_Hrg ON dbo_FCLIT.LookupAnchor = dbo_Action_Hrg.LookupAnchor

WHERE (((dbo_Entities.ClosedFlag)=0) AND 
((dbo_Entities.DateClosed) Is Not Null) AND 
(dbo_FCLIT.StartAction) Is Not Null) AND 
((dbo_FCLIT.End) Is Null) AND 
((dbo_Entities.EntityID) Not Like "*TEST*") AND 
((CDate([HrgDate]))>Date()));

[pbaldy]

For starters, the CDate() will error on a Null. Are there any in that field?

[hellfire45]

Yes there are, and it is returning errors. I just figured that the >date() where clause would basically pull those out anyway. Can you please suggest a better way to do it? I'm quite new.

[ItsMe]

SELECT executes before WHERE

[pbaldy]

How can it evaluate the >Date() without trying to do the conversion first? You can try wrapping the field in the Nz() function, like CDate(Nz([HrgDate], #1/1/2001#))

ItsMe, do you have a cite for that? It conflicts with what I've heard previously.

[ItsMe]

ItsMe, do you have a cite for that? It conflicts with what I've heard previously.

Not specific for Access but specific for SQL server. I put it my notes and, just now, recalled it from memory. Maybe a year ago, I was watching tutorials on Channel 9. What I wrote in my notes was based on the tutorial. Later, I will look for the vid on my hard drive or even just the notes. I will post up here afterwards.

[pbaldy]

Mine was also for SQL Server, and says something different than your source:

http://blog.sqlauthority.com/2009/04...ent-execution/

I've heard something similar for Access but don't have a cite for it. They also had other clauses before SELECT.

[ItsMe]

Paul,

It was bugging me so I found a screenshot. You are correct and I did not recall correctly. So here is the order for T-SQL.

.

[hellfire45]

This worked great. You're a wizard. Is Nz basically the equivalent of Iferror?

[pbaldy]

No worries, I just wanted to make sure I wasn't screwed up. In a way it makes sense, as evaluating the WHERE clause to restrict the data set before performing other operations would be more efficient.

[pbaldy]

This worked great. You're a wizard. Is Nz basically the equivalent of Iferror?

I don't think so. Nz() replaces Null with another value.

[ItsMe]

I've heard something similar for Access but don't have a cite for it. They also had other clauses before SELECT.

I would be interested in seeing something specific for Access. In the meantime, I will do a better job remembering the T-SQL evaluation order. Have a good day Paul.

[pbaldy]

I would be interested in seeing something specific for Access. In the meantime, I will do a better job remembering the T-SQL evaluation order. Have a good day Paul.

So would I. You too!