Duane Hookom Concantenate error

[jeff]

OK,

This is such a simple problem that the answer is probably right in
front of me, but I just can't see it...

I have downloaded Duane's Concantenate demo. I have imported the
module into an Access 2003 (SP2) db. I have created my query and set
up my column to contain the merged values from multiple records. But,
when I run it, the debugger kicks in and I get the "User-defined type
not defined" error, and the line referenced is the "Dim rs As New
ADODB.Recordset" highlighted line.

Now I had tried this before, copying into the FPWWIND.mdb file and I
had gotten the same error--couldn't figure that one out either. But
when I created a brand new db (again in Access 2003), and imported the
Products table from the FPWIND.mdb and the concantenate module, it
worked just fine.

So, what am I missing? Something is different, and for the life of me
I cannot figure this out.

Any help may save what few, remining grey strands are left.

Your help is GREATLY appreciated!

Jeff

 

[Marshall Barton]

I have downloaded Duane's Concantenate demo. I have imported the
module into an Access 2003 (SP2) db. I have created my query and set
up my column to contain the merged values from multiple records. But,
when I run it, the debugger kicks in and I get the "User-defined type
not defined" error, and the line referenced is the "Dim rs As New
ADODB.Recordset" highlighted line.

Now I had tried this before, copying into the FPWWIND.mdb file and I
had gotten the same error--couldn't figure that one out either. But
when I created a brand new db (again in Access 2003), and imported the
Products table from the FPWIND.mdb and the concantenate module, it
worked just fine.

Ir sounds like you you do not have a Reference set for the
ADO library (VBE - Tools - References menu item).

 

[jeff]

Ir sounds like you you do not have a Reference set for the
ADO library (VBE - Tools - References menu item).

Marsh,

Good call--I knew it was something simple. But, now that I have that
checked I get an "undefined function 'Concatenate' in expression'
error. I copied the name from the function when I was creating the
expression, so I know I have spelled it correctly, And I have resaved
the Concatenate module, and I see the Concatenate function in the
library, So what am I missing?

Thanks again,

Jeff

 

[John Spencer]

You cannot have the module named that same as any function or subroutine.
Change the name of the module to modConcatenate.

I think you may still have a problem with the code executing. Read the
notes in the code and you will see that the code is written to work with
either DAO or ADO. If you are using an .mdb then you probably need to
comment out the line that are there for ADO and remove the comment mark
(apostrophe at start of line) for the DAO lines.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[jeff]

You cannot have the module named that same as any function or subroutine.
Change the name of the module to modConcatenate.

I think you may still have a problem with the code executing. Read the
notes in the code and you will see that the code is written to work with
either DAO or ADO. If you are using an .mdb then you probably need to
comment out the line that are there for ADO and remove the comment mark
(apostrophe at start of line) for the DAO lines.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.








- Show quoted text -

John,

Ok, so when I imported the module, I left the name the same:
basConcatenate, with the function name (still) as Concatenate. And a
little more background:

I am using the concatenate function to combine multiple records in
five different tables: litigation, estate planning, real property,
corporate and guardian. I have run five different queries (on each of
the previous tables) using the concatenate function and each time it
has worked just fine. To accumulate the results of the five queries
(each of which created a table with the same four fields) I used a
UNION query, which I named AllMatterInfoRecs. Now, when I try to use
(for the sixth time) the Concatenate function on the AllMatterInfoRecs
table, I get the "undefined function..." error.

Nothing has changed except I exited and restarted Access and I am
using the function on a table which is based on a UNION query, which
joins five tables together. The query field which uses the Concatenate
function is:

