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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|