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...
 
Top