SQL: Nulls into DateValue function getting 3464 Data type Mismatch

[donwiss]

The expression in the falsepart of the iif is still being evaluated, and I believe is causing the error. How do I write this to get around the Null issue?

SELECT FldrName,[Name],MatDate FROM Securities WHERE iif(IsNull(MatDate),True,DateValue(MatDate) < #10/19/2020#);

MatDate is a Text data type.

 

[ian_add]

How about .... iif(MatDate & '' = '',true,.....

 

[Micron]

the falsepart of the iif is still being evaluated

Both the true and false parts of IIF are evaluated - that's the way it works.
All it takes is for one record to have Null in the field being used in a function such as DateValue and you can get that or some other error such as" Subscript Out Of Range". You probably need to convert Null to some lesser date, such as

DateValue(NZ(MatDate,"01/01/00"))

 

[donwiss]

@Micron.
Thanks for the response, but I find Nz (NullToZero) is an Access function, not a SQL function. I wish it were, it is a very elegant solution. I did find that IsNull() in other SQLs works like Nz.

@ian_add.
I'm not sure what you are proposing. Both sides of Iif have to evaluate.

 

[Joe4]

Thanks for the response, but I find Nz (NullToZero) is an Access function, not a SQL function

Take a look at COALESCE, which is what you can use in SQL instead of NZ: SQL Server COALESCE() Function

 

[donwiss]

Take a look at COALESCE, which is what you can use in SQL instead of NZ: SQL Server COALESCE() Function

Except I find that COALESCE is not in Access SQL.

 

[Joe4]

Question title:

SQL: Nulls into DateValue function getting 3464 Data type Mismatch

Previous response:

Thanks for the response, but I find Nz (NullToZero) is an Access function, not a SQL function

Current response:

Except I find that COALESCE is not in Access SQL.

You seem to be flip-flopping back and forth here. Are you looking for a SQL solution or an Access one?

 

[donwiss]

I'm looking for a SQL solution that works in Access. I'm using DAO to call from Excel VBA.

 

[Joe4]

I'm looking for a SQL solution that works in Access. I'm using DAO to call from Excel VBA.

That is a critical detail you want to be sure to mention at the beginning, so we can understand exactly what it is you are after.
I have never tried to do anything like that (involving Excel VBA, DAO, and Access all in the same solution).
I find I have the most success limiting the number of different programming languages to incorporate in a single solution.

There may be other ways around doing what you are trying to avoid these issues, but without having a full understanding of exactly what you are trying to accomplish (and why you are trying to do it this particular way), it is hard to say.

 

[Micron]

Confused
SQL simply means Structured Query Language and you posted in an Access forum and Access has a version of SQL and you are trying to use Access sql in Access. I have to believe you didn't try what I posted because it works in Access. So is the sql you wrote in post one being used in Access or not?