Concatenation of Text as Expression In A Query

G

Guest

Hello:

I have found a previous question on this site called "Sum of Text fields?"
from 8/19/04 and it describes what I am trying to accomplish. I have this:

DESCRIPTION TEXT
ELX12 text1
ELX12 text2
ELX12 text3

and what I want to display is this:

DESCRIPTION TEXT
ELX12 text1, text2, text3

My tables are linked ODBC Tables and I have set a 1-to-M relationship from
Description-to-Text

This is the expression I am trying to use, but I keep getting either syntax
or undefined function errors. So I'm looking for some help. Thanks!

Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
 
G

Guest

All I get are some fields which display the number "0"

Dirtbike said:
Have you Tried [TEXT]=[THIS]&", "&[THAT]&", "&[THE_OTHER] ?

la knight said:
Hello:

I have found a previous question on this site called "Sum of Text fields?"
from 8/19/04 and it describes what I am trying to accomplish. I have this:

DESCRIPTION TEXT
ELX12 text1
ELX12 text2
ELX12 text3

and what I want to display is this:

DESCRIPTION TEXT
ELX12 text1, text2, text3

My tables are linked ODBC Tables and I have set a 1-to-M relationship from
Description-to-Text

This is the expression I am trying to use, but I keep getting either syntax
or undefined function errors. So I'm looking for some help. Thanks!

Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
Click to expand...
Click to expand...
 
D

Duane Hookom

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP
--

la knight said:
All I get are some fields which display the number "0"

Dirtbike said:
Have you Tried [TEXT]=[THIS]&", "&[THAT]&", "&[THE_OTHER] ?

la knight said:
Hello:

I have found a previous question on this site called "Sum of Text
fields?"
from 8/19/04 and it describes what I am trying to accomplish. I have
this:

DESCRIPTION TEXT
ELX12 text1
ELX12 text2
ELX12 text3

and what I want to display is this:

DESCRIPTION TEXT
ELX12 text1, text2, text3

My tables are linked ODBC Tables and I have set a 1-to-M relationship
from
Description-to-Text

This is the expression I am trying to use, but I keep getting either
syntax
or undefined function errors. So I'm looking for some help. Thanks!

Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Duane, thanks for your reply! Yes, it seems like a very simple expression
to use but when I apply it to my tables I get an error which says that the
function Concatenante is undefined. Keep in mind I have two linked tables via
ODBC in my DB that I have no control over. I can only pull the info from
them. Here is my SQL. Maybe someone can tell me why it's not working.

SELECT V_ORDER_LINES.PART, V_ORDER_TEXT.ORDER_NO, V_ORDER_LINES.DESCRIPTION,
Concatenante("SELECT TEXT & Chr(9) & Role FROM V_ORDER_TEXT WHERE TEXT = " &
[TEXT],Chr(13) & Chr(10)) AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15"));


Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP
--

la knight said:
All I get are some fields which display the number "0"

Dirtbike said:
Have you Tried [TEXT]=[THIS]&", "&[THAT]&", "&[THE_OTHER] ?

:

Hello:

I have found a previous question on this site called "Sum of Text
fields?"
from 8/19/04 and it describes what I am trying to accomplish. I have
this:

DESCRIPTION TEXT
ELX12 text1
ELX12 text2
ELX12 text3

and what I want to display is this:

DESCRIPTION TEXT
ELX12 text1, text2, text3

My tables are linked ODBC Tables and I have set a 1-to-M relationship
from
Description-to-Text

This is the expression I am trying to use, but I keep getting either
syntax
or undefined function errors. So I'm looking for some help. Thanks!

Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

********* UPDATE FROM MY PREVIOUS POST ***********

I installed the basConcatenate Module into my DB from your DB and
un-commented the lines in the code for DOA and this is the error I get when I
try to run the query:

