SQL "AS" clause not working trying to rename a field.

[John Hazen]

I did some experiments and have found some very strange "behavior". If I build a query on an Employee table using EmpNo:EmployeeID as the Field, I get "Employee ID" as the name in the output (I presume, taken from the field's caption property in the Employee table) My rename to "EmpNo" is ignored. However, if I open the query field's column property sheet (by right clicking on it) and fill in a some other name in the caption property, (like Emp Num) then that name is used and overrides both the specified name (which still shows "Field" box) and the caption property in the table. Now here is the "strange" part... The SQL remains the same either way! It says: "Select EmployeeID as EmpNo from Employees". But when you run it, the query's caption property name is used.
Is there a way around this? I need to use this SQL in a combo box rowsource and the field must be renamed to fit in the column.

 

[The_Doc_Man]

Is there a chance that the name you wanted to use is already in use for that table?

Either as the name of another field or as the name of a named index?

 

[John Hazen]

No chance. I have even tried some different names like "jfgjg" just to be sure.

 

[Pat Hartman]

I have to admit that I have not figured out how to control this either. In fact, it caused me so much grief in one of the first app's I developed that I no longer enter captions at all when I define tables. The other thing I don't do is to link the table column to a combobox for lookup. The time that these two things save when developing forms and reports is far outweighed by the problems they cause with queries.

 

[John Hazen]

New Discovery: A "trick" to get rowsource SQL to ignore table defined captions.

Select [myfield] & "" AS [my field] from...

This works because Access does not consider myfield & "" to actually be coming from a table, but rather views it as the result of a concatenation. So it allows you to override the table's caption using the "AS" clause text for the column heading.

If you specify just the field name (from a table) like this:

Select [myfield] AS [My Field] from...

Access says, "Oh, this field is coming from a table... I better go get the table caption for the column heading. If it finds one, it uses it for the column heading and ignores your "AS" clause.

P.S. If this field is the bound field, I think you would need to include it again with the column width = 0.