Concatenate Field, Count Characters and Shorten Value

[Dean]

Hi, I have a table with non-unique identifiers. I need to take all
the values with the same ID's and combine them into one field with a
semicolon as a seperator. These values may exceed 255 characters. I
then need to count the values in the cell and see if it adds up to 240
or more and then shorten that field by cutting off the excess and
adding only "...and other". So, to recap, I need to summarise values
based on the ID field, count the characters and then cut the field off
and add the text.

I was thinking I could summarize the table somehow, then use a update
query or script to add a character count to a new field in the table
and then shorten those to 240 characters. Then update the field by
adding the text. This all said, I have absolutely no idea how to do
this! Please help.

Dean

[Salad]

Dean wrote:

Have you considered a MEMO field? You can hold lots of data in a memo.
You can even display it on a form with scroll bars.

[Bob Quintal]

Dean <deanca...@yahoo.com> wrote in
news:1191873523.8...@50g2000hsm.googlegroups.com:

Find the fConcatChild() function by Google search on this group,
That summarises the row, use the Left() function to limit to 240
characters.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dean]

> You can even display it on a form with scroll bars.- Hide quoted text -
>
> - Show quoted text -

Hi ...Salad.

Yes, I'm aware of the memo field type, but the thing is that it needs
to go back into software, which doesn't support memo fields. The
largest it can go there is 255 in a text field. It's quite a bugger,
otherwise it would've been a lot easier I guess.

Cheers anyway mate.

Dean

[Dean]

Hi Bob,

Right, first of all, I can't find any reference to fConcatChild under
Access Help. Do I need to add this from a library? I got the posts
on this site though, but it seems to all over the place and not really
the answer I need. Also, I need to identify the cells with more than
240 characters, so I can add the text "...and other" to the end of
it. Is there a way to count characters?

If I have a field named PARCEL_ID in a table named OWNERS_JOINED, then
how do I concatenate the OWNER_NAME field based on the PARCEL_ID
field?

Cheers mate

Dean

[bob.q...@gmail.com]

On Oct 8, 10:39 pm, Dean <deancarst...@yahoo.com> wrote:
> Hi Bob,
>
> Right, first of all, I can't find any reference to fConcatChild under
> Access Help. Do I need to add this from a library? I got the posts
> on this site though, but it seems to all over the place and not really
> the answer I need. Also, I need to identify the cells with more than
> 240 characters, so I can add the text "...and other" to the end of
> it. Is there a way to count characters?
>

You will not find any reference to it in access, it's a user-defined
function written by Dev Ashish that you will need to paste into a
code
module as described on the web page where it lives.
http://www.mvps.org/access/modules/mdl0004.htm

To count characters, you just use the len() function. To cut the
string
to 240 characters use the left function.

I suggest that first you will need to create a temporary table, with
the Parcel_ID and a memo type field to hold the whole string, then
use
the temp table to trim to length and add the "...and other" to the
long
strings, then move the results to their new home.

> If I have a field named PARCEL_ID in a table named OWNERS_JOINED, then
> how do I concatenate the OWNER_NAME field based on the PARCEL_ID
> field?
>
> Cheers mate
>
> Dean

The following queries will do that

INSERT into Temp1 (PARCEL_ID,OWNERS_JOINED)
SELECT DISTINCT PARCEL_ID, fConcatChild("OWNERS_JOINED",
"PARCEL_ID","OWNER_NAME","STRING",[PARCEL_ID]) FROM OWNERS_JOINED

INSERT into NewTable1 (PARCEL_ID,OWNERS_JOINED)
SELECT PARCEL_ID, IIF( LEN(OWNERS_JOINED)>240,LEFT(OWNERS_JOINED,240)
&
"...and more",OWNERS_JOINED) FROM temp1