How to trim leading spaces that are not removed by Trim

D

Davo78

Hi All
I am trying to remove the leading space in 14 fields of my "CustomersImport"
table, I am using an update query with the following SQL:

UPDATE CustomersImport SET CustomersImport.F3 = LTrim([F3]),
CustomersImport.F4 = LTrim([F4]), CustomersImport.F5 = LTrim([F5]),
CustomersImport.F6 = LTrim([F6]), CustomersImport.F7 = LTrim([F7]),
CustomersImport.F8 = LTrim([F8]), CustomersImport.F9 = LTrim([F9]),
CustomersImport.F10 = LTrim([F10]), CustomersImport.F11 = LTrim([F11]),
CustomersImport.F12 = LTrim([F12]), CustomersImport.F13 = LTrim([F13]),
CustomersImport.F14 = LTrim([F14]);

The result in "CustomersImport" table is that it does not remove the
space,
if I highlight a record in a field then manually delete the space then
re-insert the space with the spacebar, then I run the update query it
removes
that space and no others.

Please refer to previous thread "Trim in Query" 1/11/2008.

Thanks in advance
Davo
 
M

Marshall Barton

Davo78 said:
I am trying to remove the leading space in 14 fields of my "CustomersImport"
table, I am using an update query with the following SQL:

UPDATE CustomersImport SET CustomersImport.F3 = LTrim([F3]),
CustomersImport.F4 = LTrim([F4]), CustomersImport.F5 = LTrim([F5]),
CustomersImport.F6 = LTrim([F6]), CustomersImport.F7 = LTrim([F7]),
CustomersImport.F8 = LTrim([F8]), CustomersImport.F9 = LTrim([F9]),
CustomersImport.F10 = LTrim([F10]), CustomersImport.F11 = LTrim([F11]),
CustomersImport.F12 = LTrim([F12]), CustomersImport.F13 = LTrim([F13]),
CustomersImport.F14 = LTrim([F14]);

The result in "CustomersImport" table is that it does not remove the
space,
if I highlight a record in a field then manually delete the space then
re-insert the space with the spacebar, then I run the update query it
removes that space and no others.
Click to expand...


The place where the data originated (probably Word) used a
non-space blank character. You can determine the character
code by using
Asc(F3)
in a calculated field in a simple select query for any
record that needs to be trimmed.

Whatever character code that is, you can then run an update
query to convert them to normal spaces:

UPDATE CustomersImport
SET F3 = Replace(F3, Chr(thecode), " "),
F4 = Replace(F4, Chr(thecode), " ")
. . .

Then you can run your Trim query.
 
G

GeoffK

Marshall

I am new to using queries and have the same problem as Davo.

Could you please explain the steps in more detail. In particular the first
prt Asc(F3)
in a calculated field
Click to expand...

My original table ProductionItems is imported from Excel and the fields are
named F1 to F6. I have tried duplicating your suggestion but cannot get it to
work.

With Thanks
GeoffK

Marshall Barton said:
Davo78 said:
I am trying to remove the leading space in 14 fields of my "CustomersImport"
table, I am using an update query with the following SQL:

UPDATE CustomersImport SET CustomersImport.F3 = LTrim([F3]),
CustomersImport.F4 = LTrim([F4]), CustomersImport.F5 = LTrim([F5]),
CustomersImport.F6 = LTrim([F6]), CustomersImport.F7 = LTrim([F7]),
CustomersImport.F8 = LTrim([F8]), CustomersImport.F9 = LTrim([F9]),
CustomersImport.F10 = LTrim([F10]), CustomersImport.F11 = LTrim([F11]),
CustomersImport.F12 = LTrim([F12]), CustomersImport.F13 = LTrim([F13]),
CustomersImport.F14 = LTrim([F14]);

The result in "CustomersImport" table is that it does not remove the
space,
if I highlight a record in a field then manually delete the space then
re-insert the space with the spacebar, then I run the update query it
removes that space and no others.
Click to expand...


The place where the data originated (probably Word) used a
non-space blank character. You can determine the character
code by using
Asc(F3)
in a calculated field in a simple select query for any
record that needs to be trimmed.

Whatever character code that is, you can then run an update
query to convert them to normal spaces:

UPDATE CustomersImport
SET F3 = Replace(F3, Chr(thecode), " "),
F4 = Replace(F4, Chr(thecode), " ")
. . .

Then you can run your Trim query.
Click to expand...
 
M

Marshall Barton

Just create a new query that is only used to determine the
non-blank space character. This is done by adding your
imported table and adding a field with the funny character
as its first character to the field list. Then in the next
field, enter Asc([the same field]) and set its criteria to
<>32

When you run the query, you should see the strings in the
first column and the ascii code for the first character in
the second column. Scroll through the records looking for a
field that starts with a space and note the value in the
second field.

Then try running the Update query using the value from the
above in place the "thecode" in the Replace function.
--
Marsh
MVP [MS Access]

I am new to using queries and have the same problem as Davo.

Could you please explain the steps in more detail. In particular the first
prt Asc(F3)
in a calculated field
Click to expand...

My original table ProductionItems is imported from Excel and the fields are
named F1 to F6. I have tried duplicating your suggestion but cannot get it to
work.


Marshall Barton said:
Davo78 said:
I am trying to remove the leading space in 14 fields of my "CustomersImport"
table, I am using an update query with the following SQL:

UPDATE CustomersImport SET CustomersImport.F3 = LTrim([F3]),
CustomersImport.F4 = LTrim([F4]), CustomersImport.F5 = LTrim([F5]),
CustomersImport.F6 = LTrim([F6]), CustomersImport.F7 = LTrim([F7]),
CustomersImport.F8 = LTrim([F8]), CustomersImport.F9 = LTrim([F9]),
CustomersImport.F10 = LTrim([F10]), CustomersImport.F11 = LTrim([F11]),
CustomersImport.F12 = LTrim([F12]), CustomersImport.F13 = LTrim([F13]),
CustomersImport.F14 = LTrim([F14]);

The result in "CustomersImport" table is that it does not remove the
space,
if I highlight a record in a field then manually delete the space then
re-insert the space with the spacebar, then I run the update query it
removes that space and no others.
Click to expand...


The place where the data originated (probably Word) used a
non-space blank character. You can determine the character
code by using
Asc(F3)
in a calculated field in a simple select query for any
record that needs to be trimmed.

Whatever character code that is, you can then run an update
query to convert them to normal spaces:

UPDATE CustomersImport
SET F3 = Replace(F3, Chr(thecode), " "),
F4 = Replace(F4, Chr(thecode), " ")
. . .

Then you can run your Trim query.
Click to expand...
Click to expand...
 
Top