Space Error

jamesfranklin

Registered User.
Local time
Yesterday, 17:26
Joined
Sep 17, 2012
Messages
26
Hi,
I am having table with some records. In that i want remove Front space and Back space

Ex
##Faculty Of Science
Faculty Of Science##

Please refer the attachment Ex In that table (Facult column-Press F2)

Any help.......Please


Thank u for spending time in this post.....
 

Attachments

use Trim() function to remove spaces from any field entry in the database.
check the attached file with trimmed query.

Trim([FieldName])

in this case:
TrimmedOrg: Trim([org_Name])

cant upload .rar file. this form support only zip file to be uploaded.
anyhow create a blank query and drag and drop the specified table to QBE of the query.
create a column name TrimmedOrg: in QBE and type TRIM([YourFieldName])
run it.
 
I looked at your database and also thought that Trim would be the answer.
However, I found that you have ASC(13) & ASC(10) in positions 1 and 2 of Facult. These characters represent a CrLf. You could update your table by replacing Facult with Mid(Facult,3).

This query will show you the contents I'm describing.
Code:
SELECT Table.ID
, Table.org_Name
, Asc(Left([facult],1)) & Asc(Mid([facult],2,1)) AS Expr1
, Table.department1
, Mid([facult],3) AS Expr2
FROM [Table];

Bottom line -- they are NOT SPACES.
Good luck.
 
Try...

Code:
SELECT ID, org_Name, Replace(Nz([table].[facult],""),Chr(13) & Chr(10),"") AS facult, department1
FROM [Table];
... or better yet, run this against your data to fix it in the table ...
Code:
UPDATE [Table] SET facult = Replace([facult], Chr(13) & Chr(10), "")
WHERE facult Is Not Null;
Cheers,
 
Re: Try...

lagbolt gave you perfect solution.
In my post it will remove spaces from org_Name not from facult field as I call the trim function on org_Name field in a query.
 
Re: Try...

lagbolt gave you good answer, in my post I use trim function on org_Name field not on facult field in a query... my miss understanding
 
not sure if you can do this with pattern matching - but maybe you need to examine the strings a char at a time, and remove anything with a <chr(32)

the question is - how do these rogue characters get into your data? maybe the solution is to fix the data source.
 
Those aren't spaces, those are carriage return and line feeds (Chr(13) & Chr(10))

You can use an update query to remove them.

UPDATE
SET
.facult = Replace([facult],Chr(13) & Chr(10),"");


Now, when you run this, you will get an error that was unable to update all of the records in the update query but still say YES to it.
 

Users who are viewing this thread

Back
Top Bottom