Leading Spaces

[Guest]

I downloaded some data and imported it into Access 2003. Some of the fields
contain a leading space. How do I eliminate that one space? I can not use
the Left or Right function since the following characters all have different
lengths.

 

[tina]

try running an Update query, using the Trim() function on each field that
has extra spaces.

suggest you back up your db first, just to be safe.

hth

 

[Guest]

Tina,

Thank you for the reply. I tried using Trim() function, and it did not
remove that one leading space. Here is my query:

Trim([Tab_DirectOrdersWithPartNumbers]![Product])

Thanks again.

 

[tina]

please post the entire SQL statement.

Tina,

Thank you for the reply. I tried using Trim() function, and it did not
remove that one leading space. Here is my query:

Trim([Tab_DirectOrdersWithPartNumbers]![Product])

Thanks again.

try running an Update query, using the Trim() function on each field that
has extra spaces.

suggest you back up your db first, just to be safe.

hth


message news:[email protected]... not
use

 

[Guest]

Here's the SQL statement:

UPDATE Tab_DirectOrdersWithPartNumbers SET
Tab_DirectOrdersWithPartNumbers.ProductNew = Trim([product]);

please post the entire SQL statement.

Tina,

Thank you for the reply. I tried using Trim() function, and it did not
remove that one leading space. Here is my query:

Trim([Tab_DirectOrdersWithPartNumbers]![Product])

Thanks again.

try running an Update query, using the Trim() function on each field that
has extra spaces.

suggest you back up your db first, just to be safe.

hth


message I downloaded some data and imported it into Access 2003. Some of the
fields
contain a leading space. How do I eliminate that one space? I can not
use
the Left or Right function since the following characters all have
different
lengths.

 

[John Vinson]

Tina,

Thank you for the reply. I tried using Trim() function, and it did not
remove that one leading space. Here is my query:

Trim([Tab_DirectOrdersWithPartNumbers]![Product])

That's not a query - that's a function call.

Try creating an Update query based on your table. Select the Product
field; on the Update To line of the query grid under Product, put

Trim([Product])

Run the query by clicking the ! icon. Your spaces should now be gone.


John W. Vinson[MVP]

 

[fredg]

I downloaded some data and imported it into Access 2003. Some of the fields
contain a leading space. How do I eliminate that one space? I can not use
the Left or Right function since the following characters all have different
lengths.

Actually you could have used Left() and Mid(), i.e.
=IIf(Asc(Left([FieldName],1)) = 32,Mid([FieldName],2),[fieldName])

but you don't need to, as Asc() reads the first character of a string,
unless you point it elsewhere.

Update YourTable Set YourTable.FieldName =
IIf(Asc([FieldName])=32,Mid([FieldName],2),[FieldName]);

Or you should be able to use:
.......Set YourTable.[FieldName] = LTrim([FieldName])

 

[tina]

hmm, well, as fred suggested, you could try using LTrim() rather than
Trim(), though both should removing leading spaces. if neither works, try

Right([product], Len([product])-1)

btw, the [product] field is a Text data type, correct?

hth

Here's the SQL statement:

UPDATE Tab_DirectOrdersWithPartNumbers SET
Tab_DirectOrdersWithPartNumbers.ProductNew = Trim([product]);

please post the entire SQL statement.

Tina,

Thank you for the reply. I tried using Trim() function, and it did not
remove that one leading space. Here is my query:

Trim([Tab_DirectOrdersWithPartNumbers]![Product])

Thanks again.

:

try running an Update query, using the Trim() function on each field that
has extra spaces.

suggest you back up your db first, just to be safe.

hth


"Don't Need Spaces" <Don't Need (e-mail address removed)>

wrote
in

message I downloaded some data and imported it into Access 2003. Some of the
fields
contain a leading space. How do I eliminate that one space? I

can
not

use
the Left or Right function since the following characters all have
different
lengths.