TRIM function

[mtress]

I'm kinda new at SQL....
I want to get rid of a space before every entry in a
field.

SELECT Items.Item
FROM Items;

Where do I put the TRIM function in the above? Also, if
the TRIM function is formatted as TRIM(text), what do I
put for the text part if I want to fix every entry in
that field? Thanks for your help.

 

[Jeff Boyce]

You didn't indicate the back-end data source. Where the spaces are
(before/after meaningful text) depends -- different back-ends (Access/Jet,
SQL-Server, text file, Excel, dBase, ...) handle spaces differently.

The Trim() function in Access removes spaces before and after. There's also
a LTrim() and RTrim() that remove the spaces from the ... (you get it,
right?). The generic Trim() function is NOT available if you are working
entirely within SQL-Server.

To "trim" a field in a query, start in a new field. Add a title for the
field (e.g., TrimmedLastName) and a colon and a space, followed by the trim
function you'll be using and the field to be trimmed -- it will look
something like:

TrimmedLastName: Trim([LastName])

Putting the field name in square brackets tells Access you want that field's
contents acted on, not the literal string.

 

[mtress]

I get data from Excel. I'm working in Access 2000.
You said:

To "trim" a field in a query, start in a new field.

A new field where? In a query? table?

Add a title for the

field (e.g., TrimmedLastName) and a colon and a space, followed by the trim
function you'll be using and the field to be trimmed -- it will look
something like:

TrimmedLastName: Trim([LastName])

Putting the field name in square brackets tells Access you want that field's
contents acted on, not the literal string.

--
Good luck

Jeff Boyce
<Access MVP>

.

 

[Jeff Boyce]

Trim a field by creating a new query. In query design mode, add the
information (title: fnc([field])) in one of the empty "cells" called
"Field".