asked on

Datatype mismatch

IIf([CompleteDt]>[qryEmpRequiredLearning].[StandardRequiredDt],1,0)

Need fresh pair of eyes on this - what am I missing?

I need to validate that if the Completedt>Required date then return 1, else 0 and have a where clause where >0

note: the CompleteDt field is not a Date Format - it may contain NR - So I need to hide the NR then convert the field back to a date field after I remove the NR from the query - even though the table will still contain the NR.

I even tryed to create 2 queries 1 base on the results of the first query - still get type mismatch - especially when I add the Where clause of >0

thanks,
Karen

SELECT qryEmpRequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title] AS [Deliquent Courses], qryEmpRequiredLearning.DateRequired, tblEmployee.LastName, tblEmployee.Mgr_OrgNo, tblEmployee.UnitChief, Format(IIf([CompleteDt]='NR','',[CompleteDt]),'mm/dd/yyyy') AS CmplDate, qryEmpRequiredLearning.StandardRequiredDt
FROM (qryEmpRequiredLearning LEFT JOIN tblCourseList_lkup ON qryEmpRequiredLearning.CourseRecID = tblCourseList_lkup.CourseRecID) LEFT JOIN tblEmployee ON qryEmpRequiredLearning.BEMS = tblEmployee.BEMS
WHERE (((tblCourseList_lkup.OnXLS)=-1))
GROUP BY qryEmpRequiredLearning.BEMS, tblCourseList_lkup.[Ilp Learning Title], qryEmpRequiredLearning.DateRequired, tblEmployee.LastName, tblEmployee.Mgr_OrgNo, tblEmployee.UnitChief, Format(IIf([CompleteDt]='NR','',[CompleteDt]),'mm/dd/yyyy'), qryEmpRequiredLearning.StandardRequiredDt
HAVING (((qryEmpRequiredLearning.BEMS)=getgbems()))
ORDER BY tblCourseList_lkup.[Ilp Learning Title];

Open in new window

Jeffrey Coachman

Make sure that both Datatypes are the same (CompleteDt and StandardRequiredDt) *in the underylying tables*

Jeff

Karen Schaefer

ASKER

Its is not a table but query and I am converting the Competedt to remove the NR that are placed in the table in the begining qry for other purposes - Now I need to Hide the NR and convert the field back to date fields and the standardRequiredDt is already a date field. I need to compare the dates then returns 1 or 0 - it seems to be when I want the 1 0 zero to come into play that is causing the problem.

Karen Schaefer

ASKER

tried:

IIf(CDate([CmplDate])>CDate([StandardRequiredDt]),1,0)

Returns #Error except where the value = 0.

What am I missing?

k

Karen Schaefer

ASKER

Here is where I am converting the CompleteDt field to include the NR in the qryEmpRequiredLearning

CompleteDt: IIf(Year([CompletionDt])<Year(Date()),"NR",[CompletionDt])

Then later I Need to remove the NR from the Query string and convert the field value back to date field - correct - or am I making it to complicated again?

Jeffrey Coachman

OK, I think I understand now

You have to specify the entire expression in the Where clause
Something like this

SELECT...
FROM....
WHERE IIf(CDate([CmplDate])>CDate([StandardRequiredDt]),1,0)>0

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

For your time:

Great thanks for the input, however I chose a simplified solution - I add the table that contains the unaltered Completion date back into the final query and used it to calculated the 1 or zero. since that field is still in a date format.

Thanks again.

Karen

Jeffrey Coachman

OK, but I think there is a way to accept your own solution as well...

Click the "Request Attention" link and inquire, if you are interested...

;-)

Jeff