Concatination in Query Problem.

What sample database?

God I'm hopeless!
Concat_2.mdb

I would do that differently by using a VBA Function to write the unique door numbers to a temp table while at the same time setting a filter and loop through each door number filtered records building the concatenation string.  Then update your results field in the temp table with the string results.  

Would be a lot cleaner ... IJS.

ET

The result doesn't make sense to me but the query is working correctly.  Please post what you think the resultset should be.  I think it should be two rows

ItemNumber	DoorType	Batch	Location	Result
1732	Type A	1st	Ground	D01, D03
1821	Type B	1st	Ground	B, D

Select allOpen in new window

To get to two rows, you must remove DoorNumber since that ends up in the concatenated string.

SELECT Sets.ItemNumber, Item.DoorType, Sets.Batch, Sets.Location, fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ") AS Result
FROM Item LEFT JOIN Sets ON Item.ItemNumber = Sets.ItemNumber
GROUP BY Sets.ItemNumber, Item.DoorType, Sets.Batch, Sets.Location, fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ")
HAVING (((Sets.Batch) Like Nz([Forms]![ProjectForm]![Batches],"*") Or (Sets.Batch) Is Null) AND ((Sets.Location) Like Nz([Forms]![ProjectForm]![Locations],"*") Or (Sets.Location) Is Null));

since query1 seems to work, why not use it?

I suspect that this problem has to do with your recordset being based on a query that is based on a query that has two form control references.  I've seen similar behavior before.

Hi Pat

The sql provided is as Query1. The problem with it is that it does not include door numbers where null values are in either Sets.Batch or Sets.Location So in the example you sent door number A is missing from result in ItemNumber1821.

Result: fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ")   Does not cater for Null values. This is the sole problem.

Hi Aikimark

Well I am doing the best I can. It seems more logical to do that rather than a complex statement in the query. I have only had a query based on a query because I do not know why we cannot use the SetsQuery as the data source. I thought that basing a query on a query may eliminate criteria conflictions. There is no other purpose for two queries in my example

Hi Pat

it is the concatenation that is the problem. The grouping I can sort later. What is actually required here is Concatenate every door number in the current query by ItemNumber and forget batch and Location in the function. I have no idea if that is possible.

Hi All

I guess this must be a really hard one. Perhaps I need to look in a different direction. I badly need a solution but cannot get anyone to solve the problem or even tell me it is impossible.

If you created a parameterized version of the query that replaced the form references with parameters, you could use a querydef object to pass these form values into the query programmatically.

So the user would type the batch or location into the parameters when the query was executed?

If that is correct I take it that there must be a fundamental difference between a form parameter and  a physical parameter entry?

Is it not possible to put "OR Is Null" twice for Batch and Location somewhere in this statement:

Result: fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ").

It is not impossible at all.  It is not even hard, but we are not here to do your work for you.  Our objective is to help you to help yourself and without knowing EXACTLY what the problem is, we are just guessing.  Now that I know what the problem is, I can tell you how to fix it.

The problem is with query1.  Don't build the criteria in the query.  Build it in the function where VBA is a little easier to use than the nested IIF()'s.   The query ends up looking like:

SELECT [DoorNumber] as ConcatField FROM [Sets] WHERE ItemNumber = 1732 And Batch Like "2nd" And Location Like "Floor 2"

What you want to end up with is:
SELECT [DoorNumber] as ConcatField FROM [Sets] WHERE ItemNumber = 1732 And (Batch = '2nd' OR Batch Is Null) And (Location = 'Floor 2' OR Location Is Null)

Also, Never use Like unless you are providing a partial string.  When using a combo, you are always providing a full string so use the = operator.

Well after struggling with this for 2 days solid Pat I never expected some one to suggest that I want someone to do the work for me. I thought EE was there to solve problems that members could not solve. That's what I see all day long here. It's what I joined for. I have just paid $115 with EE online on this very problem. Hasn't solved the problem at all. Not sure what I am supposed to do. If I can't solve a problem, working on it longer is not going to help. I tried to find you in the list for online help but you were not there (I was typing in Pat Harman) I offered $100 for someone to work on this project I got no responses. I really am struggling so if you could offer something a little more helpful I would be delighted to pay whatever it costs. I have worked with access for 15 years and have built some beautiful stuff but functions are not my strong area, always solved the problem in other ways. I have no formal training. So I am stuck working with what I have. So can you help with this? Paid or otherwise I don't mind.

