includes space with data

  • Thread starter Thread starter mtress
  • Start date Start date
M

mtress

I'm importing from Excel. One field (type: text) has a
space in front of data, imports the same way, and causes
subsequent problems when I link table with others. Data
in Excel spreadsheet is pasted from a custom program,
leaving no way to correct problem in Excel (e.g. left
justify column). Can I fix this problem somehow on
imported table (removing space) or can I fix it on linked
tables by somehow adding a space to the data in linked
field? Thanks for all your help.
 
Hi,

I assume you mean that the data in the field is prefixed by a space
character, e.g.
" Jones"
instead of
"Jones"

To permanently get rid of the space, use an update query to update this
field to
Mid([XXX],2)
where XXX is the field name. To ensure that this only affects values
that begin with a space, add a calculated field to the query
FirstChar :Left([XXX],1)
and set a criterion on it of
" "
..
 
John Nurick said:
To permanently get rid of the space, use an update query to update this
field to
Mid([XXX],2)
where XXX is the field name. To ensure that this only affects values
that begin with a space, add a calculated field to the query
FirstChar :Left([XXX],1)
and set a criterion on it of
" "
Click to expand...

Could also use the LTRIM (or TRIM) function.

Jamie.

--
 
Back
Top