Procedure Name Same as Module Name

[Barmaximus]

Admin: This thread was moved from a hijack question posted in Combining Rows-Opposite of Union. Please do not post new questions in existing threads.

@NeoPa

Created a Bytes account to tell you this work is still helping people in 2019!

This Concat function (the one marked as solution) was exactly what I was looking for. Function worked perfectly yesterday. However, today I'm getting an error "Undefined Function 'Concat' In Expression" with no change in my code. I created a new module and changed Concat to Concat2 and it worked. I'm new to VBA and would love to know how to avoid this in the future.

Also, does this work when using a query or subquery in the FROM clause? For me, this yields no concatenation, but creating a table from the same query and referencing that works. Any advice would be great.

[twinnyfo]

Barmaximus,

Welcome to Bytes!

However, today I'm getting an error "Undefined Function 'Concat' In Expression" with no change in my code.

Any chance that you named the Module "Concat"? This would cause some ambiguity within the DB.

[Barmaximus]

@twinnyfo of course I did. Changing the name of the module fixed this. Any thoughts on why it worked fine previously?

[twinnyfo]

You probably tried using it prior to saving the Module's Name.

When you call Concat and you have a Module named Concat and a Procedure with the same name, the DB doesn't know how to properly disambiguate. When you build the procedure, your Module was named "Module1" by default--so there was no issue.

These types of issues have had me scratching my head for hours in the past. Now I know to use better naming conventions. So, I would have saved the Module as "modConcat", which contained a Procedure named "Concat". Some go further and name Functions "fnConcat" to further prevent any disambiguation.

Hope this hepps!

Let us know if there are any other headscratchers you need hepp with!

[Barmaximus]

Thanks @twinnyfo . Any thoughts on this function not yielding a concatenation when called in a Select query with a sub Select query in the From clause? I'm trying to use the function on data generated from a query. However, it doesn't yield the desired results unless I create a table that is identical to the query output.

[twinnyfo]

My only thought is that the SQL engine gets confused or overwhelmed when going into the subqueries. For example, you can refer to open forms for criteria in your queries, but you can't refer to open forms when the criteria is in a subquery (if I remember my experiences correctly).

What have you been trying? If you post your SQL, we can take a look and see if we can decipher anything.

[Barmaximus]

Below is my code. The subquery finds all keyword matches from a reference library [Component Library] to [Data - Inventory].Description and returns the keyword found and the associated Type. This yields more than one row per component where multiple matches are found. Works perfectly as a standalone query. When inserted as a subquery, it yields one match per component, no concatenation. However, when I create a table identical to the results of the query, I can achieve the desired concatenation.

SELECT x.Component, 
       Max(x.Description), 
       Max(Concat(Nz(x.Component), Nz(x.PartType)))
FROM (SELECT [Data - Inventory].Component, 
             [Data - Inventory].Description, 
             [Component Library].Keyword AS [Key], 
             [Component Library].Type AS PartType
      FROM [Component Library] 
      LEFT JOIN [Data - Inventory] 
      ON [Data - Inventory].Description LIKE "*" &  [Component Library].Keyword & "*") 
      AS x
GROUP BY x.Component;

[twinnyfo]

I wonder if SQL just doesn't like using that function with JOINs?

[Barmaximus]

It's possible. I'm extremely new to SQL. But based on my limited knowledge, I would assume it would complete the entire subquery and return it to the main query in the form of a table regardless of the method used to achieve the data.

[twinnyfo]

I'm sure NeoPa will have an intelligent answer on this one.

When SQL executes subqueries, I don't know if the query runs underneath to its full extent and then JOINs or if the top query executes, and the executes a subquery based upon the JOIN.

I know when I have chosen to use sub-queries (instead of things which are frowned upon, like using a Domain Aggregate Function) that I loose the flexibility of using that query as an updateable query. I've recently tried to eliminate all my DA functions from my queries (which is the "right thing to do") but had to go back because I was using them to update tables.

SQL can be finicky sometimes.

[Barmaximus]

The end goal of this query is to perform a one time Update/population of a Type field in my [Data - Inventory] table. I have been building it in steps to ease in troubleshooting. I have only been using SQL for a few weeks and am trying to avoid starting bad habits, so any advice is greatly appreciated!

[twinnyfo]

I can tell you right now that using this method will not allow you to use it as an UPDATE query.

Ther eis a part of me that wonders about your subquery design. Will something like this work?

SELECT [Data - Inventory].Component, 
       [Data - Inventory].Description, 
       Max(Concat(Nz([Data - Inventory].Component), Nz([Component Library].Type))) AS PartTypes
FROM [Component Library] 
LEFT JOIN [Data - Inventory] 
ON [Data - Inventory].Description LIKE "*" &  [Component Library].Keyword & "*" 
GROUP BY [Data - Inventory].Component;

[Barmaximus]

This works but yields the same result as the non-concatenated result I described previously. I like the idea of this suggestion avoiding the need of a subquery if I can get it to work.

[twinnyfo]

And, as I have been playing with this little function, I can't get the concatenation to work with any type of join. It works fine if there is only one table.

You might have to use VBA to cycle through all your required components and concatenate on the fly and update your table as you go.

[twinnyfo]

We may be a bit off center from this thread. I recommend you begin a new thread and we can work thorugh the specifics if you need.

[Barmaximus]

Done.