• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Storing trailing spaces in a text field in MS Access 2000

Hi,
I am using Access 2000 database. I want to store trailing spaces in a text field but Access isnt letting me do that. It truncates any leading spaces in the text field.
Actually I am breaking a string and storing it in to 4 text fields. The source string has spaces in it. It is possible that one or more of the chunks of source string can contain trailing spaces. I need those spaces to be stored also in the database fields.

I will really appritiate if any one can help me in this regards

Thanks.
0
ibrahimhakim
Asked:
ibrahimhakim
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Arthur_WoodCommented:
Access DOES NOT allow storing trailing blanks in ANY text field, and that cannot be changed.

if you need the blanks in order to 'reconstruct' the original text from the four parts, then you can always include the " " in the reconstruction:

Select [Field1] & " " & [Field2] & " " & [Field3] & " " & [Field4] as OrginalField from table

AW

0
 
1WilliamCommented:
Another Possibly Helpful Hint:
If you need to have those trailing spaces (and I assume the number of spaces can vary for each record), prior to splitting your data into the individual fields, you might want to find out how many spaces there are.  Store this number in a new column(or columns).  That way, when you have to re-construct the data, you'll know how many spaces to put in!
0
 
ibrahimhakimAuthor Commented:
Thanks for the promt reply.
Wood,
I thought the same first but recognized that it isnt feasible cause incase if a chunk does not break exactly at a space but in the middle of a word, joining the four strings back will put a space in the word.

Since Access doesnt itself support the leading spaces, I guess I will have to do the thing differently.


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Arthur_WoodCommented:
alternatively, you might want to investigate a 'parsing' algorithm that does NOT break in the MIDDLE of words, but rather at the next EARLIER natural space between words.

Another thought---make your text field a MEMO field, which can hold up to 65000 characters.  The only limitation is that a MEMO field cannot be part of an INDEX, but that is rarely an issue.  Rahter than Datatype Text, make it Datatype MEMO.

AW
0
 
ibrahimhakimAuthor Commented:
I tried with MEMO type but this doesnt support trailing spaces either.

First option looks good. The only thing is that it will slow down the process as there may be thousands of records to be traversed. Anyways, I will give it a try.


0
 
nexusnationCommented:
Hi ibrahimhakim,
This question has been abandoned and needs to be finalized (102 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
1WilliamCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to Arthur_Wood
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now