Re: How to trim leading spaces that are not removed by Trim

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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]


GeoffK wrote:
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

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" wrote:

Davo78 wrote:
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.


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

Relevant Pages

  • Re: SQL*PLUS select statment
    ... single column; Here is an example: ... Another bad habit is to store dates as character strings (rely as well ... with the query and modify it to the way I need it and I could not get ... dynamic data back with that said how could I turn this into a dynamic ...
    (comp.databases.oracle.misc)
  • Re: Newbie performance/design question
    ... > character. ... just need to speed up this particular query, ... Pro SQL Server 2000 Database Design - ... > My first thought is to create a new field composed of the above,> concatenated together and separated by perhaps a '|' vertical bar ...
    (microsoft.public.sqlserver.programming)
  • Re: remove alpha content in text field
    ... >need to check each character. ... >Function ValNumber(strString As String) As Integer ... >Dim intX As Integer ... >You can call it from a query: ...
    (microsoft.public.access.queries)
  • Re: How to Remove
    in Fields
    ... couldn't run a query to update a field to remove a specific character ... there via ASP or .Net or something like that, ... >for blank records when doing html imports. ... >too randomized to run a delete query against. ...
    (comp.databases.ms-access)
  • Access 2000 Query using "Between Operator"
    ... I am developing a Query that requires a "Between Operator" ... The problem is associated with the 3 and 4 character ... IF the 3 and 4 character data were excluded ... have tried enclosing the Prompt field with a Val ...
    (microsoft.public.access.queries)