Preventing #Error with CDate in queries

[Xevon]

I'm working on a query in the Access 2019 query editor that pulls from a table with a text field containing both text and dates, none of which are uniform in any way. For instance a date could look like 02-15-22 or 2/15/2022, and various strings that are not dates. Using the CDate or DateValue functions return #Error when the field doesn't contain something of a valid date format, so I thought that I'd check for the error:

iif(iserror(cdate([field1])),"",cdate([field1]))

However the #Error still comes up. I've tried checking for Nulls, trying to see if the field IsNumeric, etc., but I don't understand why that error check isn't doing the job. Greatly appreciate any input! Here's the SQL View if it helps at all:

SELECT dbo_Stock.stk_item_no, dbo_SalesItems.s_descript, dbo_Stock.stk_inv_qttytd, dbo_Stock.stk_row, dbo_Stock.stk_aisle, dbo_Stock.stk_bin, IIf(IsError(CDate([stk_bin])),"",CDate([stk_bin])) AS bin
FROM dbo_SalesItems INNER JOIN dbo_Stock ON dbo_SalesItems.[s_item_no] = dbo_Stock.[stk_item_no];

Result of Datasheet View:

Thank you,
- Xevon -

 

[theDBguy]

Hi. Try using the IsDate() function to check if the value is a date or not.

 

[Xevon]

The field is text, IsDate returns negative for all, thanks though!

 

[theDBguy]

The field is text, IsDate returns negative for all, thanks though!

Hmm, using your sample data, here's what I get.

 

[GPGeorge]

I'm working on a query in the Access 2019 query editor that pulls from a table with a text field containing both text and dates, none of which are uniform in any way. For instance a date could look like 02-15-22 or 2/15/2022, and various strings that are not dates. Using the CDate or DateValue functions return #Error when the field doesn't contain something of a valid date format, so I thought that I'd check for the error:

iif(iserror(cdate([field1])),"",cdate([field1]))

However the #Error still comes up. I've tried checking for Nulls, trying to see if the field IsNumeric, etc., but I don't understand why that error check isn't doing the job. Greatly appreciate any input! Here's the SQL View if it helps at all:

SELECT dbo_Stock.stk_item_no, dbo_SalesItems.s_descript, dbo_Stock.stk_inv_qttytd, dbo_Stock.stk_row, dbo_Stock.stk_aisle, dbo_Stock.stk_bin, IIf(IsError(CDate([stk_bin])),"",CDate([stk_bin])) AS bin
FROM dbo_SalesItems INNER JOIN dbo_Stock ON dbo_SalesItems.[s_item_no] = dbo_Stock.[stk_item_no];

Result of Datasheet View:
View attachment 98286

Thank you,
- Xevon -

It would aid evaluation to see the actual table as well, since that's where the data is.

 

[Xevon]

Hmm, using your sample data, here's what I get.

View attachment 98287

Very intersting: If I change the IIf then I get correct results but when I use it in a WHERE clause, it returns no rows (okay three):

SELECT dbo_Stock.stk_item_no, dbo_SalesItems.s_descript, dbo_Stock.stk_inv_qttytd, dbo_Stock.stk_row, dbo_Stock.stk_aisle, dbo_Stock.stk_bin, IIf(IsDate([stk_bin]),CDate([stk_bin]),"") AS bin
FROM dbo_SalesItems INNER JOIN dbo_Stock ON dbo_SalesItems.[s_item_no] = dbo_Stock.[stk_item_no]
WHERE (((dbo_Stock.stk_bin)=IsDate([stk_bin])));

Results

However
SELECT dbo_Stock.stk_item_no, dbo_SalesItems.s_descript, dbo_Stock.stk_inv_qttytd, dbo_Stock.stk_row, dbo_Stock.stk_aisle, dbo_Stock.stk_bin, IIf(IsDate([stk_bin]),CDate([stk_bin]),"") AS bin
FROM dbo_SalesItems INNER JOIN dbo_Stock ON dbo_SalesItems.[s_item_no] = dbo_Stock.[stk_item_no];

Results

Which is why I said that IsDate doesn't work, but it only doesn't work in the WHERE clause. Thank you for persisting and coming back with your test results, I had given up on that. I'm sure there's a good reason why it doesn't work in the WHERE clause, but that's beyond me.

 

[The_Doc_Man]

WHERE (((dbo_Stock.stk_bin)=IsDate([stk_bin])));

The IsDate() function returns true or false. The stk_bin field is a string. Your WHERE clause is asking if a string (stk_bin) is equal to a Boolean value. I'm betting that doesn't usually work out too well.

Part of your problem is the non-uniformity of date formats in stk_bin. You have not less than 3 distinctly different formats in your sample of the qryInventoryCleanup table, not counting some other things that I can't decide WHAT they mean.

1: 01/27/20 is clearly dd/mm/yy and ISDATE() would like it.
2. 06-05-20 and 06-08-20 are probably mm-dd-yy and ISDATE() might like it.
3. It would appear that 05/21-18 is actually mm/dd-yy and I would guess that ISDATE() won't like it.

All four of the cases I called out have a good date in the BIN field that would pass muster for ISDATE(). I can't BEGIN to guess at the items beginning with V in the stk_bin field. But with non-uniform formats, you are going to have a serious pile of trouble in analyzing anything in that column. This might be a case where you would need to create a VBA function to look at that string to test it for matching one of your date criteria. I don't think the standard ISDATE() function is versatile enough given your wildly different formats.

 

[theDBguy]

What is the purpose of the WHERE condition? Can you translate it using plain words?

 

[Xevon]

It was just me, not paying attention to details. Here's what I intended:

SELECT dbo_Stock.stk_item_no, dbo_SalesItems.s_descript, dbo_Stock.stk_inv_qttytd, dbo_Stock.stk_row, dbo_Stock.stk_aisle, dbo_Stock.stk_bin, IIf(IsDate([stk_bin]),CDate([stk_bin]),"") AS bin
FROM dbo_SalesItems INNER JOIN dbo_Stock ON dbo_SalesItems.[s_item_no] = dbo_Stock.[stk_item_no]
WHERE IsDate([stk_bin]);

However I put that into the query editor and it created the incorrect SQL WHERE clause. I edited the SQL statement and all is well, I suppose that I don't need the IIF statement any longer, that was just for testing.

Thank you very much!

 

[theDBguy]

It was just me, not paying attention to details. Here's what I intended:

SELECT dbo_Stock.stk_item_no, dbo_SalesItems.s_descript, dbo_Stock.stk_inv_qttytd, dbo_Stock.stk_row, dbo_Stock.stk_aisle, dbo_Stock.stk_bin, IIf(IsDate([stk_bin]),CDate([stk_bin]),"") AS bin
FROM dbo_SalesItems INNER JOIN dbo_Stock ON dbo_SalesItems.[s_item_no] = dbo_Stock.[stk_item_no]
WHERE IsDate([stk_bin]);

However I put that into the query editor and it created the incorrect SQL WHERE clause. I edited the SQL statement and all is well, I suppose that I don't need the IIF statement any longer, that was just for testing.

Thank you very much!

Glad to hear you got it sorted out. Good luck with your project.