Trim Function

[Guest]

I just posted regarding sort order of fields and realized that my problem is
that when I specify a text field length of 12 for my question number (it may
be up to that length, but may also be less than that length), it adds leading
spaces when I input it on my form.

This is why it isn't sorting properly.

Should I use a Trim function to get rid of the spaces? If so, what is the
syntax.

 

[Guest]

The easiest way to do your sort is in design view place the field for first
order of sort to the left of the second order field. In the Sort row of the
design view grid select Ascending.

 

[John W. Vinson]

I just posted regarding sort order of fields and realized that my problem is
that when I specify a text field length of 12 for my question number (it may
be up to that length, but may also be less than that length), it adds leading
spaces when I input it on my form.

Access doesn't do this by itself unless you're specifically including code to
do so or typing in the blanks. Are you sure of that? Could you post an example
of the data, the SQL of your query, and how it's sorting?

John W. Vinson [MVP]

 

[Jamie Collins]

Access doesn't do this by itself unless you're specifically including code to
do so or typing in the blanks.

FWIW Access (the engine) sometimes adds *trailing* spaces e.g. using
Northwind

SELECT DISTINCT
CompanyName = (SPACE(5) & CompanyName),
CompanyName = (CompanyName & SPACE(5))
FROM Customers;

will return columns FALSE and TRUE respectively.

CREATE TABLE Test (col1 NVARCHAR(5), col2 NCHAR(5))
;
INSERT INTO Test (col1, col2) VALUES ('ABC', 'ABC')
;
SELECT LEN(col1), LEN(col2)
FROM Test;

returns 3 and 5 respectively.

Jamie.

--