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

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
Or to use your original expression

SELECT...
FROM....
WHERE IIf([CompleteDt]>[qryEmpRequiredLearning].[StandardRequiredDt],1,0)>0
0
 
Jeffrey CoachmanMIS LiasonCommented:
Make sure that both Datatypes are the same (CompleteDt and StandardRequiredDt) *in the underylying tables*

Jeff
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Karen SchaeferBI ANALYSTAuthor Commented:
tried:

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

Returns #Error except where the value = 0.

What am I missing?

k
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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?

0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.