Data Type Mismatch Error (1 Viewer)

[Jonny]

This One Is Working

SELECT DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate, Max(DegreeDate.Degree) AS [Current Degree]
FROM EmpList INNER JOIN DegreeDate ON EmpList.ID = DegreeDate.EmplID
GROUP BY DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate
HAVING (((EmpList.Position)="1") AND ((DegreeDate.DegreeDate)<Now()-365*4) AND ((Max(DegreeDate.Degree))=11));

..and this one is working

SELECT DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate, Max(DegreeDate.Degree) AS [Current Degree]
FROM EmpList INNER JOIN DegreeDate ON EmpList.ID = DegreeDate.EmplID
GROUP BY DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate
HAVING (((EmpList.Position)="2") AND ((DegreeDate.DegreeDate)<Now()-365*3) AND ((Max(DegreeDate.Degree))=11));

However merging both above into one with "OR" operator throw an error of "Data Type Mismatch..". What am I doing wrong?

SELECT DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate, Max(DegreeDate.Degree) AS [Current Degree]
FROM EmpList INNER JOIN DegreeDate ON EmpList.ID = DegreeDate.EmplID
GROUP BY DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate
HAVING (((EmpList.Position)="1") AND ((DegreeDate.DegreeDate)<Now()-365*4) AND ((Max(DegreeDate.Degree))=11)) OR (((EmpList.Position)="2") AND ((DegreeDate.DegreeDate)<Now()-365*3) AND ((Max(DegreeDate.Degree))=11));

 

[Gasman]

Have you tried putting it into the sql view of a new query and see if that highlights the error.?

I am thinking brackets are causing the problem.

 

[Jonny]

Have you tried putting it into the sql view of a new query and see if that highlights the error.?

I am thinking brackets are causing the problem.

No luck..

 

[Gasman]

Can you build via the QBE?

 

[The_Doc_Man]

I checked the parentheses. They appear to balance and the two "forks" of the OR appear to be consistent with each other. I suspect, based on the placement of some of those parentheses, that you built the individual queries in a grid because the QBE grid typically adds a few layers that really aren't needed.

Having said that, I think you have a couple of items that need another set of parentheses. I rewrote the "OR" query to reformat it for analysis. When I did, nothing popped out at me as being syntactically wrong. I'll show you what I did so you can see how to reformat really nasty queries.

SELECT 
    DegreeDate.EmplID, 
    EmpList.FirstName, 
    EmpList.LastName, 
    EmpList.Position, 
    DegreeDate.DegreeDate, 
    Max(DegreeDate.Degree) AS [Current Degree]
FROM 
    EmpList INNER JOIN DegreeDate ON EmpList.ID = DegreeDate.EmplID
GROUP BY 
    DegreeDate.EmplID, 
    EmpList.FirstName, 
    EmpList.LastName, 
    EmpList.Position, 
    DegreeDate.DegreeDate
HAVING 
        ( ( ( EmpList.Position ) = "1" ) AND 
        ( ( DegreeDate.DegreeDate ) < Now() - 365 * 4 ) AND 
        ( ( Max( DegreeDate.Degree ) ) = 11 ) ) 
    OR 
        ( ( ( EmpList.Position ) = "2" ) AND 
        ( ( DegreeDate.DegreeDate ) < Now() - 365 * 3 ) AND 
        ( ( Max( DegreeDate.Degree ) ) =11 ) ) ;

I would have written the last part like this:

...
HAVING 
        ( ( EmpList.Position = "1" ) AND 
        ( DegreeDate.DegreeDate < ( Now()- ( 365 * 4 ) ) ) AND 
        ( Max( DegreeDate.Degree ) = 11 ) ) 
    OR 
        ( ( EmpList.Position = "2" ) AND 
        ( DegreeDate.DegreeDate < ( Now() - ( 365 * 3 ) ) ) AND 
        ( Max( DegreeDate.Degree ) = 11 ) ) ;

I might point out that sometimes things can get confused when you have a table that has the same name as a field in that table. Technically legal, but known to confuse the heck out of folks. In your case, "DegreeDate.DegreeDate" looks a little odd. I don't offhand recall whether this leads to problems because of the duality of "DegreeDate" but it is something to look at.

I'll also point out that grouping by too many fields can lead to some strange results. You have a GROUP BY on DegreeDate.DegreeDate. MAX() works on the group in this context, not on the whole table, so you should see one record for each degree the person holds UNLESS they got two degrees on the same date. In essence, you might as well have not bothered to use the MAX function to find out their highest degree because with that grouping and the way the tables appear to be structured (by inference), every record will be unique anyway. I.e. you will get a record for every person for the year in which they got a degree.

 

[JHB]

I wouldn't call a field name and a table name as the same.

... DegreeDate.DegreeDate ..

 

[Jonny]

Fixed both the parentheses and renamed the "DegreeDate.DegreeDate" however MS ACCESS still giving same error..
Something weird happens in criteria expression.

I've replaced the OR operator with UNION and this solved..

SELECT DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate, Max(DegreeDate.Degree) AS [Current Degree]
FROM EmpList INNER JOIN DegreeDate ON EmpList.ID = DegreeDate.EmplID
GROUP BY DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate
HAVING (((EmpList.Position)="1") AND ((DegreeDate.DegreeDate)<Now()-365*4) AND ((Max(DegreeDate.Degree))=11))

UNION

SELECT DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate, Max(DegreeDate.Degree) AS [Current Degree]
FROM EmpList INNER JOIN DegreeDate ON EmpList.ID = DegreeDate.EmplID
GROUP BY DegreeDate.EmplID, EmpList.FirstName, EmpList.LastName, EmpList.Position, DegreeDate.DegreeDate
HAVING (((EmpList.Position)="2") AND ((DegreeDate.DegreeDate)<Now()-365*3) AND ((Max(DegreeDate.Degree))=11));