Concatenate multiple entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I used the example database DUANE HOOKOM has on a website, so I hoping he
sees this and responds. I am not by any means a programmer and welcome all
input. A person in my company used crystal reports to dump all of the
information into an Access database. Every incident is numbered like so
YYYYMMDD####. There are multiple narratives per incident : 1 through
infinity. There are then multiple line numbers per narrative. The data has
been extracted from a DB2 using crystal reports. I was told don't ask, but
that format had to be this way.

Example data
strIncidentNumber intNarrativeNumber intNarrativeLineNumber strNarrative
200508100027 1 1 blah blah
200508100027 1 2
200508100027 1 3
200508100027 1 4
200508100027 1 5
200508100027 1 6
200508100027 1 7
200508100027 1 8
200508100027 1 9
200508100027 2 1
200508100027 2 2
200508100027 2 3
200508100027 2 4
200508100027 2 5
200508100027 3 1
200508100027 3 2
200508100027 3 3
200508100027 3 4
200508100027 3 5

When I run the code I have I get :

Run-time error '2147217900 (80040e14)

Syntax error (missing operator) in query expression
'RMSandNameNarrativeNo = 200508100009 1'

Here is the Code in the query : Narrative: Concatenate("SELECT
strNarrativeText FROM Crexport5 WHERE RMSandNarrativeNo =" &
[RMSandNarrativeNo])

Here is my table setup

Table1
RMSandNarrativeNo text PRIMARY KEY
RMSno

CREXPORT5
RMSandNarrativeNo text FOREIGN KEY
strIncidentNumber text
intIncidentNarrativeNumber Number
intNarrativeLineNumber Number
intNarrativeType Text
strNarrativeText Text 255 characters

Thanks
Bryan
 
Not Duane, but
You need to use text delimiters in the query you are passing to the
function. RMSandNarrativeNo is a text field - that contains number
characters. One way to do this is to use the following.

