How Can I Sort an ADO Table on a Fieldname Containing a Space?

Question

I am using Delphi, but this is a simple and general problem:

I'm doing the following:

var
  ArticlesTable: TADOTable;

begin
  ArticlesTable.DisableControls;
  ArticlesTable.Sort := 'CITY';
  ArticlesTable.First;
  while not ArticlesTable.Eof do begin
    ...
    ArticlesTable.Next;
  end;

This works very well and allows me to efficiently process the records one by one with the records ordered ascending by the CITY field as they are coming in.

However, now I wanted to order by the field "LAST NAME" which has an embedded space in it. But when I use the statement:

  ArticlesTable.Sort := 'CITY';

I get the error message:

  EOleException:  Arguments are of the wrong type, are out of acceptable range, 
                  or are in conflict with one another.

I have seen the help on the SORT string syntax. It says to separate multiple fields by commas, and to add ASC or DESC after a field to sort ascending or descending. But it doesn't say what to do if a fieldname has a space in it.

I've tried putting the field name in single and double quotes and even using #20 as the space character, but those don't work.

I can't change the field name on the Microsoft Access database, because there are other programs I don't work with that depend on it.

I'm sure there must be an easy way to do this that I don't know about.

Answer 1

Have you tried surrounding the fieldname by square brackets? for example:

ArticlesTable.Sort := '[LAST NAME]';