A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Removing extra spacing in imported text



 
 
Thread Tools Display Modes
  #1  
Old November 27th, 2006, 02:47 PM posted to microsoft.public.access.queries
Kat
external usenet poster
 
Posts: 268
Default Removing extra spacing in imported text

I have (2) tables that were imported into access from excel. However, the
data has a combination of letters, numbers, and dashes in it. That will
make the data type as Text. Included in the data are spaces before and after
the text. I want to remove the spaces so that I can create a query to match
two same fields from diffrent tables. In a query I built a field that should
have removed the spaces but it did not. This is what I did: ItemNew:
Trim$([26774]![Item]). But it did not remove the spaces. Until I remove the
spaces I can not match the data from both tables. What am I missing?
  #2  
Old November 27th, 2006, 03:46 PM posted to microsoft.public.access.queries
Roger Carlson
external usenet poster
 
Posts: 222
Default Removing extra spacing in imported text

You need to make an Update Query. In the Query Builder, make a Select query
with the one field ([Item]). Then change the query to an Update Query
(there's a button on the tool bar), and put the expression you used below in
the UpdateTo row of the field. Then run the query with the Run button.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Kat" wrote in message
...
I have (2) tables that were imported into access from excel. However, the
data has a combination of letters, numbers, and dashes in it. That will
make the data type as Text. Included in the data are spaces before and

after
the text. I want to remove the spaces so that I can create a query to

match
two same fields from diffrent tables. In a query I built a field that

should
have removed the spaces but it did not. This is what I did: ItemNew:
Trim$([26774]![Item]). But it did not remove the spaces. Until I remove

the
spaces I can not match the data from both tables. What am I missing?



  #3  
Old November 27th, 2006, 03:54 PM posted to microsoft.public.access.queries
kingston via AccessMonster.com
external usenet poster
 
Posts: 620
Default Removing extra spacing in imported text

How are you using the function Trim()? Are you using it in an update query
or a select query? In an update query, the preview will display the original
data, not the results. You'll have to run the update query to change the
data. If you're using it in a select query, the characters might not be
spaces; try the functions Clean() or Asc() to determine what you've got.
Also, if all records always have a space before and after the data, you can
use the functions Mid() and Len() to extract the part that you want:
Mid([Field],2,Len([Field])-2)

Kat wrote:
I have (2) tables that were imported into access from excel. However, the
data has a combination of letters, numbers, and dashes in it. That will
make the data type as Text. Included in the data are spaces before and after
the text. I want to remove the spaces so that I can create a query to match
two same fields from diffrent tables. In a query I built a field that should
have removed the spaces but it did not. This is what I did: ItemNew:
Trim$([26774]![Item]). But it did not remove the spaces. Until I remove the
spaces I can not match the data from both tables. What am I missing?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200611/1

  #4  
Old November 27th, 2006, 04:48 PM posted to microsoft.public.access.queries
Kat
external usenet poster
 
Posts: 268
Default Removing extra spacing in imported text

Roger,

I was woundering if I needed to do an update query. I tried to do it with a
make table query but it did not work. I created the update query and put
Trim$([26774]!Item) in the update to field but it still is not working. When
I run the query and go back to the table the spacing is still there. What am
I doing wrong?

"Roger Carlson" wrote:

You need to make an Update Query. In the Query Builder, make a Select query
with the one field ([Item]). Then change the query to an Update Query
(there's a button on the tool bar), and put the expression you used below in
the UpdateTo row of the field. Then run the query with the Run button.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Kat" wrote in message
...
I have (2) tables that were imported into access from excel. However, the
data has a combination of letters, numbers, and dashes in it. That will
make the data type as Text. Included in the data are spaces before and

after
the text. I want to remove the spaces so that I can create a query to

match
two same fields from diffrent tables. In a query I built a field that

should
have removed the spaces but it did not. This is what I did: ItemNew:
Trim$([26774]![Item]). But it did not remove the spaces. Until I remove

the
spaces I can not match the data from both tables. What am I missing?




  #5  
Old November 27th, 2006, 04:50 PM posted to microsoft.public.access.queries
Kat
external usenet poster
 
Posts: 268
Default Removing extra spacing in imported text

Kingston,

I have never used any of these functions. What are they? All I want to do
is remove the spaces or text from a field. What is Clean()?

"kingston via AccessMonster.com" wrote:

How are you using the function Trim()? Are you using it in an update query
or a select query? In an update query, the preview will display the original
data, not the results. You'll have to run the update query to change the
data. If you're using it in a select query, the characters might not be
spaces; try the functions Clean() or Asc() to determine what you've got.
Also, if all records always have a space before and after the data, you can
use the functions Mid() and Len() to extract the part that you want:
Mid([Field],2,Len([Field])-2)

Kat wrote:
I have (2) tables that were imported into access from excel. However, the
data has a combination of letters, numbers, and dashes in it. That will
make the data type as Text. Included in the data are spaces before and after
the text. I want to remove the spaces so that I can create a query to match
two same fields from diffrent tables. In a query I built a field that should
have removed the spaces but it did not. This is what I did: ItemNew:
Trim$([26774]![Item]). But it did not remove the spaces. Until I remove the
spaces I can not match the data from both tables. What am I missing?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200611/1


  #6  
Old November 27th, 2006, 06:06 PM posted to microsoft.public.access.queries
Roger Carlson
external usenet poster
 
Posts: 222
Default Removing extra spacing in imported text

How are you "running" the query? With the Run button or with the View
button? In select queries, it makes no difference, but with action queries,
you HAVE to use the Run button. Also, try Trim() instead of Trim$().

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Kat" wrote in message
...
Roger,

I was woundering if I needed to do an update query. I tried to do it with

a
make table query but it did not work. I created the update query and put
Trim$([26774]!Item) in the update to field but it still is not working.

When
I run the query and go back to the table the spacing is still there. What

am
I doing wrong?

"Roger Carlson" wrote:

You need to make an Update Query. In the Query Builder, make a Select

query
with the one field ([Item]). Then change the query to an Update Query
(there's a button on the tool bar), and put the expression you used

below in
the UpdateTo row of the field. Then run the query with the Run button.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Kat" wrote in message
...
I have (2) tables that were imported into access from excel. However,

the
data has a combination of letters, numbers, and dashes in it. That

will
make the data type as Text. Included in the data are spaces before

and
after
the text. I want to remove the spaces so that I can create a query to

match
two same fields from diffrent tables. In a query I built a field that

should
have removed the spaces but it did not. This is what I did: ItemNew:
Trim$([26774]![Item]). But it did not remove the spaces. Until I

remove
the
spaces I can not match the data from both tables. What am I missing?






  #7  
Old November 27th, 2006, 07:06 PM posted to microsoft.public.access.queries
Kat
external usenet poster
 
Posts: 268
Default Removing extra spacing in imported text

I give up. I have tried both ways trim and trim$. There must be something
to do with where the inital report is coming from. It came from Micro
stratgy8. I have never used it. Then I exported it to excel. Then imported
it to Access. Thanks for your help!

"Roger Carlson" wrote:

How are you "running" the query? With the Run button or with the View
button? In select queries, it makes no difference, but with action queries,
you HAVE to use the Run button. Also, try Trim() instead of Trim$().

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Kat" wrote in message
...
Roger,

I was woundering if I needed to do an update query. I tried to do it with

a
make table query but it did not work. I created the update query and put
Trim$([26774]!Item) in the update to field but it still is not working.

When
I run the query and go back to the table the spacing is still there. What

am
I doing wrong?

"Roger Carlson" wrote:

You need to make an Update Query. In the Query Builder, make a Select

query
with the one field ([Item]). Then change the query to an Update Query
(there's a button on the tool bar), and put the expression you used

below in
the UpdateTo row of the field. Then run the query with the Run button.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Kat" wrote in message
...
I have (2) tables that were imported into access from excel. However,

the
data has a combination of letters, numbers, and dashes in it. That

will
make the data type as Text. Included in the data are spaces before

and
after
the text. I want to remove the spaces so that I can create a query to
match
two same fields from diffrent tables. In a query I built a field that
should
have removed the spaces but it did not. This is what I did: ItemNew:
Trim$([26774]![Item]). But it did not remove the spaces. Until I

remove
the
spaces I can not match the data from both tables. What am I missing?






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.