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.
15 669 twinnyfo 3,653
Recognized Expert Moderator Specialist
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.
@twinnyfo of course I did. Changing the name of the module fixed this. Any thoughts on why it worked fine previously?
twinnyfo 3,653
Recognized Expert Moderator Specialist
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!
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 3,653
Recognized Expert Moderator Specialist
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.
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 3,653
Recognized Expert Moderator Specialist
I wonder if SQL just doesn't like using that function with JOINs?
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 3,653
Recognized Expert Moderator Specialist
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Olivier Vierlinck |
last post by:
Hi,
I've a python script using somecalls to the abs() built-in function.
Now, I have to import a module named 'abs' too...
The consequence if that line code like
if (abs(a-b) < epsilon:
...
does not work anymore, with this error msg:
|
by: John Bentley |
last post by:
I have two tasks that I expect will be straight forward to anyone with
reflection skills.
1. To return the the name of an object instance (not type) determined at run
time.
For example, I would like to call a function that takes a basic variable and
writes to the debug window the name of the variable and value.
Dim myNumber As Integer = 12
|
by: Pavils Jurjans |
last post by:
Hello,
I have bumped upon this problem: I do some client-side form processing
with JavaScript, and for this I loop over all the forms in the
document. In order to identify them, I read their "name" property
(which sources from "name" HTML attribue). The problem is, that if the
form contains form control named "name", it overwrites the form name
property. In fact, I'm quite surprised that it's so easy to spoil any
of the form object...
|
by: Casey Hawthorne |
last post by:
Is there a way to determine -- when parsing -- if a word contains a
builtin name or other imported system module name?
Like "iskeyword" determines if a word is a keyword!
--
Regards,
Casey
|
by: Mike Hoff |
last post by:
Hello,
I am wondering if it is possible to pass a procedure name to another proc.
I have been looking into delegates, but cannot seem to get the code correct.
Basically what I have is a proc to add handlers to various controls on a
form. basically each control would get mostly the same handlers, but there
is a particular handler that would vary based on the parent on the control
in question. What I would like to do is something like...
| |
by: Anders Jansson |
last post by:
Hi all.
I have 2 problems when I try to open and convert an ASP.NET VS 2003
web-applikation in VS 2005.
In VS 2003 I have no problems at all!
First: One subfolder with will not be converted! The converted web project
are missing this subfolder completely.
39 errors are displayed in the Conversion Report saying: ERROR: Unable to
get local copy of file <file>. Why? All files are there and readable!!
|
by: alain MONTMORY |
last post by:
Hello everybody,
I am a newbie to python so I hope I am at the right place to expose my
problem..... :-http://www.python.org/doc/2.4.2/ext/pure-embedding.html 5.3 Pure Embedding
I download the code example from
http://www.python.org/doc/2.4.2/ext/run-func.txt
I call the file "TestOfficiel.c" and I compile it with :
gcc -g -I/usr/include/python2.3/ TestOfficiel.c -o TestOfficiel
-lpython2.3 -ldl
all is OK (or seems to be...).
|
by: Michel Vanderbeke |
last post by:
Hello,
While logging the errors in my program, I want to know in which class,
function or procedure they occured.
Is it possible to know the name of the class, function and / or procedure in
which the error was produced? Is it also possible to know at which line
number of the program the error occured?
Many thanks and greetings,
|
by: Neil |
last post by:
Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name of
the procedure that's writing the error. But, rather than customizing each
error handler with the procedure name, it would be nice to be able to call a
system variable or function that gives me the procedure name and module
name. Is that possible?
|
by: PhpCool |
last post by:
Hi, since sometime I'm stuck in a problem where I want to check or
uncheck all the checkboxes. If I'm choosing name for the checkbox
array as 'chkbx_ary' then I'm able to check/uncheck all the checkboxes
(code pasted below). But if name of the checkbox array is
'chkbx_ary' then it's failing. I want the name to be 'chkbx_ary'
because I want to access this array at server side.
Though one may not require to see php part but I'm still pasting...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |