Rename a field in a query

[mohobrien]

I can't fathom this one. I have a field called Current Status in a table. When I run a query I want to rename the field like this
STATUS:Current Status

No matter what I do, the field remains Current Status. I have tried putting square brackets around like this STATUS:[Current Status] but Access throws them out. Every other field I have tried is no problem.
If I add another field in the query and try to call that STATUS:AnotherField, it renames that other field to Current Status, not STATUS. Even if I remove Current Status from the query!
Any body understand what is happening?

 

[boblarson]

Where are you attempting to do this?

It should be fairly easy in the QBE grid

Under the Field area:
MyNewName:My Current Field


In SQL view:

Select [My Current Field] As MyNewName

etc.

 

[mohobrien]

That is where I am doing this. I have done this hundreds of times before. I am wondering if STATUS or Current is a reserved word.

 

[boblarson]

That is where I am doing this. I have done this hundreds of times before. I am wondering if STATUS or Current is a reserved word.

Status isn't but Current (as a word by itself) is.

See here for a comprehensive list.

 

[mohobrien]

Shouldn't putting brackets around like this [Current Status] allow it to work? Access won't accept the brackets.

 

[boblarson]

Shouldn't putting brackets around like this [Current Status] allow it to work? Access won't accept the brackets.

Can you post your database (you can clear all of the data out using my free reset tool) and then say which query you are trying this on. Then we can see what is happening.

 

[mohobrien]

I stripped everything except a bit of a table and a query.

 

[boblarson]

Okay, that was easy.

1. You need to remove the CAPTION from the field in the table.

2. Then delete the entire field from the query and do it over.

It should work.

Also a side note, you should not define lookups for fields at table level. See here for why.

 

[mohobrien]

After using access daily for ten years I find that there is a property called Caption. That's embarrassing. Thanks Bob. I'll read what you suggested.

 

[boblarson]

After using access daily for ten years I find that there is a property called Caption. That's embarrassing. Thanks Bob. I'll read what you suggested.

Hey don't feel bad. I've been using Access and developing in it for almost 14 years and I keep learning new things about it. There is a lot to learn and you don't necessarily touch on everything as you go along until one day - BOOM! you find you do.

 

[techexpressinc]

Re: Rename a field in a query - how when a caption ??

Does anyone know how to do the rename of a field with caption without using the caption name or the name of the field.

I have a user that wants a different name on the output other than the table field name and different than the caption.

I am have not found an way, yet.


I said I could so I will. I am guessing the only way would be to create a new table from the query??

Russ

 

[techexpressinc]

The fix I found is this, where old name is the string.
newname: StrConv(string, conversion [, LCID ] )
Russ

 

[Mihail]

A "solution":
So, I have a field "FieldWithCaption" with caption "FieldCaption"
Now, I create a query in QueryDesign view:
A:FieldWithCaption | NewFieldName:[A]

This work but, unfortunately, is no way to hide the first field.
Of course, if a Form or a report is based on this query, no matter.

If you need to show a query, the only one solution I can find is to design a new query, based on the previous one, where to drag only the NewFieldName.

 

[techexpressinc]

Mihail - thanks I have used your technique too.
Russ

 

[Pat Hartman]

Thankfully, Captions are much more intelligently implemented in A2010 but for earlier versions they caused almost as many problems as table level lookup fields. Your user shouldn't be looking at tables directly so Captions aren't doing anything but getting in the way in your version of Access. I would suggest removing them. In the cases where you want to export a query, use Alias' to rename the fields to more user friendly names.