?
Solved

Storing trailing spaces in a text field in MS Access 2000

Posted on 2003-03-11
8
Medium Priority
?
208 Views
Last Modified: 2008-02-01
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
Comment
Question by:ibrahimhakim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8112536
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
 
LVL 18

Expert Comment

by:1William
ID: 8113114
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
 

Author Comment

by:ibrahimhakim
ID: 8121294
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 200 total points
ID: 8124335
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
 

Author Comment

by:ibrahimhakim
ID: 8126292
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 8785524
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
 
LVL 18

Expert Comment

by:1William
ID: 8888786
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question