I did that and it gave the result of 1, however this query uses unconverted dates. When I enter the query using CDate, I get the data mismatch error.
Datatype mismatch in criteria expression regarding a CDate function field.
- Thread starter Kellen
- Start date
I did that and it gave the result of 1, however this query uses unconverted dates. When I enter the query using CDate, I get the data mismatch error.
Yeah, but just to make sure, that query gave me does work for dates are are in a text field right?
Your query works when I use PURDT as it is (a text).
But when I convert PURDT into a date using CDate it doesn't work.
MarkK
bit cruncher
- Local time
- Today, 15:18
- Joined
- Mar 17, 2004
- Messages
- 8,571
It has previously been explained in this thread that if you supply an invalid string to CDate(), CDate() will fail. Consider . . .
Similarly, you have been shown a query that will count invalid dates in your source data, and apparently when you run that query, the result is 1. That means there is one (1) invalid date in your source data. I think that to get the result you are looking for, you need to remove the invalid date from your source data. One way you can do that is with another query, maybe something like . . .
. . . so that query will only return records where the field DateFieldToTest is a valid date.
Once you have filtered out invalid dates from your source data, then you can run CDate() successfully.
Hope that helps,
Code:
Debug.Print CDate("Not a date")
Code:
SELECT * FROM SourceQuery WHERE IsDate(DateFieldToTest)Once you have filtered out invalid dates from your source data, then you can run CDate() successfully.
Hope that helps,