trim in query

[Davo78]

Hi All

I am trying to trim a leading space at the start of each field in my
"Customers" table which is an Exel spreadsheet imported using
"TransferSpreadsheet"

After researching the internet I have been able to get this to work using,
for example: CustomerName: Trim([F3]) in the Field line of a select query
("CustomersTrimQry").

My promlem is that when I append the data from "CustomersTrimQry" to the
"Customers" table the space returns.

Prior to using trim I ran 3 queries from the original "CustomersImport"
table, "CustomersdeleteQry" (removes null records), "CustomersAppendQry"
(appends to 'Customers" table) and "CustomersImportClearQry" (deletes all
records in "CustomersImport" table.

When using trim I still use the 3 queries but the "CustomersAppendQry" is
based on the "CustomersTrimQry".

Can anyone explain why I get the leading space when I append the data to
"Customers" table or advise me of another approach to the objective.

Thanks in advance
Davo

 

[Jeanette Cunningham]

Davo,
my first thought is that you accidentally appended the F3 data in its
untrimmed state. This can happen easily when you are learning how to do
these trim, delete and append queries. Make sure you back up (make a copy
of) your main data table before you do any action queries on it (append,
delete, update).

Jeanette Cunningham

 

[Davo78]

Jeanette

Thank you for your prompt reply.

I have double checked that the data in "CustomersTrimQry" has trimmed the
leading space.

There are a total of 14 fields that require the leading space trimmed.

All data is backed up on a regular basis.

Thanks again
Davo

Davo,
my first thought is that you accidentally appended the F3 data in its
untrimmed state. This can happen easily when you are learning how to do
these trim, delete and append queries. Make sure you back up (make a copy
of) your main data table before you do any action queries on it (append,
delete, update).

Jeanette Cunningham

Hi All

I am trying to trim a leading space at the start of each field in my
"Customers" table which is an Exel spreadsheet imported using
"TransferSpreadsheet"

After researching the internet I have been able to get this to work using,
for example: CustomerName: Trim([F3]) in the Field line of a select
query
("CustomersTrimQry").

My promlem is that when I append the data from "CustomersTrimQry" to the
"Customers" table the space returns.

Prior to using trim I ran 3 queries from the original "CustomersImport"
table, "CustomersdeleteQry" (removes null records), "CustomersAppendQry"
(appends to 'Customers" table) and "CustomersImportClearQry" (deletes all
records in "CustomersImport" table.

When using trim I still use the 3 queries but the "CustomersAppendQry" is
based on the "CustomersTrimQry".

Can anyone explain why I get the leading space when I append the data to
"Customers" table or advise me of another approach to the objective.

Thanks in advance
Davo

 

[Jeanette Cunningham]

Davo,
glad to hear that you backup often.
I am thinking that you have 14 fields in the Customer table that still get a
leading space after you append the data from CustomersTrimQry - is this
correct?
Just runnning through the steps to be clear about the process.
Data is imported into CustomersImport table.
Delete all null records from CustomersImport table.
Trim the leading space from each field in CustomersImport table.
After you have trimmed the leading space, open CustomersImport table in
datasheet view and check that all the leading spaces have been removed.
Append the data from CustomersImport table to the Customer table.

The above would normally put trimmed data into Customer table.
If you are following all the above steps and still getting data with leading
spaces, please post back with the SQL view of your trim and append queries.

Jeanette Cunningham

Jeanette

Thank you for your prompt reply.

I have double checked that the data in "CustomersTrimQry" has trimmed the
leading space.

There are a total of 14 fields that require the leading space trimmed.

All data is backed up on a regular basis.

Thanks again
Davo

Davo,
my first thought is that you accidentally appended the F3 data in its
untrimmed state. This can happen easily when you are learning how to do
these trim, delete and append queries. Make sure you back up (make a copy
of) your main data table before you do any action queries on it (append,
delete, update).

Jeanette Cunningham

Hi All

I am trying to trim a leading space at the start of each field in my
"Customers" table which is an Exel spreadsheet imported using
"TransferSpreadsheet"

After researching the internet I have been able to get this to work
using,
for example: CustomerName: Trim([F3]) in the Field line of a select
query
("CustomersTrimQry").

My promlem is that when I append the data from "CustomersTrimQry" to
the
"Customers" table the space returns.

Prior to using trim I ran 3 queries from the original "CustomersImport"
table, "CustomersdeleteQry" (removes null records),
"CustomersAppendQry"
(appends to 'Customers" table) and "CustomersImportClearQry" (deletes
all
records in "CustomersImport" table.

When using trim I still use the 3 queries but the "CustomersAppendQry"
is
based on the "CustomersTrimQry".

Can anyone explain why I get the leading space when I append the data
to
"Customers" table or advise me of another approach to the objective.

Thanks in advance
Davo

 

[Davo78]

Jeanette

I have been investigating this problem further and found that I need to use
an update query.

The SQL is as follows:
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.

Davo

Davo,
glad to hear that you backup often.
I am thinking that you have 14 fields in the Customer table that still get a
leading space after you append the data from CustomersTrimQry - is this
correct?
Just runnning through the steps to be clear about the process.
Data is imported into CustomersImport table.
Delete all null records from CustomersImport table.
Trim the leading space from each field in CustomersImport table.
After you have trimmed the leading space, open CustomersImport table in
datasheet view and check that all the leading spaces have been removed.
Append the data from CustomersImport table to the Customer table.

The above would normally put trimmed data into Customer table.
If you are following all the above steps and still getting data with leading
spaces, please post back with the SQL view of your trim and append queries.

Jeanette Cunningham

Jeanette

Thank you for your prompt reply.

I have double checked that the data in "CustomersTrimQry" has trimmed the
leading space.

There are a total of 14 fields that require the leading space trimmed.

All data is backed up on a regular basis.

Thanks again
Davo

Davo,
my first thought is that you accidentally appended the F3 data in its
untrimmed state. This can happen easily when you are learning how to do
these trim, delete and append queries. Make sure you back up (make a copy
of) your main data table before you do any action queries on it (append,
delete, update).

Jeanette Cunningham


Hi All

I am trying to trim a leading space at the start of each field in my
"Customers" table which is an Exel spreadsheet imported using
"TransferSpreadsheet"

After researching the internet I have been able to get this to work
using,
for example: CustomerName: Trim([F3]) in the Field line of a select
query
("CustomersTrimQry").

My promlem is that when I append the data from "CustomersTrimQry" to
the
"Customers" table the space returns.

Prior to using trim I ran 3 queries from the original "CustomersImport"
table, "CustomersdeleteQry" (removes null records),
"CustomersAppendQry"
(appends to 'Customers" table) and "CustomersImportClearQry" (deletes
all
records in "CustomersImport" table.

When using trim I still use the 3 queries but the "CustomersAppendQry"
is
based on the "CustomersTrimQry".

Can anyone explain why I get the leading space when I append the data
to
"Customers" table or advise me of another approach to the objective.

Thanks in advance
Davo

 

[Jeanette Cunningham]

Davo,
the trim function trims off the common space character which is Ascii 32
The leading spaces in your fields are most likely not Ascii 32, but some
other character which creates a space.
Could you repost this as a new question asking
How to trim leading spaces that are not removed by Trim
Someone else can help.

Jeanette Cunningham

Jeanette

I have been investigating this problem further and found that I need to
use
an update query.

The SQL is as follows:
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.

Davo

Davo,
glad to hear that you backup often.
I am thinking that you have 14 fields in the Customer table that still
get a
leading space after you append the data from CustomersTrimQry - is this
correct?
Just runnning through the steps to be clear about the process.
Data is imported into CustomersImport table.
Delete all null records from CustomersImport table.
Trim the leading space from each field in CustomersImport table.
After you have trimmed the leading space, open CustomersImport table in
datasheet view and check that all the leading spaces have been removed.
Append the data from CustomersImport table to the Customer table.

The above would normally put trimmed data into Customer table.
If you are following all the above steps and still getting data with
leading
spaces, please post back with the SQL view of your trim and append
queries.

Jeanette Cunningham

Jeanette

Thank you for your prompt reply.

I have double checked that the data in "CustomersTrimQry" has trimmed
the
leading space.

There are a total of 14 fields that require the leading space trimmed.

All data is backed up on a regular basis.

Thanks again
Davo


:

Davo,
my first thought is that you accidentally appended the F3 data in its
untrimmed state. This can happen easily when you are learning how to
do
these trim, delete and append queries. Make sure you back up (make a
copy
of) your main data table before you do any action queries on it
(append,
delete, update).

Jeanette Cunningham


Hi All

I am trying to trim a leading space at the start of each field in my
"Customers" table which is an Exel spreadsheet imported using
"TransferSpreadsheet"

After researching the internet I have been able to get this to work
using,
for example: CustomerName: Trim([F3]) in the Field line of a
select
query
("CustomersTrimQry").

My promlem is that when I append the data from "CustomersTrimQry" to
the
"Customers" table the space returns.

Prior to using trim I ran 3 queries from the original
"CustomersImport"
table, "CustomersdeleteQry" (removes null records),
"CustomersAppendQry"
(appends to 'Customers" table) and "CustomersImportClearQry"
(deletes
all
records in "CustomersImport" table.

When using trim I still use the 3 queries but the
"CustomersAppendQry"
is
based on the "CustomersTrimQry".

Can anyone explain why I get the leading space when I append the
data
to
"Customers" table or advise me of another approach to the objective.

Thanks in advance
Davo

 

[Davo78]

Jeanette

Thank you for your help

Davo

Davo,
the trim function trims off the common space character which is Ascii 32
The leading spaces in your fields are most likely not Ascii 32, but some
other character which creates a space.
Could you repost this as a new question asking
How to trim leading spaces that are not removed by Trim
Someone else can help.

Jeanette Cunningham

Jeanette

I have been investigating this problem further and found that I need to
use
an update query.

The SQL is as follows:
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.

Davo

Davo,
glad to hear that you backup often.
I am thinking that you have 14 fields in the Customer table that still
get a
leading space after you append the data from CustomersTrimQry - is this
correct?
Just runnning through the steps to be clear about the process.
Data is imported into CustomersImport table.
Delete all null records from CustomersImport table.
Trim the leading space from each field in CustomersImport table.
After you have trimmed the leading space, open CustomersImport table in
datasheet view and check that all the leading spaces have been removed.
Append the data from CustomersImport table to the Customer table.

The above would normally put trimmed data into Customer table.
If you are following all the above steps and still getting data with
leading
spaces, please post back with the SQL view of your trim and append
queries.

Jeanette Cunningham

Jeanette

Thank you for your prompt reply.

I have double checked that the data in "CustomersTrimQry" has trimmed
the
leading space.

There are a total of 14 fields that require the leading space trimmed.

All data is backed up on a regular basis.

Thanks again
Davo


:

Davo,
my first thought is that you accidentally appended the F3 data in its
untrimmed state. This can happen easily when you are learning how to
do
these trim, delete and append queries. Make sure you back up (make a
copy
of) your main data table before you do any action queries on it
(append,
delete, update).

Jeanette Cunningham


Hi All

I am trying to trim a leading space at the start of each field in my
"Customers" table which is an Exel spreadsheet imported using
"TransferSpreadsheet"

After researching the internet I have been able to get this to work
using,
for example: CustomerName: Trim([F3]) in the Field line of a
select
query
("CustomersTrimQry").

My promlem is that when I append the data from "CustomersTrimQry" to
the
"Customers" table the space returns.

Prior to using trim I ran 3 queries from the original
"CustomersImport"
table, "CustomersdeleteQry" (removes null records),
"CustomersAppendQry"
(appends to 'Customers" table) and "CustomersImportClearQry"
(deletes
all
records in "CustomersImport" table.

When using trim I still use the 3 queries but the
"CustomersAppendQry"
is
based on the "CustomersTrimQry".

Can anyone explain why I get the leading space when I append the
data
to
"Customers" table or advise me of another approach to the objective.

Thanks in advance
Davo