AllMatterInfo: Concatenate("Select CorporateMatterInfo. MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo.ClientNumberA] & Chr(9) & Chr(10))

The names of the fields in the AllMatterInfoRecs table are:

CorporateMatterInfo.ClientNumberA
CorporateMatterInfo.CCode
CorporateMatterInfo.UniqueID
CorporateMatterInfo.MatterInfo

I cannot figure out why this message is coming up. Also, even though
the function has worked five previous times, in the same MS
Access .mdb file, I tried going back to the funciton and removing the
comment markers for the four lines starting with 'Dim rs As
DAO.Database... and then commented out the two lines starting with
"Dim rs As New ADODB.Recordset... No joy there either.

Any ideas?

Thanks,

Jeff

 

[John Spencer]

Assuming that your posted code was pasted.

AllMatterInfo: Concatenate("Select CorporateMatterInfo. MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo.ClientNumberA] & Chr(9) & Chr(10))

--There is a space between CorporateMatterInfo. and MatterInfo.
--[CorporateMatterInfo.ClientNumberA] is improperly bracketed.
[CorporateMatterInfo].[ClientNumberA]
--You are assuming that ClientNumberA is a number field and not text.
-- & Chr(9) & Chr(10) at the end makes no real sense unless you want that to
be the divider. If you want Tab plus line feed to be the divider then you
need a COMMA in place of the first ampersand (&). (Why Tab before line
feed? Do you really want a new line Chr(13) & Chr(10) or a new line and a
tab character Chr(13) & Chr(10) & Chr(9)). You do realize that Chr(9) will
display as a square box in Access).

Guessing that what you want is possibly the following
AllMatterInfo: Concatenate("Select CorporateMatterInfo.MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo].[ClientNumberA], Chr(13) & Chr(10))



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

You cannot have the module named that same as any function or subroutine.
Change the name of the module to modConcatenate.

I think you may still have a problem with the code executing. Read the
notes in the code and you will see that the code is written to work with
either DAO or ADO. If you are using an .mdb then you probably need to
comment out the line that are there for ADO and remove the comment mark
(apostrophe at start of line) for the DAO lines.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

(e-mail address removed) wrote:
I have downloaded Duane's Concantenate demo. I have imported the
module into an Access 2003 (SP2) db. I have created my query and set
up my column to contain the merged values from multiple records. But,
when I run it, the debugger kicks in and I get the "User-defined type
not defined" error, and the line referenced is the "Dim rs As New
ADODB.Recordset" highlighted line.

Now I had tried this before, copying into the FPWWIND.mdb file and I
had gotten the same error--couldn't figure that one out either. But
when I created a brand new db (again in Access 2003), and imported
the
Products table from the FPWIND.mdb and the concantenate module, it
worked just fine.

Ir sounds like you you do not have a Reference set for the
ADO library (VBE - Tools - References menu item).

--
Marsh
MVP [MS Access]

Marsh,

Good call--I knew it was something simple. But, now that I have that
checked I get an "undefined function 'Concatenate' in expression'
error. I copied the name from the function when I was creating the
expression, so I know I have spelled it correctly, And I have resaved
the Concatenate module, and I see the Concatenate function in the
library, So what am I missing?

Thanks again,

Jeff- Hide quoted text -

- Show quoted text -

John,

Ok, so when I imported the module, I left the name the same:
basConcatenate, with the function name (still) as Concatenate. And a
little more background:

I am using the concatenate function to combine multiple records in
five different tables: litigation, estate planning, real property,
corporate and guardian. I have run five different queries (on each of
the previous tables) using the concatenate function and each time it
has worked just fine. To accumulate the results of the five queries
(each of which created a table with the same four fields) I used a
UNION query, which I named AllMatterInfoRecs. Now, when I try to use
(for the sixth time) the Concatenate function on the AllMatterInfoRecs
table, I get the "undefined function..." error.

Nothing has changed except I exited and restarted Access and I am
using the function on a table which is based on a UNION query, which
joins five tables together. The query field which uses the Concatenate
function is:

AllMatterInfo: Concatenate("Select CorporateMatterInfo. MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo.ClientNumberA] & Chr(9) & Chr(10))

The names of the fields in the AllMatterInfoRecs table are:

CorporateMatterInfo.ClientNumberA
CorporateMatterInfo.CCode
CorporateMatterInfo.UniqueID
CorporateMatterInfo.MatterInfo

I cannot figure out why this message is coming up. Also, even though
the function has worked five previous times, in the same MS
Access .mdb file, I tried going back to the funciton and removing the
comment markers for the four lines starting with 'Dim rs As
DAO.Database... and then commented out the two lines starting with
"Dim rs As New ADODB.Recordset... No joy there either.

Any ideas?

Thanks,

Jeff

 

[jeff]

Assuming that your posted code was pasted.

AllMatterInfo: Concatenate("Select CorporateMatterInfo. MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo.ClientNumberA] & Chr(9) & Chr(10))

--There is a space between CorporateMatterInfo. and MatterInfo.
--[CorporateMatterInfo.ClientNumberA] is improperly bracketed.
[CorporateMatterInfo].[ClientNumberA]
--You are assuming that ClientNumberA is a number field and not text.
-- & Chr(9) & Chr(10) at the end makes no real sense unless you want that to
be the divider. If you want Tab plus line feed to be the divider then you
need a COMMA in place of the first ampersand (&). (Why Tab before line
feed? Do you really want a new line Chr(13) & Chr(10) or a new line and a
tab character Chr(13) & Chr(10) & Chr(9)). You do realize that Chr(9) will
display as a square box in Access).

Guessing that what you want is possibly the following
AllMatterInfo: Concatenate("Select CorporateMatterInfo.MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo].[ClientNumberA], Chr(13) & Chr(10))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

You cannot have the module named that same as any function or subroutine.
Change the name of the module to modConcatenate.
I think you may still have a problem with the code executing. Read the
notes in the code and you will see that the code is written to work with
either DAO or ADO. If you are using an .mdb then you probably need to
comment out the line that are there for ADO and remove the comment mark
(apostrophe at start of line) for the DAO lines.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

(e-mail address removed) wrote:
I have downloaded Duane's Concantenate demo. I have imported the
module into an Access 2003 (SP2) db. I have created my query and set
up my column to contain the merged values from multiple records. But,
when I run it, the debugger kicks in and I get the "User-defined type
not defined" error, and the line referenced is the "Dim rs As New
ADODB.Recordset" highlighted line.
Now I had tried this before, copying into the FPWWIND.mdb file and I
had gotten the same error--couldn't figure that one out either. But
when I created a brand new db (again in Access 2003), and imported
the
Products table from the FPWIND.mdb and the concantenate module, it
worked just fine.
Ir sounds like you you do not have a Reference set for the
ADO library (VBE - Tools - References menu item).
--
Marsh
MVP [MS Access]
Marsh,
Good call--I knew it was something simple. But, now that I have that
checked I get an "undefined function 'Concatenate' in expression'
error. I copied the name from the function when I was creating the
expression, so I know I have spelled it correctly, And I have resaved
the Concatenate module, and I see the Concatenate function in the
library, So what am I missing?
Thanks again,
Jeff- Hide quoted text -
- Show quoted text -

John,

Ok, so when I imported the module, I left the name the same:
basConcatenate, with the function name (still) as Concatenate. And a
little more background:

I am using the concatenate function to combine multiple records in
five different tables: litigation, estate planning, real property,
corporate and guardian. I have run five different queries (on each of
the previous tables) using the concatenate function and each time it
has worked just fine. To accumulate the results of the five queries
(each of which created a table with the same four fields) I used a
UNION query, which I named AllMatterInfoRecs. Now, when I try to use
(for the sixth time) the Concatenate function on the AllMatterInfoRecs
table, I get the "undefined function..." error.

Nothing has changed except I exited and restarted Access and I am
using the function on a table which is based on a UNION query, which
joins five tables together. The query field which uses the Concatenate
function is:

AllMatterInfo: Concatenate("Select CorporateMatterInfo. MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo.ClientNumberA] & Chr(9) & Chr(10))

The names of the fields in the AllMatterInfoRecs table are:

I cannot figure out why this message is coming up. Also, even though
the function has worked five previous times, in the same MS
Access .mdb file, I tried going back to the funciton and removing the
comment markers for the four lines starting with 'Dim rs As
DAO.Database... and then commented out the two lines starting with
"Dim rs As New ADODB.Recordset... No joy there either.

Any ideas?

Jeff- Hide quoted text -

- Show quoted text -

John,

--There is a space between CorporateMatterInfo. and MatterInfo. --DUH!
--[CorporateMatterInfo.ClientNumberA] is improperly bracketed. --
DOUBLE DUH!!
[CorporateMatterInfo].[ClientNumberA]
--You are assuming that ClientNumberA is a number field and not
text.-- It is a number field.

And I agree that the last section should be changed to ", Chr(13) &
Chr(10))"

I copied and pasted your version of the query field using the
concatentate function into the query. I still got the "undefined
function..." error.

Your help is appreciated.

Jeff

 

[John Spencer]

If the function works in one place and not in another then I am baffled.

Can you confirm that the function exists and that the module and the
function do not share the same name?

Have you tried to use the concatenate function elsewhere? Have you tried a
simple use of the concatenate function in the VBA immediate window? Just to
see if you get an error
?Concatenate("SELECT someField From SomeTable Where SomeField Like 'A*' ")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[jeff]

If the function works in one place and not in another then I am baffled.

Can you confirm that the function exists and that the module and the
function do not share the same name?

Have you tried to use the concatenate function elsewhere? Have you tried a
simple use of the concatenate function in the VBA immediate window? Just to
see if you get an error
?Concatenate("SELECT someField From SomeTable Where SomeField Like 'A*' ")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

John,

--There is a space between CorporateMatterInfo. and MatterInfo. --DUH!
--[CorporateMatterInfo.ClientNumberA] is improperly bracketed. --
DOUBLE DUH!!
[CorporateMatterInfo].[ClientNumberA]
--You are assuming that ClientNumberA is a number field and not
text.-- It is a number field.

And I agree that the last section should be changed to ", Chr(13) &
Chr(10))"

I copied and pasted your version of the query field using the
concatentate function into the query. I still got the "undefined
function..." error.

Your help is appreciated.

Jeff- Hide quoted text -

- Show quoted text -

John,

I can run a query that uses it (one of the five queries gathering data
records from the other five tables: litigation, guardian, etc...) ,
and then try to run the query (pulling data from the Union query) that
is trying to summarize all of those records. If it is the first time I
try to run the query (just loading Access and then clicking on the
Queries list and double-clicking on the summary query) I get a Run-
time 3061 error stating that I have "too few paramters. Expected 1."
The field using the concatenate query is a copy of the one that you
listed in a prior response. By my review, the function requires only
one (the second being optional) and when I add a watch in the
Concatenate for the pstrSQL variable, I get the entire select
statement, as I expect.

If, after I get this run-time error, I try to close out of the VBA
environment and return to Access and run it again (in any of the
queries) I receive the "Undefined function..." message.

Does this help?

Thanks,
Jeff

 

[John Spencer]

I have no idea why this won't work for you. If I get time I will try to
duplicate your query set up and see if the same error occurs for me.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

If the function works in one place and not in another then I am baffled.

Can you confirm that the function exists and that the module and the
function do not share the same name?

Have you tried to use the concatenate function elsewhere? Have you tried
a
simple use of the concatenate function in the VBA immediate window? Just
to
see if you get an error
?Concatenate("SELECT someField From SomeTable Where SomeField Like 'A*'
")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

John,

--There is a space between CorporateMatterInfo. and MatterInfo. --DUH!
--[CorporateMatterInfo.ClientNumberA] is improperly bracketed. --
DOUBLE DUH!!
[CorporateMatterInfo].[ClientNumberA]
--You are assuming that ClientNumberA is a number field and not
text.-- It is a number field.

And I agree that the last section should be changed to ", Chr(13) &
Chr(10))"

I copied and pasted your version of the query field using the
concatentate function into the query. I still got the "undefined
function..." error.

Your help is appreciated.

Jeff- Hide quoted text -

- Show quoted text -

John,

I can run a query that uses it (one of the five queries gathering data
records from the other five tables: litigation, guardian, etc...) ,
and then try to run the query (pulling data from the Union query) that
is trying to summarize all of those records. If it is the first time I
try to run the query (just loading Access and then clicking on the
Queries list and double-clicking on the summary query) I get a Run-
time 3061 error stating that I have "too few paramters. Expected 1."
The field using the concatenate query is a copy of the one that you
listed in a prior response. By my review, the function requires only
one (the second being optional) and when I add a watch in the
Concatenate for the pstrSQL variable, I get the entire select
statement, as I expect.

If, after I get this run-time error, I try to close out of the VBA
environment and return to Access and run it again (in any of the
queries) I receive the "Undefined function..." message.

Does this help?

Thanks,
Jeff

 

[jeff]

I have no idea why this won't work for you. If I get time I will try to
duplicate your query set up and see if the same error occurs for me.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

If the function works in one place and not in another then I am baffled.
Can you confirm that the function exists and that the module and the
function do not share the same name?
Have you tried to use the concatenate function elsewhere? Have you tried
a
simple use of the concatenate function in the VBA immediate window? Just
to
see if you get an error
?Concatenate("SELECT someField From SomeTable Where SomeField Like 'A*'
")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
John,
--There is a space between CorporateMatterInfo. and MatterInfo. --DUH!
--[CorporateMatterInfo.ClientNumberA] is improperly bracketed. --
DOUBLE DUH!!
[CorporateMatterInfo].[ClientNumberA]
--You are assuming that ClientNumberA is a number field and not
text.-- It is a number field.
And I agree that the last section should be changed to ", Chr(13) &
Chr(10))"
I copied and pasted your version of the query field using the
concatentate function into the query. I still got the "undefined
function..." error.
Your help is appreciated.
Jeff- Hide quoted text -
- Show quoted text -

John,

I can run a query that uses it (one of the five queries gathering data
records from the other five tables: litigation, guardian, etc...) ,
and then try to run the query (pulling data from the Union query) that
is trying to summarize all of those records. If it is the first time I
try to run the query (just loading Access and then clicking on the
Queries list and double-clicking on the summary query) I get a Run-
time 3061 error stating that I have "too few paramters. Expected 1."
The field using the concatenate query is a copy of the one that you
listed in a prior response. By my review, the function requires only
one (the second being optional) and when I add a watch in the
Concatenate for the pstrSQL variable, I get the entire select
statement, as I expect.

If, after I get this run-time error, I try to close out of the VBA
environment and return to Access and run it again (in any of the
queries) I receive the "Undefined function..." message.

Does this help?

Thanks,
Jeff- Hide quoted text -

- Show quoted text -

OK,

I'm so close--I think the problem was that, as I had explained above,
I had done five previous queries to pull individual records from five
different tables. I then used a UNION query to consolidate the
records. What I had done (in trying to figure out why the last
concatenate query wasn't working was to go back and change seom of the
original queries. I thought I hadn't saved the changes, but it was
very late (so late it was morning) and I guess I had saved the
changes, which had caused the field names to change. The error message
was doing a poor job of pointing that out..

Anyway, now the query is working (no error message, anyway) but the
result is more than I want.
Given that I have a table that has the two following records:

ClientNumberA CCODE UniqueID SUMMI
2 XXXC 2 - XXXC
corporate Matter: ABC Company
2 XXXC 2 - XXXC
Estate Planning Matter: 3 - B2222 - 3/10/1997

My concatenate query should give me the following result (where the
SUMMI field is a text field)

ClientNumberA CCODE UniqueID SUMMI
2 XXXC 2 - XXXC
corporate Matter: ABC Company

Estate Planning Matter: 3 - B2222 - 3/10/1997 (where this is a two
line text field-but all in one record.)

Instead, I am getting two records with the above values: the
Concatenate part is doing what it supposed to, but the resulting table
is not eliminating the second record (which is a duplicate of the
first.) I tried Select DISTINCT, but that didn't help.

Any ideas? Get this one, and it's done!

Thanks,
Jeff

 

[John Spencer]

I would expect the following to work for you

SELECT DISTINCT ClientNumberA
, CCODE
, UniqueID
Concatenate("Select Distinct CorporateMatterInfo.MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo].[ClientNumberA] & " ORDER BY MatterInfo", Chr(13) &
Chr(10)) as AllMatterInfo

FROM AllMatterInfoRecs

Note that MatterInfo is not included in the main query.
Also note that I included an order by clause in the Concatenate expression
just to ensure that the values were being returned in the same order.

IF you include MatterInfo in the main query then Distinct will give you a
record for each MatterInfo.

I know you said you included Distinct, but I wasn't sure if you meant in the
main query or in the sub query.

By the way it almost always helps to post the SQL of the query you are
having a problem with.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[jeff]

I would expect the following to work for you

SELECT DISTINCT ClientNumberA
, CCODE
, UniqueID
Concatenate("Select Distinct CorporateMatterInfo.MatterInfo
FROM AllMatterInfoRecs WHERE ClientNumberA = " &
[CorporateMatterInfo].[ClientNumberA] & " ORDER BY MatterInfo", Chr(13) &
Chr(10)) as AllMatterInfo

FROM AllMatterInfoRecs

Note that MatterInfo is not included in the main query.
Also note that I included an order by clause in the Concatenate expression
just to ensure that the values were being returned in the same order.

IF you include MatterInfo in the main query then Distinct will give you a
record for each MatterInfo.

I know you said you included Distinct, but I wasn't sure if you meant in the
main query or in the sub query.

By the way it almost always helps to post the SQL of the query you are
having a problem with.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

OK,

I'm so close--I think the problem was that, as I had explained above,
I had done five previous queries to pull individual records from five
different tables. I then used a UNION query to consolidate the
records. What I had done (in trying to figure out why the last
concatenate query wasn't working was to go back and change seom of the
original queries. I thought I hadn't saved the changes, but it was
very late (so late it was morning) and I guess I had saved the
changes, which had caused the field names to change. The error message
was doing a poor job of pointing that out..

Anyway, now the query is working (no error message, anyway) but the
result is more than I want.
Given that I have a table that has the two following records:

ClientNumberA CCODE UniqueID SUMMI
2 XXXC 2 - XXXC
corporate Matter: ABC Company
2 XXXC 2 - XXXC
Estate Planning Matter: 3 - B2222 - 3/10/1997

My concatenate query should give me the following result (where the
SUMMI field is a text field)

ClientNumberA CCODE UniqueID SUMMI
2 XXXC 2 - XXXC
corporate Matter: ABC Company

Estate Planning Matter: 3 - B2222 - 3/10/1997 (where this is a two
line text field-but all in one record.)

Instead, I am getting two records with the above values: the
Concatenate part is doing what it supposed to, but the resulting table
is not eliminating the second record (which is a duplicate of the
first.) I tried Select DISTINCT, but that didn't help.

Any ideas? Get this one, and it's done!

Thanks,
Jeff- Hide quoted text -

- Show quoted text -

OK,

I am now getting an error that says that I am passing too few
paramters (expecting 1.) Here's the SQL:

SELECT DISTINCT AllMatterInfoRecs.CorporateMatterInfo.ClientNumberA,
AllMatterInfoRecs.CorporateMatterInfo.CCode,
AllMatterInfoRecs.CorporateMatterInfo.UniqueID, Concatenate("SELECT
DISTINCT [CorporateMatterInfo].[MatterInfo] FROM AllMatterInfoRecs
WHERE ClientNumberA = " & CorporateMatterInfo.ClientNumberA,Chr(13) &
Chr(10)) AS SumMatterInfo
FROM AllMatterInfoRecs;

I'm sorry--I just can't see what's wrong. I've tried rewriting this,
but I still can't get it. I'm hoping a new set of eyes will help.

Thanks in advance.

Jeff

 

[John Spencer]

I'm sorry but I don't understand the references to tables and fields with
three parts in Access such as
AllMatterInfoRecs.CorporateMatterInfo.ClientNumberA
That normally should be something like
AllMatterInfoRecs.ClientNumberA

In the SQL string passed to the Concatenate function you have a reference to
a table in the Select clause that doesn't exist in the FROM clause
[CorporateMatterInfo].[MatterInfo] FROM AllMatterInfoRecs
Should that be
.... [AllMatterInfoRecs].[MatterInfo] FROM AllMatterInfoRecs ...
or should it be
.... [CorporateMatterInfo].[MatterInfo] FROM CorporateMatterInfo ...


SELECT DISTINCT AllMatterInfoRecs.ClientNumberA,
AllMatterInfoRecs.CCode,
AllMatterInfoRecs.UniqueID
, Concatenate("SELECT DISTINCT [AllMatterInfoRecs].[MatterInfo]
FROM AllMatterInfoRecs
WHERE ClientNumberA = " & CorporateMatterInfo.ClientNumberA,Chr(13) &
Chr(10)) AS SumMatterInfo
FROM AllMatterInfoRecs;

 

[jeff]

I'm sorry but I don't understand the references to tables and fields with
three parts in Access such as
AllMatterInfoRecs.CorporateMatterInfo.ClientNumberA
That normally should be something like
AllMatterInfoRecs.ClientNumberA

In the SQL string passed to the Concatenate function you have a reference to
a table in the Select clause that doesn't exist in the FROM clause
[CorporateMatterInfo].[MatterInfo] FROM AllMatterInfoRecs
Should that be
... [AllMatterInfoRecs].[MatterInfo] FROM AllMatterInfoRecs ...
or should it be
... [CorporateMatterInfo].[MatterInfo] FROM CorporateMatterInfo ...

SELECT DISTINCT AllMatterInfoRecs.ClientNumberA,
AllMatterInfoRecs.CCode,
AllMatterInfoRecs.UniqueID
, Concatenate("SELECT DISTINCT [AllMatterInfoRecs].[MatterInfo]
FROM AllMatterInfoRecs
WHERE ClientNumberA = " & CorporateMatterInfo.ClientNumberA,Chr(13) &
Chr(10)) AS SumMatterInfo
FROM AllMatterInfoRecs;

I am now getting an error that says that I am passing too few
paramters (expecting 1.) Here's the SQL:

SELECT DISTINCT AllMatterInfoRecs.CorporateMatterInfo.ClientNumberA,
AllMatterInfoRecs.CorporateMatterInfo.CCode,
AllMatterInfoRecs.CorporateMatterInfo.UniqueID, Concatenate("SELECT
DISTINCT [CorporateMatterInfo].[MatterInfo] FROM AllMatterInfoRecs
WHERE ClientNumberA = " & CorporateMatterInfo.ClientNumberA,Chr(13) &
Chr(10)) AS SumMatterInfo
FROM AllMatterInfoRecs;

I'm sorry--I just can't see what's wrong. I've tried rewriting this,
but I still can't get it. I'm hoping a new set of eyes will help.

Thanks in advance.

Jeff- Hide quoted text -

- Show quoted text -

The table that I reference AllMatterInfoRecs is the result of a UNION
query by the same name. As the UNION query takes the field names from
the first table in the UNION (there are five tables joined via the
UNION query) the field names listed in the resulting query (from which
I am using the above query) lists the field names as:

CorporateMatterInfo.ClientNumberA
CorporateMatterInfo.CCode
CorporateMatterInfo.UniqueID
CorporateMatterInfo.MatterInfo

I initially tried using just the syntax you describe
(AllMatterInfoRecs.ClientNumberA) and got an error message. I honestly
don't remember what it was now, but that was the reason I changed to
the syntax you see in the query.

Jeff