Microsoft Access 2010 query gives error: Data type mismatch in expression

Hi Experts,

I'm trying to move some code from a sample database defined in this question.

to my production database.

The DConcat function is from Patrick Mattews article Domain Aggregate for Concatenating Values.

This is the query that gives the error:
SELECT [MailChimp Export0].ClientID, '"' & DCONCAT("[ItemCategory]","[MailChimp Export0]","ClientID = '" & [ClientID] & "'",",") & '"' AS ItemCat
FROM [MailChimp Export0]
GROUP BY [MailChimp Export0].ClientID, '"' & DCONCAT("[ItemCategory]","[MailChimp Export0]","ClientID = '" & [ClientID] & "'",",") & '"';

This is the query that it refers to (MailChimp Export0):

SELECT CLIENTS3.ClientID, Items.ItemCategory
FROM CLIENTS3 LEFT JOIN ([Item Transactions] LEFT JOIN Items ON [Item Transactions].ItemID = Items.ItemID) ON CLIENTS3.ClientID = [Item Transactions].CustomerID
GROUP BY CLIENTS3.ClientID, Items.ItemCategory;

The query MailChimp Export0 runs OK.

Regards,
Leigh
LVL 1
LeighWardleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
I guess it should read:

SELECT
    [MailChimp Export0].ClientID,
    DCONCAT("[ItemCategory]","[MailChimp Export0]","ClientID = '" & [ClientID] & "'",",") AS ItemCat
FROM
    [MailChimp Export0]
GROUP BY
    [MailChimp Export0].ClientID,
    DCONCAT("[ItemCategory]","[MailChimp Export0]","ClientID = '" & [ClientID] & "'",",")

/gustav
0
LeighWardleAuthor Commented:
Thanks, Gustav.

That query gives the same error.
0
Ryan ChongCommented:
Check the data type of field: ClientID. If it's an integer, try change:

.... ,"ClientID = '" & [ClientID] & "'"  ....

to

.... ,"ClientID = " & [ClientID] & " " ....
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.

Patrick MatthewsCommented:
I suspect Ryan Chong is on the right track here.  Following his suggestion, if ClientID is numeric, then the query should be:

SELECT [MailChimp Export0].ClientID, 
    DCONCAT("[ItemCategory]", "[MailChimp Export0]", "ClientID = " & [ClientID], ",") AS ItemCat
FROM [MailChimp Export0]
GROUP BY [MailChimp Export0].ClientID

Open in new window


Note that you do not have to repeat the DConcat expression in the GROUP BY clause; the only table element you are including in the DConcat is ClientID, and you already have that in the GROUP BY clause.

If that does not work, please attach a sample file.  Please do not use Dropbox, as I do not wish to create an account simply to get your sample file.  EE will allow you to attach a file to your comment :)
0
LeighWardleAuthor Commented:
Hi Ryan and Patrick,

Just to be clear, ClientID is Text.
I will wait for further feedback before attaching a sample file.
0
Gustav BrockCIOCommented:
Then this should do - as correctly stated by Patrick - you only need to Group By ClientID:

SELECT
     [MailChimp Export0].ClientID,
     DCONCAT("[ItemCategory]","[MailChimp Export0]","ClientID = '" & [ClientID] & "'") AS ItemCat
 FROM
     [MailChimp Export0]
 GROUP BY
     [MailChimp Export0].ClientID

 /gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeighWardleAuthor Commented:
Hi Gustav,

Wow! That worked!
How should I allocate the points?  Suggestions please.

Regards,
Leigh
0
Gustav BrockCIOCommented:
Great!
You should allocate points to comments you find of value.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.