Keeping the Caption when using SQL

[paty]

In a database I am creating, I constantly create tables using SQL (through VBA) that I subsequently want to use in reports. In some tables, I want the name of the field that prints on the report to be different than the field name so I have specified a caption for those fields. However, when I create other tables from my tables using SQL, the caption does not seem to carry over. Does anyone know if there is a way to do this through SQL and what the correct syntax is?

 

[GGleason]

paty,

I don't know if this is what you have in mind, but if you have a field called LastName and you want it to appear as Last Name, your SQL should be:

SELECT LastName as [Last Name]
FROM blah blah

hth,
GGleason

 

[paty]

Hi,

I thought about doing this but I dismissed it because I thought this would make the field name in the new table Last Name. If I were to go into the design of the new table, I would like the field name to still be 'LastName' but the caption to be 'Last Name'. I will try it anyway but if you have any other thoughts, I would appreciate it!

 

[GGleason]

It doesn't change the name of the field in the table, it just "tags" that field with a different name within the query.

GGleason

 

[MichaelRed]

Hmmmmmmmmmm ,

It is true that the "AS" clause doesn't change the TABLE's field name - but in subsquent usage from the QUERY, the alais is the only way to reference the field, so in the limited sense of the set of operations starting with the query (using the "AS" clause changes the reference (name) of the field.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over