Invalid Memo, OLE, or Hyperlink Object in subquery

[emozley]

Hi,

I've got a query which runs as follows:

strSQL = "SELECT Users.UserID, Users.FirstName, Users.Surname, Users.FeeEarner, Users.Partner, " &_
"StaffStatus.StartDate, StaffStatus.LeavingDate, " &_
"(SELECT Entitlement FROM HolidayEntitlement WHERE HolidayEntitlement.UserID=Users.UserID AND EntitlementYear=2006) AS LastYearEntitlement, " &_
"(SELECT EntitlementID FROM HolidayEntitlement WHERE HolidayEntitlement.UserID=Users.UserID AND EntitlementYear=2006) AS LastYearEntitlementID, " &_
"(SELECT Entitlement FROM HolidayEntitlement WHERE HolidayEntitlement.UserID=Users.UserID AND EntitlementYear=2007) AS ThisYearEntitlement, " &_
"(SELECT EntitlementID FROM HolidayEntitlement WHERE HolidayEntitlement.UserID=Users.UserID AND EntitlementYear=2007) AS ThisYearEntitlementID " &_
"FROM Users LEFT JOIN StaffStatus ON Users.UserID = StaffStatus.UserID " &_
"ORDER BY Users.FirstName"

This produces a recordset that shows their name, start date and leaving date and how much holiday they were allowed to take in 2006 and 2007. In my HolidayEntitlement table there is a field called 'Notes' which I have made a memo field. When I try and include this in the query however it gives the error

Invalid Memo, OLE, or Hyperlink Object in subquery 'Notes'

Where am I going wrong?

Thanks very much

Ed

 

[lespaul]

I think your query needs a little assistance before moving into the issue of your memo field (and there are known issues with memo fields). But I think this is much easier to read and should do the same thing as the one you've got. Why don't you try this and then we'll look into your memo issue.

SELECT U.UserID, U.FirstName, U.Surname, U.FeeEarner, U.Partner, S.StartDate, S.LeavingDate, H2006.Entitlement AS LastYearEntitlement, H2006.EntitlementID As LastYearEntitlementID, H2007.Entitlement AS ThisYearEntitlement, H2007.EntitlementID As ThisYearEntitlementID,
FROM Users U
INNER JOIN HolidayEntitlement H2006 ON H2006.UserID = U.UserID AND EntitlementYear=2006
INNER JOIN HolidayEntitlement H2007 ON H2007.UserID = U.UserID AND EntitlementYear=2007  

LEFT JOIN StaffStatus S ON U.UserID = S.UserID 
ORDER BY Users.FirstName"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[emozley]

Hi - thanks for this. Aliasing seems to be a lot more efficient!

There does seems to be an error with this though. I removed the comma from before the FROM part but it now says there is a missing operator?

Thanks

Ed

 

[lespaul]

can you post the SQL that gives the error?

It's not only aliasing, it's joining into HolidayEntitlement based on the year rather having additional select statements in the main select clause.

Leslie

 

[emozley]

It says

Syntax error (missing operator) in query expresssion ''.

The SQL I'm trying to execute is

SELECT U.UserID, U.FirstName, U.Surname, U.FeeEarner, U.Partner, S.StartDate, S.LeavingDate, H2006.Entitlement AS LastYearEntitlement, H2006.EntitlementID As LastYearEntitlementID, H2007.Entitlement AS ThisYearEntitlement, H2007.EntitlementID As ThisYearEntitlementID
FROM Users U
INNER JOIN HolidayEntitlement H2006 ON H2006.UserID = U.UserID AND EntitlementYear=2006
INNER JOIN HolidayEntitlement H2007 ON H2007.UserID = U.UserID AND EntitlementYear=2007

LEFT JOIN StaffStatus S ON U.UserID = S.UserID
ORDER BY Users.FirstName"

I am using MS Access 2003 if this makes any difference.

Thanks very much

Ed

 

[PHV]

I think Leslie meant this:

SELECT U.UserID, U.FirstName, U.Surname, U.FeeEarner, U.Partner, S.StartDate, S.LeavingDate
, H2006.Entitlement AS LastYearEntitlement, H2006.EntitlementID As LastYearEntitlementID
, H2007.Entitlement AS ThisYearEntitlement, H2007.EntitlementID As ThisYearEntitlementID
FROM ((Users U
INNER JOIN HolidayEntitlement H2006 ON U.UserID = H2006.UserID)
INNER JOIN HolidayEntitlement H2007 ON U.UserID = H2007.UserID)
LEFT JOIN StaffStatus S ON U.UserID = S.UserID
WHERE H2006.EntitlementYear = 2006 AND H2007.EntitlementYear = 2007
ORDER BY Users.FirstName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

no, that's what I meant to do, does Access not allow that JOIN syntax with the additional criteria? I do that in my queries all the time in DB2.

Les

 

[PHV]

Welcome to the JetSQL world, Leslie ;-)

 

[lespaul]

Ok, Ed, now that we've made your original query much easier to understand and read...what's the problem you're having with the Memo field?

SELECT U.UserID, U.FirstName, U.Surname, U.FeeEarner, U.Partner, S.StartDate, S.LeavingDate
, H2006.Entitlement AS LastYearEntitlement, H2006.EntitlementID As LastYearEntitlementID,
[b]H2006.[Notes] as 2006Notes[/b], H2007.Entitlement AS ThisYearEntitlement, H2007.EntitlementID As ThisYearEntitlementID, [b]H2007.[Notes] As 2007Notes[/b]
FROM ((Users U
INNER JOIN HolidayEntitlement H2006 ON U.UserID = H2006.UserID)
INNER JOIN HolidayEntitlement H2007 ON U.UserID = H2007.UserID)
LEFT JOIN StaffStatus S ON U.UserID = S.UserID
WHERE H2006.EntitlementYear = 2006 AND H2007.EntitlementYear = 2007
ORDER BY Users.FirstName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[emozley]

Hi,

The problem is that if you have a select within a select you cannot use a memo field. I have got round the problem for now by changing it to a text field although of course this means it is limited to 255 characters. I think this should be OK for this particular purpose but I will try your approach as this will give more flexibility and looks a bit more efficient.

Thanks very much

Ed