Preventing #Error with CDate in queries (1 Viewer)

Xevon

New member
Local time
Today, 07:22
Joined
Feb 16, 2022
Messages
5
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:
Screenshot 2022-02-15 164839.png


Thank you,
- Xevon -
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:22
Joined
Oct 29, 2018
Messages
21,358
Hi. Try using the IsDate() function to check if the value is a date or not.
 

Xevon

New member
Local time
Today, 07:22
Joined
Feb 16, 2022
Messages
5
The field is text, IsDate returns negative for all, thanks though!
 

GPGeorge

Grover Park George
Local time
Today, 07:22
Joined
Nov 25, 2004
Messages
1,776
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

New member
Local time
Today, 07:22
Joined
Feb 16, 2022
Messages
5
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
Screenshot 2022-02-15 191853.png


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
Screenshot 2022-02-15 192058.png


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

Immoderate Moderator
Staff member
Local time
Today, 09:22
Joined
Feb 28, 2001
Messages
27,003
Code:
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

I’m here to help
Staff member
Local time
Today, 07:22
Joined
Oct 29, 2018
Messages
21,358
What is the purpose of the WHERE condition? Can you translate it using plain words?
 

Xevon

New member
Local time
Today, 07:22
Joined
Feb 16, 2022
Messages
5
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

I’m here to help
Staff member
Local time
Today, 07:22
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom