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.

 

[donwiss]

When I tried your first suggestion, I got the error 3085 Undefined function 'Nz' in expression. I tried:

SELECT FldrName,[Name],MatDate FROM Securities WHERE DateValue(Nz(MatDate,"1/1/1900")) < #10/20/2020#;

 

[donwiss]

I'm guessing my problem is I'm using Access 2002. I haven't bothered to upgrade from Excel 2002, as my application is only VBA, and the only VBA difference I've found is the BASE function, which I don't need. But I gather that Access's SQL has gained features.

I will have to move the upgrade closer to the present.

Thanks for your help.

 

[Joe4]

What exactly is it that you are trying to do?
If you are trying to pull data from Access to Excel, you may be able to do your preliminary calculations (like using "NZ") in an Access query.
Then, you may be able to use your Excel VBA/DAO solution to pull data from that query (applying any other dynamic criteria you may need at that time), instead of from the Access table/query you were trying to pull from before.

But upgrading to something more current would be good too, as new functionality was added in Access, VBA, and the DAO libraries.

 

[Micron]

You should have Nz([MatDate] not Nz(MatDate
This runs without an error but will return records with no dates.

SELECT [Attendance Log].Entry, [Attendance Log].EmployeeID, [Attendance Log].[Last Name], [Attendance Log].DateV
FROM [Attendance Log]
WHERE ((DateValue(Nz([DateV],"1/1/1900"))<#10/20/2020#));

I guess the real question is why are you using DateValue function.

 

[donwiss]

Putting square brackets around MatDate gets the same error. I don't find any evidence that Nz works in my old version. To proceed with development of the form (e.g. deleting selected rows), I'm simply retrieving all. I will have months before it has to fully work.

I'm using the DateValue function, as I'm storing the dates in text fields. I choose to do it this way, so to avoid the no date in a date field hassle, and to make the data more approachable to other programs.

 

[Micron]

Sorry that didn't help. I see now that you are likely on to something wrt versioning as I found this under the M$ function page
Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

Maybe you'd be better off with a date field and coerce the data type to whatever type you need for other apps when you need it. Or you might be able to nest another IIF with IsNull to provide the bogus date.

 

[Micron]

For those who might follow, testing for Null dates with IIF works and if anyone could verify that this is the case in any Access version earlier than 2007 that would be nice. I think IIF has been around longer than Nz. It will return records where the date is null, which may or may not be what everyone would want. Since the field data is text it's a tenuous approach because the field could also contain a zls (zero length string) or even "dog" for anyone else.

SELECT [Table1].Entry, [Table1].ID, [Table1].[Last], [Table1].dte FROM [Table1]
WHERE DateValue(IIf(IsNull([dte]),#1/1/1900#,[dte]))<#10/20/2020#;

 

[donwiss]

Sorry for the delay. I'm now getting back to this. First, I've realized that my initial question was incorrect. There are no Nulls. The non-dates are empty strings. Now the data is edited, so the only possibilities are empty strings and valid dates.

Iif has always existed. And it is the correct solution, especially now that they aren't Nulls. This works fine:

WHERE DateValue(IIf(MatDate = "",#1/1/1900#,MatDate))<#10/20/2020#;

Thanks for your help.

P.S. @Micron. I can only set one of your responses as the correct answer. I left it at the first, as that did answer my original question.

 

[JonXL]

Micron's comment about the text data type containing zero-length strings is definitely something to look into. You're right now only testing for NULL fields, but you may need to nest another conditional in there also looking for the ZLS.

Abe: Posted without reading your followup. Looks like you got it with Micron's fix.