I badly need a solution but cannot get anyone to solve the problem or even tell me it is impossible.

I told you what was wrong with Query1.  But, rather than fixing it in the query, I would build the criteria in the function.

I don't take gigs or online calls so that is why you couldn't find me.  Do you understand how to get from what you have for criteria to what you need?  You can change it in query1 if you prefer.  I just wouldn't do it that way.  Part of using a function gets you to simplify the query.  Just pass in the necessary fields and build the criteria string in the function.

You have code in your form's button click event. In that event, you could pass the values of the form's controls into the parameters of the query.

Unfortunately Pat I do not have any skills when it comes to functions. I was just looking for a book online to purchase but if I find one that I think I might understand it won't get to me in time to satisfy my customer. You are my only hope unless I can pass your comments onto the Lady that was trying to help me online. But I guess she is in bed by now.

I can tell you exactly what I want but can't help at all with Fuction.

Did you see this post???

https://www.experts-exchange.com/questions/28974524/Concatination-in-Query-Problem.html?anchorAnswerId=41830436#a41830436

ET

Hi etsherman

I am sorry it must have come through just before other posts I didn't see it. I naturally scroll down to bottom so I missed it.

Just a thought though the query that ideally should have this concatenation field has 220 columns. Would it still work. I must admit I prefer the idea of a function handling it but "cleaner" I like. I am not sure if Pat is doing anything with it so rather than having two people working on it let us see if Pat comes up with anything first and if not I would love your help. Derek

Hi etsherman

Would the temporary table need to be added to the main query linking, presumably, by itemnumber and then just add the concatenated field from the temporary table to the QBE grid?

Yes, you could do that.  Then use the OnClose Event of a Form or Report Object to clear the temp table once finished.

ET

Are you on the online assist or project I have offered $100 for someone to do this

Oops it's 1.15 am here must go to bed. I will be out all tomorrow. Catch up on Friday

Derek

I did not fix your problem.  I determined what it was and told you how to fix it.

Contact me at the email address in my profile.  I think this is something we need to discuss further especially if you are considering a new approach.

ET

etsherman, this is not how EE works.  Discussions are supposed to be public.  If you want to be hired to do the work, use the Gigs or call option.

Seems like there is even more confusion when I reviewed the comments posted under the Gig for this question.  I'm not familiar with the Call Option.


ET

I gave the poster the solution earlier in the thread.  He just doesn't know how to implement it.  So, look back at 41830661 and 41830717.  He needs to fix the part of query1 where he is building the criteria that is used in the function.  I posted an example of what the final string needs to look like.  Apparently someone needs to actually change the concatenation of that string for him to include all the parts.  Don't forget the parentheses since the criteria will now contain both AND and OR operators.

So Pat

Still can't find anyone on EE Project or otherwise to solve the issue. I don't know how you expected me to solve this when no-one else on the EE network is prepared or able to solve it. Is there anyone you know in EE that is capable of doing this fix either online or as a project. I'm at the point of loosing faith altogether (Bit dramatic but not far from the truth)

Do you understand how the string I posted is different from the string you are building?  Did you attempt to modify your concatenation to build the string differently?  It sounded like etsherman was offering to do this for you but didn't.  I don't have time to look at it today but please try it and post back.  I'll look tonight or tomorrow to see how you are doing.

I did not want to comment on your solution Pat as it would have just created more confusion.  I was willing to provide DatabaseDek with another method.  I hadn't been on EE for a while and they used to have "Hire Me" in your Profile.  That may have changed now.  If he is willing to post it as I gig so I can apply for it ... I would be glad to work with him to get it solved.

What happened to the "Hire Me" option ... is it still there and I am just not seeing it???

ET

Eric, please accept the Gig.  I don't do gigs.

Gustav Brock seems to be working with DatabaseDek on his question.  That is one lengthy discussion.  If he wants to go another route just let him post a new Gig.  It would take me a day just to read up on everything to get caught up to where he is now.  

Thanks,

ET

Hi Eric/ Pat

Thanks for your help.

Spent 2 hours on this. Hope it's clear!

I have raised the Project offer to 200 US because I have added another requirement.

Project is called Concatination in Query
https://www.experts-exchange.com/gigs/2543/Concatination-in-Query.html

I will opt out of this one as my solution would have involved a VBA function instead of
SQL.  I think DatabaseDek wants to keep it in SQL.

ET

Database,
PLEASE try to modify the string as I instructed and post back.