Calculated Field Length Error

[Andrew]

I'm developing a database in Access 2003 and having trouble with the
calculated fields in one of my query. The database is designed to search for
key words in a long string of text. Data is imported into the database from
an excel spreadsheet where there are approximately 50 rows (fields). It
doesn't matter which of the 50 fields a key word appears in, so to simply the
search process in Access, I'm trying to lump all the fields together into one
very large field called "AggregateText."

I have a query called qryAggregateText, which has a calculated field
"AggregateText" which is defined as:

AggregateText: [TextField1] + [TextField2] +[TextField3]...[TextField50].

When I finish typing this expression into the query design view, I get an
error that reads: "The expression you entered is missing a closing
parenthesis, bracket (]), or vertical bar (|)."

When I go back into the expression, I notice that the last character or two
are cut off. I assumed this meant the expression was two long, so I tried
breaking it up into several calculated fields which add together to form
AggregateText. I still get the same error, even when there are only 777
characters in the expression. What is puzzling is that the expression
doesn't get cut down to 256 characters, only one or two are removed.

Any suggestions on what is causing this error and how I can avoid it?

Thanks for your help,
Andrew

 

[G. Vaught]

First of all, if that is how you have to add values no wonder you are having
issues. Your data needs serious evaluation.

If you are just trying to concatenate values, then you are using the wrong
formula. You need to make your AggregateText a memo field in order to accept
greater than 255. You also need to concatenate using [fieldname]& " " &
[fieldname2] etc.

Get your terminology correct. Fields are columns, rows are records. How many
records do you have and how many fields(columns) do you have?

 

[Andrew]

Apologies for the poorly written email. What I meant to say was that the
data spreadsheet has about 50 columns (i.e. fields). These 50 columns need
to be concatenated into one string of text so that I can run a query on it to
search for key words. The SQL statement for my keyword search query is as
follows:

SELECT qryAggregateText.ID, tblKeyWords.Term
FROM qryAggregateText LEFT JOIN tblKeyWords ON
qryAggregateText.AggregateText Like "*" & tblKeyWords.Term & "*";

The query qryAggregateText is defined as: [Field1] & [Field2] & [Field3].
There are no plus signs (I copied it incorrectly in the original post,
sorry). Also, there is no need for spaces between the text, so the " " is
not necessary. It is my understanding that the Select query noted above will
not run on a memo field, and that the Like statement can only be used with
text fields, which is why I am not dumping the data into a memo field in a
table and running the query on the table, but instead using the aggregate
text query as the source.

Please let me know if this logic is still faulty and if you have any
suggestions as to how to correct it. Thanks for the advice.

First of all, if that is how you have to add values no wonder you are having
issues. Your data needs serious evaluation.

If you are just trying to concatenate values, then you are using the wrong
formula. You need to make your AggregateText a memo field in order to accept
greater than 255. You also need to concatenate using [fieldname]& " " &
[fieldname2] etc.

Get your terminology correct. Fields are columns, rows are records. How many
records do you have and how many fields(columns) do you have?

I'm developing a database in Access 2003 and having trouble with the
calculated fields in one of my query. The database is designed to search
for
key words in a long string of text. Data is imported into the database
from
an excel spreadsheet where there are approximately 50 rows (fields). It
doesn't matter which of the 50 fields a key word appears in, so to simply
the
search process in Access, I'm trying to lump all the fields together into
one
very large field called "AggregateText."

I have a query called qryAggregateText, which has a calculated field
"AggregateText" which is defined as:

AggregateText: [TextField1] + [TextField2] +[TextField3]...[TextField50].

When I finish typing this expression into the query design view, I get an
error that reads: "The expression you entered is missing a closing
parenthesis, bracket (]), or vertical bar (|)."

When I go back into the expression, I notice that the last character or
two
are cut off. I assumed this meant the expression was two long, so I tried
breaking it up into several calculated fields which add together to form
AggregateText. I still get the same error, even when there are only 777
characters in the expression. What is puzzling is that the expression
doesn't get cut down to 256 characters, only one or two are removed.

Any suggestions on what is causing this error and how I can avoid it?

Thanks for your help,
Andrew