To give sql by removing empty space from the data

[pol]

Please let me know How I can give the sql by giving formated width

for example

The column width of the table is 100 but I want to display by triming the
blank space from left and right part while giving select statement

For exampl

Select Trim(comments) from test. But it is not working . Please help

with thank and regards

Pol

 

[Dale Fye]

By not working, what do you mean?

Do you want to update the table, to eliminate the leading spaces (there
shouldn't be any trailing spaces)? If this is what you want to do, the sql
would look like:

UPDATE yourTableName
SET [yourFieldName] = Trim([yourFieldName])

HTH
Dale

 

[pol]

Thanks for the reply

But I cannot give the condition for trimming the empty space while giving
the update . Becuase the data is updated by another front end application .
I cannot do anything on that application. So if there is any chance in
Access to trim the empty space along with SELECT statment please let me know
that would be very helpful to me.

With thanks

Pol

By not working, what do you mean?

Do you want to update the table, to eliminate the leading spaces (there
shouldn't be any trailing spaces)? If this is what you want to do, the sql
would look like:

UPDATE yourTableName
SET [yourFieldName] = Trim([yourFieldName])

HTH
Dale

Please let me know How I can give the sql by giving formated width

for example

The column width of the table is 100 but I want to display by triming the
blank space from left and right part while giving select statement

For exampl

Select Trim(comments) from test. But it is not working . Please help

with thank and regards

Pol

 

[John W. Vinson]

Thanks for the reply

But I cannot give the condition for trimming the empty space while giving
the update . Becuase the data is updated by another front end application .
I cannot do anything on that application. So if there is any chance in
Access to trim the empty space along with SELECT statment please let me know
that would be very helpful to me.

You can simply use a calculated field in the SELECT clause:

TrimmedText: Trim([fieldname])