Access Text Field Problem

[khurley]

Hi Folks,

I am have a functioning db that pulls in Data from a SQL database into my Access Table. Using Access 2003. Trouble is i have found I ahve a problem with one of my fields.

I am currently using linked tables to the SQL data and then I have a make table query that creates the MAIN TABLE table. Then a couple other queries that update other fields in the MAIN TABLE.

One of the update queries pulls data from field called Defendants. When I look at it in access it shows as a memo field. I looked in SQL table and its set as ntext 16. I only want the first 50 bytes of this field in my text field in access.

I put this data into a TEXT field formatted at 50 characters.

For some reason on some records I am getting more data than 50 characters. I will give you an example:

I have the following data in the SQL field. I am showing it as it was entered on the screen:

230 FIFTH AVENUE ASSOCIATES,
(a New York Limited Partnership)
NEWMARK REAL ESTATE CORPORATION and
THE CITY OF NEW YORK


I want the first 50 bytes of this data in my text field.
Here is what I see:

230 FIFTH AVENUE ASSOCIATES,

and then I see a whole lot of spaces....but when i print out this table i get the following:

230 FIFTH AVENUE ASSOCIATES,
(a New York Limited


it doens't even show me the whole thing.... Is there a CRLF problem maybe??

When i looked at the table in SQL raw I noticed the field there looks the same as ACCESS. meaning I can see
230 FIFTH AVENUE ASSOCIATES, and then all i see is spaces.

However if i put my cursor next to the comman after Associates and click my delete button the text (a New York Limited appears...

any ideas????

Thanks
Kathy

 

[PHV]

Have you tried to play with the Left function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[khurley]

I'l not familiar with that function. Could you by any chance give me an example?

 

[Golom]

It is possible that you have carriage return (chr(13)), Line Feed (Chr(10)) or Tab (Chr(9)) characters in the string and that's causing weird display symptoms. You can get rid of them with

Replace(Replace(Replace([TheField],Chr(9),""),Chr(10),""),Chr(13)"")

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[khurley]

it is definitely possible!

Where would i put that code? Can i run that as part of the query or a macro? do i run it by itself??

 

[Golom]

All of the above are possible but in a query

Select
Replace(Replace(Replace([TheField],Chr(9),""),Chr(10),""),Chr(13)"") As [StrippedField], ...

From ...

etc.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]