Narrative: Concatenate("SELECT strNarrativeText FROM Crexport5 WHERE
RMSandNarrativeNo =""" & [RMSandNarrativeNo] & """")
 
John, you are the man. I really need to learn this stuff. Thank you very
much, I may get lunch out of this, and if I could I would give it you.

John Spencer said:
Not Duane, but
You need to use text delimiters in the query you are passing to the
function. RMSandNarrativeNo is a text field - that contains number
characters. One way to do this is to use the following.

Narrative: Concatenate("SELECT strNarrativeText FROM Crexport5 WHERE
RMSandNarrativeNo =""" & [RMSandNarrativeNo] & """")



BDDewell408 said:
I used the example database DUANE HOOKOM has on a website, so I hoping he
sees this and responds. I am not by any means a programmer and welcome
all
input. A person in my company used crystal reports to dump all of the
information into an Access database. Every incident is numbered like so
YYYYMMDD####. There are multiple narratives per incident : 1 through
infinity. There are then multiple line numbers per narrative. The data
has
been extracted from a DB2 using crystal reports. I was told don't ask,
but
that format had to be this way.

Example data
strIncidentNumber intNarrativeNumber intNarrativeLineNumber strNarrative
200508100027 1 1 blah
blah
200508100027 1 2
200508100027 1 3
200508100027 1 4
200508100027 1 5
200508100027 1 6
200508100027 1 7
200508100027 1 8
200508100027 1 9
200508100027 2 1
200508100027 2 2
200508100027 2 3
200508100027 2 4
200508100027 2 5
200508100027 3 1
200508100027 3 2
200508100027 3 3
200508100027 3 4
200508100027 3 5

When I run the code I have I get :

Run-time error '2147217900 (80040e14)

Syntax error (missing operator) in query expression
'RMSandNameNarrativeNo = 200508100009 1'

Here is the Code in the query : Narrative: Concatenate("SELECT
strNarrativeText FROM Crexport5 WHERE RMSandNarrativeNo =" &
[RMSandNarrativeNo])

Here is my table setup

Table1
RMSandNarrativeNo text PRIMARY KEY
RMSno

CREXPORT5
RMSandNarrativeNo text FOREIGN KEY
strIncidentNumber text
intIncidentNarrativeNumber Number
intNarrativeLineNumber Number
intNarrativeType Text
strNarrativeText Text 255 characters

Thanks
Bryan
Click to expand...
Click to expand...
 
Lunch? Did I hear someone say lunch? Why would you share with John Spencer
and not me? Where are you located and what's on the menu?

Duane Hookom
MS Access MVP


BDDewell408 said:
John, you are the man. I really need to learn this stuff. Thank you very
much, I may get lunch out of this, and if I could I would give it you.

John Spencer said:
Not Duane, but
You need to use text delimiters in the query you are passing to the
function. RMSandNarrativeNo is a text field - that contains number
characters. One way to do this is to use the following.

Narrative: Concatenate("SELECT strNarrativeText FROM Crexport5 WHERE
RMSandNarrativeNo =""" & [RMSandNarrativeNo] & """")



BDDewell408 said:
I used the example database DUANE HOOKOM has on a website, so I hoping
he
sees this and responds. I am not by any means a programmer and welcome
all
input. A person in my company used crystal reports to dump all of the
information into an Access database. Every incident is numbered like
so
YYYYMMDD####. There are multiple narratives per incident : 1 through
infinity. There are then multiple line numbers per narrative. The
data
has
been extracted from a DB2 using crystal reports. I was told don't ask,
but
that format had to be this way.

Example data
strIncidentNumber intNarrativeNumber intNarrativeLineNumber
strNarrative
200508100027 1 1 blah
blah
200508100027 1 2
200508100027 1 3
200508100027 1 4
200508100027 1 5
200508100027 1 6
200508100027 1 7
200508100027 1 8
200508100027 1 9
200508100027 2 1
200508100027 2 2
200508100027 2 3
200508100027 2 4
200508100027 2 5
200508100027 3 1
200508100027 3 2
200508100027 3 3
200508100027 3 4
200508100027 3 5

When I run the code I have I get :

Run-time error '2147217900 (80040e14)

Syntax error (missing operator) in query expression
'RMSandNameNarrativeNo = 200508100009 1'

Here is the Code in the query : Narrative: Concatenate("SELECT
strNarrativeText FROM Crexport5 WHERE RMSandNarrativeNo =" &
[RMSandNarrativeNo])

Here is my table setup

Table1
RMSandNarrativeNo text PRIMARY KEY
RMSno

CREXPORT5
RMSandNarrativeNo text FOREIGN KEY
strIncidentNumber text
intIncidentNarrativeNumber Number
intNarrativeLineNumber Number
intNarrativeType Text
strNarrativeText Text 255 characters

Thanks
Bryan
Click to expand...
Click to expand...
Click to expand...
 
Golleeee!
Duane, it is your solution. I just posted a fix to his calling code.

So if the poster is willing to share the lunch and you can get to the
location, please do so. I do ask you to let me know what I missed.

Duane Hookom said:
Lunch? Did I hear someone say lunch? Why would you share with John Spencer
and not me? Where are you located and what's on the menu?

Duane Hookom
MS Access MVP


BDDewell408 said:
John, you are the man. I really need to learn this stuff. Thank you
very
much, I may get lunch out of this, and if I could I would give it you.

John Spencer said:
Not Duane, but
You need to use text delimiters in the query you are passing to the
function. RMSandNarrativeNo is a text field - that contains number
characters. One way to do this is to use the following.

Narrative: Concatenate("SELECT strNarrativeText FROM Crexport5 WHERE
RMSandNarrativeNo =""" & [RMSandNarrativeNo] & """")



I used the example database DUANE HOOKOM has on a website, so I hoping
he
sees this and responds. I am not by any means a programmer and
welcome
all
input. A person in my company used crystal reports to dump all of the
information into an Access database. Every incident is numbered like
so
YYYYMMDD####. There are multiple narratives per incident : 1 through
infinity. There are then multiple line numbers per narrative. The
data
has
been extracted from a DB2 using crystal reports. I was told don't
ask,
but
that format had to be this way.

Example data
strIncidentNumber intNarrativeNumber intNarrativeLineNumber
strNarrative
200508100027 1 1
blah
blah
200508100027 1 2
200508100027 1 3
200508100027 1 4
200508100027 1 5
200508100027 1 6
200508100027 1 7
200508100027 1 8
200508100027 1 9
200508100027 2 1
200508100027 2 2
200508100027 2 3
200508100027 2 4
200508100027 2 5
200508100027 3 1
200508100027 3 2
200508100027 3 3
200508100027 3 4
200508100027 3 5

When I run the code I have I get :

Run-time error '2147217900 (80040e14)

Syntax error (missing operator) in query expression
'RMSandNameNarrativeNo = 200508100009 1'

Here is the Code in the query : Narrative: Concatenate("SELECT
strNarrativeText FROM Crexport5 WHERE RMSandNarrativeNo =" &
[RMSandNarrativeNo])

Here is my table setup

Table1
RMSandNarrativeNo text PRIMARY KEY
RMSno

CREXPORT5
RMSandNarrativeNo text FOREIGN KEY
strIncidentNumber text
intIncidentNarrativeNumber Number
intNarrativeLineNumber Number
intNarrativeType Text
strNarrativeText Text 255 characters

Thanks
Bryan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top