I expect there is a null value of MailingID in your query. Try to filter
them out.

--
Duane Hookom
MS Access MVP


"HLCruz via AccessMonster.com" u27207@uwe wrote in message
news:a742e58f0434e@uwe...
I have a database that I created in Access 2003; there are a few places
where
I use the code from Duane Hookum's concatentae funcion in my database.

The code has worked perfectly for well over a year in several instances,
however suddenly it won't work anywhere. I get a run-time error - Syntax
error (missing operator) in query expression - 'MailingID =' ... when I
debug it, the following line of code is hightlighted:

Set rs= db.OpenRecordset (PastStrSQL)

I haven't changed any of my code, however we did move to Access 2007
recently.
If the SQL doesn't appear to be the problem, I would appreciate any
suggestions on where to troubleshoot. I really miss using this funtion.

Thank you,
Heather

Here is a sample of the SQL of one of the queries that no longer works ...

SELECT sysqryShiningStarHouseholdIDs.MailingID, Concatenate("SELECT
LastName
FROM sysqryShiningStars WHERE MailingID =" & [MailingID] & " ORDER BY
LastName") AS LastNames, Concatenate("SELECT FirstName FROM
sysqryShiningStars WHERE MailingID =" & [MailingID] & " ORDER BY
FirstName")
AS FirstNames, Concatenate("SELECT Age FROM sysqryShiningStars WHERE
MailingID =" & [MailingID] & " ORDER BY FirstName") AS Age
FROM sysqryShiningStarHouseholdIDs;


SQL for sqryShiningStarHouseholdIDs:
SELECT sysqryShiningStarDesignators.MailingID, First
(sysqryShiningStarDesignators.ProfileCode) AS FirstOfProfileCode
FROM sysqryShiningStarDesignators
GROUP BY sysqryShiningStarDesignators.MailingID;

SQL for sysqryShiningStarDesignators:
SELECT tblMailingList.MailingID, tblDesignators.ProfileCode
FROM tblMailingList RIGHT JOIN tblDesignators ON tblMailingList.MailingID
=
tblDesignators.MailingID
WHERE (((tblDesignators.ProfileCode)="ShiningStar"));

--
Message posted via http://www.accessmonster.com