RTrim & Sorting issue

[Mary]

I am using the RTrim function to remove trailing spaces for one of my fields.
This is a field I need to sort by, and ever since I added the RTRim function,
the sorting is including duplicate records, my guess is it includes one for
the field with a space and one without. Is there a way around this? In the
past, all records included a trailing space. Now my data source (Oracle) has
removed the trailing space. I need to include records from before and after
this change took place. I am using Access 97.

Thanks,
Mary

 

[John Spencer]

Did you change the report's Sorting and Grouping to sort by the trimmed
field?

You didn't say where you are using RTrim. In the underlying query for the
report? In some vba in the form?

In report design view
-- Select View: Sorting and Grouping
-- set sort by to =RTrim(TheField) instead of [The Field]

 

[Mary]

I've tried it in the underlying query, the field itself on the report, and in
the sorting and grouping area. When putting it in the sorting & grouping
section, no duplicates, but it separates the field into two groups, those
with spaces and those without. I need them to appear as one group. I'd prefer
to do it in the query, but =RTrim([Circuit_ID]) is not removing the trailing
spaces. I tried without the brackets when I saw your response, but Access 97
adds them.

Did you change the report's Sorting and Grouping to sort by the trimmed
field?

You didn't say where you are using RTrim. In the underlying query for the
report? In some vba in the form?

In report design view
-- Select View: Sorting and Grouping
-- set sort by to =RTrim(TheField) instead of [The Field]

I am using the RTrim function to remove trailing spaces for one of my
fields.
This is a field I need to sort by, and ever since I added the RTRim
function,
the sorting is including duplicate records, my guess is it includes one
for
the field with a space and one without. Is there a way around this? In
the
past, all records included a trailing space. Now my data source (Oracle)
has
removed the trailing space. I need to include records from before and
after
this change took place. I am using Access 97.

Thanks,
Mary

 

[John Spencer]

RTrim([Circuit_ID]) is not removing trailing spaces in the query, then the
spaces aren't spaces, but some other character.

Are you doing something like
Field: CircuitID: RTrim([Circuit_ID])
in the query? You should be

Then in the Sorting and Grouping of the report you need to sort by CircuitID
(the name of the newly calculated field)
Also, you will need to change the control source from Circuit_ID to
CircuitID

I've tried it in the underlying query, the field itself on the report, and
in
the sorting and grouping area. When putting it in the sorting & grouping
section, no duplicates, but it separates the field into two groups, those
with spaces and those without. I need them to appear as one group. I'd
prefer
to do it in the query, but =RTrim([Circuit_ID]) is not removing the
trailing
spaces. I tried without the brackets when I saw your response, but Access
97
adds them.

Did you change the report's Sorting and Grouping to sort by the trimmed
field?

You didn't say where you are using RTrim. In the underlying query for
the
report? In some vba in the form?

In report design view
-- Select View: Sorting and Grouping
-- set sort by to =RTrim(TheField) instead of [The Field]

I am using the RTrim function to remove trailing spaces for one of my
fields.
This is a field I need to sort by, and ever since I added the RTRim
function,
the sorting is including duplicate records, my guess is it includes one
for
the field with a space and one without. Is there a way around this? In
the
past, all records included a trailing space. Now my data source
(Oracle)
has
removed the trailing space. I need to include records from before and
after
this change took place. I am using Access 97.

Thanks,
Mary