"Ambiguous name. in query expression..." (then what follows is my SQL which
I posted in the previous post.

Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP
--

la knight said:
All I get are some fields which display the number "0"

Dirtbike said:
Have you Tried [TEXT]=[THIS]&", "&[THAT]&", "&[THE_OTHER] ?

:

Hello:

I have found a previous question on this site called "Sum of Text
fields?"
from 8/19/04 and it describes what I am trying to accomplish. I have
this:

DESCRIPTION TEXT
ELX12 text1
ELX12 text2
ELX12 text3

and what I want to display is this:

DESCRIPTION TEXT
ELX12 text1, text2, text3

My tables are linked ODBC Tables and I have set a 1-to-M relationship
from
Description-to-Text

This is the expression I am trying to use, but I keep getting either
syntax
or undefined function errors. So I'm looking for some help. Thanks!

Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

******* UPDATED ********

I also adjusted my query language to (see below) and now I am getting a
Compile Error which says "Duplicate declaration in current scope" ?????

Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP
--

la knight said:
All I get are some fields which display the number "0"

Dirtbike said:
Have you Tried [TEXT]=[THIS]&", "&[THAT]&", "&[THE_OTHER] ?

:

Hello:

I have found a previous question on this site called "Sum of Text
fields?"
from 8/19/04 and it describes what I am trying to accomplish. I have
this:

DESCRIPTION TEXT
ELX12 text1
ELX12 text2
ELX12 text3

and what I want to display is this:

DESCRIPTION TEXT
ELX12 text1, text2, text3

My tables are linked ODBC Tables and I have set a 1-to-M relationship
from
Description-to-Text

This is the expression I am trying to use, but I keep getting either
syntax
or undefined function errors. So I'm looking for some help. Thanks!

Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
Click to expand...
Click to expand...
Click to expand...
 
A

Ann Scharpf via AccessMonster.com

I had a similar problem in January and Duane helped me. This was the missing
last step in my case, maybe it'll help you:

Duane wrote:

Open your module and select Tools-References. Find the ADO library
(Microsoft ActiveX Data Objects library) with the highest version number and
check it. Then try to compile your application by Debug->Compile.


Hope this helps.

Ann Scharpf

la said:
******* UPDATED ********

I also adjusted my query language to (see below) and now I am getting a
Compile Error which says "Duplicate declaration in current scope" ?????
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Click to expand...
[quoted text clipped - 30 lines]
Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

"adjusted my query language" doesn't have much meaning to me. Can you
provide the sql view of your query. It would also help to know the data
types of all field referenced in the concatenate function.

--
Duane Hookom
MS Access MVP
--

la knight said:
******* UPDATED ********

I also adjusted my query language to (see below) and now I am getting a
Compile Error which says "Duplicate declaration in current scope" ?????

Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP
--

la knight said:
All I get are some fields which display the number "0"

:

Have you Tried [TEXT]=[THIS]&", "&[THAT]&", "&[THE_OTHER] ?

:

Hello:

I have found a previous question on this site called "Sum of Text
fields?"
from 8/19/04 and it describes what I am trying to accomplish. I have
this:

DESCRIPTION TEXT
ELX12 text1
ELX12 text2
ELX12 text3

and what I want to display is this:

DESCRIPTION TEXT
ELX12 text1, text2, text3

My tables are linked ODBC Tables and I have set a 1-to-M
relationship
from
Description-to-Text

This is the expression I am trying to use, but I keep getting either
syntax
or undefined function errors. So I'm looking for some help. Thanks!

Expr1:
[TEXT]=fConcatChild("V_ORDER_TEXT.TEXT","V_ORDER_LINES.DESCRIPTION","V_ORDER_TEXT.TEXT","Long",[TEXT])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OKAY - Here's where I stand now (I have tried so many things, I think I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION, V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION, V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ TEXT
15 472 1 DescriptionA 1
ABCD
15 472 1 DescriptionA 2
EFGH
15 472 2 DescriptionB 1
{this maybe blank}
15 472 2 DescriptionB 2
MY DOG
15 472 3 DescriptionB 3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION LINE_NUMERIC
PART TEXT_SEQ
TEXT
Any suggestions where I'm going wrong??????
 
D

Duane Hookom

You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

la knight said:
OKAY - Here's where I stand now (I have tried so many things, I think I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA 1
ABCD
15 472 1 DescriptionA 2
EFGH
15 472 2 DescriptionB 1
{this maybe blank}
15 472 2 DescriptionB 2
MY DOG
15 472 3 DescriptionB 3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
 
G

Guest

Yes - ORDER_NO is Text, the only field which is a number is LINE_NUMERIC

Duane Hookom said:
You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

la knight said:
OKAY - Here's where I stand now (I have tried so many things, I think I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA 1
ABCD
15 472 1 DescriptionA 2
EFGH
15 472 2 DescriptionB 1
{this maybe blank}
15 472 2 DescriptionB 2
MY DOG
15 472 3 DescriptionB 3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
Click to expand...
 
D

Duane Hookom

Did my reply resolve your issue?

--
Duane Hookom
MS Access MVP
--

la knight said:
Yes - ORDER_NO is Text, the only field which is a number is LINE_NUMERIC

Duane Hookom said:
You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

la knight said:
OKAY - Here's where I stand now (I have tried so many things, I think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Upon trying your new suggestion, I get this error messgae:
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's clause. Revise the SELECT
statement of the Subquery to request only one field"

Duane Hookom said:
You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

la knight said:
OKAY - Here's where I stand now (I have tried so many things, I think I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA 1
ABCD
15 472 1 DescriptionA 2
EFGH
15 472 2 DescriptionB 1
{this maybe blank}
15 472 2 DescriptionB 2
MY DOG
15 472 3 DescriptionB 3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
Click to expand...
 
G

Guest

Duane: please see me second response below this one!

Duane Hookom said:
Did my reply resolve your issue?

--
Duane Hookom
MS Access MVP
--

la knight said:
Yes - ORDER_NO is Text, the only field which is a number is LINE_NUMERIC

Duane Hookom said:
You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

OKAY - Here's where I stand now (I have tried so many things, I think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Are you sure you need V_ORDER_TEXT in the main query? I can't understand why
you need V_ORDER_TEXT stuff in the main query when this is your table in the
concatenate function....

Does this work?

SELECT PART, ORDER_NO, DESCRIPTION,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [ORDER_NO] &
""" ORDER BY TEXT_SEQ",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES
WHERE PART="CUSTOM15" AND
ORDER_NO="0000472"
ORDER BY DESCRIPTION;

--
Duane Hookom
MS Access MVP
--

la knight said:
Upon trying your new suggestion, I get this error messgae:
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's clause. Revise the
SELECT
statement of the Subquery to request only one field"

Duane Hookom said:
You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

la knight said:
OKAY - Here's where I stand now (I have tried so many things, I think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I tried both your suggestions again and now I'm getting syntax error.

Duane Hookom said:
Are you sure you need V_ORDER_TEXT in the main query? I can't understand why
you need V_ORDER_TEXT stuff in the main query when this is your table in the
concatenate function....

Does this work?

SELECT PART, ORDER_NO, DESCRIPTION,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [ORDER_NO] &
""" ORDER BY TEXT_SEQ",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES
WHERE PART="CUSTOM15" AND
ORDER_NO="0000472"
ORDER BY DESCRIPTION;

--
Duane Hookom
MS Access MVP
--

la knight said:
Upon trying your new suggestion, I get this error messgae:
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's clause. Revise the
SELECT
statement of the Subquery to request only one field"

Duane Hookom said:
You missed:
"It would also help to know the data types of all field referenced in the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

OKAY - Here's where I stand now (I have tried so many things, I think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I am having trouble seeing your "syntax error" (or your syntax) and really
don't know if you need V_ORDER_TEXT in your main query.

How about some help?

--
Duane Hookom
MS Access MVP
--

la knight said:
I tried both your suggestions again and now I'm getting syntax error.

Duane Hookom said:
Are you sure you need V_ORDER_TEXT in the main query? I can't understand
why
you need V_ORDER_TEXT stuff in the main query when this is your table in
the
concatenate function....

Does this work?

SELECT PART, ORDER_NO, DESCRIPTION,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [ORDER_NO] &
""" ORDER BY TEXT_SEQ",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES
WHERE PART="CUSTOM15" AND
ORDER_NO="0000472"
ORDER BY DESCRIPTION;

--
Duane Hookom
MS Access MVP
--

la knight said:
Upon trying your new suggestion, I get this error messgae:
"You have written a subquery that can return more than one field
without
using the EXISTS reserved word in the main query's clause. Revise the
SELECT
statement of the Subquery to request only one field"

:

You missed:
"It would also help to know the data types of all field referenced in
the
concatenate function"
I believe ORDER_NO is text since you compare it to "0000472".

Try:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT,
Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=""" & [V_ORDER_TEXT].[ORDER_NO] &
"""",Chr(13) & Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO =
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


--
Duane Hookom
MS Access MVP
--

OKAY - Here's where I stand now (I have tried so many things, I
think
I've
gone crazy)

This is the SQL (WITHOUT) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO
=
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;


This is the SQL (WITH) the concatenation expression:

SELECT V_ORDER_LINES.PART, V_ORDER_LINES.ORDER_NO,
V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ,
V_ORDER_TEXT.TEXT, Concatenate("SELECT [TEXT] & Chr(9) & Role FROM
[V_ORDER_TEXT] WHERE ORDER_NO=" & [V_ORDER_TEXT.ORDER_NO],Chr(13) &
Chr(10))
AS AddtLines
FROM V_ORDER_LINES LEFT JOIN V_ORDER_TEXT ON V_ORDER_LINES.ORDER_NO
=
V_ORDER_TEXT.ORDER_NO
WHERE (((V_ORDER_LINES.PART)="CUSTOM15") AND
((V_ORDER_LINES.ORDER_NO)="0000472"))
ORDER BY V_ORDER_TEXT.LINE_NUMERIC, V_ORDER_LINES.DESCRIPTION,
V_ORDER_TEXT.TEXT_SEQ;

WITHOUT the Concatenation Expression I get this:

PART ORDER NO LINE NO DESCRIPTION TEXT SEQ
TEXT
15 472 1 DescriptionA
1
ABCD
15 472 1 DescriptionA
2
EFGH
15 472 2 DescriptionB
1
{this maybe blank}
15 472 2 DescriptionB
2
MY DOG
15 472 3 DescriptionB
3
SKIP

THIS IS WHAT I NEED:

PART ORDER NO DESCRIPTION TEXT
15 472 DescriptionA ABCD
EFGH
15 472 DescriptionB
MY
DOG
SKIP

When I try to run the query with the Concatenation I get thi error:

Run-time error '-2147217904(80040e10)': No value given for one or
more
required parameters

These are the tables: (linked ODBC - I did not create them nor can
change
them)

TABLES: V_ORDER_LINES V_ORDER
TEXT
FIELDS: ORDER_NO (primary) 1-M ORDER_NO
DESCRIPTION
LINE_NUMERIC
PART
TEXT_SEQ

TEXT
Any suggestions where I'm going wrong??????
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Let me start over and try to simplify this query into a smaller piece which I
can then use to call upon in another query. My thought is perhaps the way
this table was constructed, I need to string together multiple fields and not
just one to get it to work. I boiled it down to using only ONE TABLE instead
V_ORDER_TEXT

If I use this SQL ---------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
FROM V_ORDER_TEXT
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE,
V_ORDER_TEXT.TEXT_SEQ, V_ORDER_TEXT.TEXT
HAVING (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)<>"ZZZZ"))
ORDER BY V_ORDER_TEXT.ORDER_LINE, V_ORDER_TEXT.TEXT_SEQ;

I get this ------------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001 ABCD
0000472 0010 002 EFGH
0000472 0030 001 UVW
0000472 0030 002 XYZ

And obviously I want this -----------

ORDER_NO ORDER_LINE TEXT_SEQ TEXT
0000472 0010 001,002 ABCD,EFGH
0000472 0030 001,002 UVW,XYZ

Maybe the table was constructed so that both TEXT_SEQ and TEXT need to be
concatenated???? Just a thought.

P.S. - I REALLY appreciate ALL you help!
 
D

Duane Hookom

Where did the field "ORDER_LINE" come from? It is fairly evident that this
was a very significant field in your desired result? Is the ORDER_LINE field
also available in the V_ORDER_LINES table?

First create a query
===qselOrders======
SELECT ORDER_NO, ORDER_LINE
FROM V_ORDER_TEXT
WHERE ORDER_NO="0000472" AND ORDER_LINE<>"ZZZZ"
GROUP BY ORDER_NO, ORDER_LINE;

Then create a query:
SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" & ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as TextLines
FROM qselOrders;

If ORDER_NO and ORDER_LINE are both in the V_ORDER_LINES table then you
should be able to replace the first query with V_ORDER_LINES.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to step through text and images in a given slide [powerpoint2007] 2
Set the Height/Width of a Text Box 1
Syntax help summing part of a txtbox 8
expression in query 2
Setting Fieldname in query with a combobox 6
Does not include the specified expression as part of an aggregatefunction 2
How to link to text within the email in a signature 4
Label text from dynamic array 1

Top