"Invalid Procedure Call" on Union Query

[jaaret]

This union query has been working fine but today it is producing an "Invalid Procedure Call" error. I have not changed the query and both underlying queries run fine.

SELECT qryEmployees.keyid, qryEmployees.Company, qryEmployees.phonecell, qryEmployees.email1, qryEmployees.email2, qryEmployees.PhotoPath, qryEmployees.Web, qryEmployees.Title FROM qryEmployees UNION SELECT qryClients.Client, qryClients.Company, qryClients.phonedirect, qryClients.Email1, qryClients.Email2, qryClients.Photo, qryClients.Web, qryClients.Title FROM qryClients ORDER BY qryEmployees.keyid;

Any assistance would be greatly appreciated!
Jaaret

 

[PHV]

What happens if you replace this:
UNION SELECT
with this ?
UNION ALL SELECT

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

 

[jaaret]

UNION SELECT ALL produces the same error.

The Union query is based on two underlying queries that run properly. Is it possible that there's something in one of those queries that works with a SELECT query but not a UNION query?

Here is the more complex of the two underlying queries:

SELECT [namefirst] & ' ' & [namelast] AS Client, dbo_company.namelookup AS Company, dbo_contacts.phonedirect, dbo_contacts.email AS Email1, dbo_contacts.email AS Email2, IIf(IsNull([photourl]),Null,IIf(IsNull([index]),'\\npebell04\Photos\clients\' & Right([photourl],Len([photourl])-41),'Y:\Client_Services\ClientPhotos\' & [index] & '.jpg')) AS Photo, IIf(IsNull(dbo_contacts!websiteurl),dbo_company!websiteurl,dbo_contacts!websiteurl) AS Web, dbo_contacts.TYPE AS Title
FROM (dbo_contacts INNER JOIN dbo_company ON dbo_contacts.employeeof = dbo_company.keyid) LEFT JOIN tblClientPhotos ON dbo_contacts.keyid = tblClientPhotos.ClientKeyID
WHERE ((([namefirst] & ' ' & [namelast]) Not Like ' *') AND ((dbo_contacts.active)<>0))
ORDER BY [namefirst] & ' ' & [namelast];

I'm going to test to see if a simpler UNION query will work.

Jaaret

 

[jaaret]

I found the problem in the underlying data. Three records had field lengths of 41 characters, which threw off this part of the SELECT statement:

Right([photourl],Len([photourl])-41)

I added an evaluation earlier in SELECT statement:

IIf(Len([photourl])<= 41,null

That solved the problem.

Jaaret

 

[PHV]

both underlying queries run fine
So, your above assertion was wrong ...