Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Criteria in Query

Status
Not open for further replies.

bcooler

Programmer
Jun 13, 2009
132
Thanks for reading!

I have a field called "Comments" where, oddly enough, I am keeping text that looks like a date (long story, just laugh and keep going...).

Now, I want to create a query that looks for records whose [date + 2 years] is older than today. In other words, if this field is the document review day, and we must re-review every 2 years, I want to know the documents that are overdue.

To obtain the date I used the following field, which attempts to covert the text to a date and add 2 years:

Code:
ReviewDueDate: DateAdd("yyyy",2,CDate([Comments]))

This works fine...well at least it gives a date that is 2 years after the initial date.

The problem is when I use criteria on this new date. If I say something like:

Code:
<Date()

Then I get an error that says, "Data Type Mismatch in criteria expression."

What am I doing wrong? I am converting the text to a date, so it should be able to perform criteria on it....right? Or do I need to covert it first in one query, then perform criteria on it in another?

Thanks!
Brian
 
What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Here's the code:

Code:
SELECT qryNameCreation.CombinedName, tblFileProp.FileName, DateAdd("yyyy",2,CDate([Comments])) AS ReviewDueDate, tblFileProp.Title, tblFileProp.Author, tblEmployee.Email
FROM (tblEmployee INNER JOIN qryNameCreation ON tblEmployee.Employee_ID = qryNameCreation.Employee_ID) INNER JOIN tblFileProp ON qryNameCreation.CombinedName = tblFileProp.Author
WHERE (((DateAdd("yyyy",2,CDate([Comments])))<Date()));
 
Ok, not sure why, but I've got it working....

Other posts say that if CDATE finds something that doesn't look like a date, it will give this type of error. I verified that all my queried results do not have this issue, but......

if I add the "((IsDate([Comments]))=-1))" criteria, the problem goes away (remember, Comments = where I store a text version of a date):

Code:
SELECT tblFileProp.ID, qryNameCreation.CombinedName, tblFileProp.FileName, DateAdd("yyyy",2,CDate([Comments])) AS ReviewDueDate, tblFileProp.Title, tblFileProp.Author, tblEmployee.Email
FROM (tblEmployee INNER JOIN qryNameCreation ON tblEmployee.Employee_ID = qryNameCreation.Employee_ID) INNER JOIN tblFileProp ON qryNameCreation.CombinedName = tblFileProp.Author
WHERE (((DateAdd("yyyy",2,CDate([Comments])))<Date()) AND ((IsDate([Comments]))=-1))
ORDER BY DateAdd("yyyy",2,CDate([Comments]));

Although this added criteria doesn't affect the # of records, it does keep CDATE from crashing. I've turned the criteria on and off just to verify and everything points to the criteria being the solution.

Not that it really matters now, but any ideas why this is true?

Brian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top