Removing extra spacing in imported text

[Guest]

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?

 

[Roger Carlson]

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/wa.exe?SUBED1=ACCESS-L

 

[kingston via AccessMonster.com]

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)

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?

 

[Guest]

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?

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/wa.exe?SUBED1=ACCESS-L

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?

 

[Guest]

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()?

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)

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?

 

[Roger Carlson]

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/wa.exe?SUBED1=ACCESS-L

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?

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/wa.exe?SUBED1=ACCESS-L

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?

 

[Guest]

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!

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/wa.exe?SUBED1=ACCESS-L

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?

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/wa.exe?SUBED1=ACCESS-L


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?