How to Change Fieldname that has CaptionName

[jhrBanker]

I receive a db that has a single table with 52 Fields. Each Field has a
Caption defined in the table. For example, Fieldname [CompanyNbr] has a
Caption of "Bank Nbr". How do I change the Column Heading in a query, since
I don't like either name (CompanyNbr or Bank Nbr)? I've tried both "CoNbr:
[CompanyNbr]" and "CoNbr: [Bank Nbr]", and neither option works. I've also
tried the SQL version (SELECT [CompanyNbr] as "CoNbr" FROM Directory. I
receive a new table daily, so it's not pratical for me to have to manually
delete the Caption's from each of the fields.

 

[John W. Vinson]

I receive a db that has a single table with 52 Fields. Each Field has a
Caption defined in the table. For example, Fieldname [CompanyNbr] has a
Caption of "Bank Nbr". How do I change the Column Heading in a query, since
I don't like either name (CompanyNbr or Bank Nbr)? I've tried both "CoNbr:
[CompanyNbr]" and "CoNbr: [Bank Nbr]", and neither option works. I've also
tried the SQL version (SELECT [CompanyNbr] as "CoNbr" FROM Directory. I
receive a new table daily, so it's not pratical for me to have to manually
delete the Caption's from each of the fields.

It's best not to use either a table datasheet OR a query datasheet for
displaying and editing data. For displaying onscreen use a Form; for printing
use a Report. On these you can use Label controls to set any captions or
identifiers you wish.

You say "neither option works" without saying HOW they fail. You would use the
actual fieldname though in the query:

CoNbr: [CompanyNbr]

in a vacant Field cell should work, so long as there isn't already a field
named CoNbr. What symptoms do you get?

 

[jhrBanker]

When I use "CoNbr: [CompanyNbr]", the resultant Column Heading is "Bank
Nbr", which is the CaptionName.
When I use "CoNbr: [Bank Nbr]", Access doesn't recognize "Bank Nbr", and
the dialog box pops up.

I need to combine/use the fields in this table with fields in other db
tables. I was hoping to link the table (Directory) in this db to the db
containing the other tables. But neither the FieldNames nor the CaptionNames
are the names I'd like to see as column headings in my queries, which will
eventually be exported to other file formats, such as Excel.

I receive a db that has a single table with 52 Fields. Each Field has a
Caption defined in the table. For example, Fieldname [CompanyNbr] has a
Caption of "Bank Nbr". How do I change the Column Heading in a query, since
I don't like either name (CompanyNbr or Bank Nbr)? I've tried both "CoNbr:
[CompanyNbr]" and "CoNbr: [Bank Nbr]", and neither option works. I've also
tried the SQL version (SELECT [CompanyNbr] as "CoNbr" FROM Directory. I
receive a new table daily, so it's not pratical for me to have to manually
delete the Caption's from each of the fields.

It's best not to use either a table datasheet OR a query datasheet for
displaying and editing data. For displaying onscreen use a Form; for printing
use a Report. On these you can use Label controls to set any captions or
identifiers you wish.

You say "neither option works" without saying HOW they fail. You would use the
actual fieldname though in the query:

CoNbr: [CompanyNbr]

in a vacant Field cell should work, so long as there isn't already a field
named CoNbr. What symptoms do you get?

 

[Evi]

I receive a db that has a single table with 52 Fields. Each Field has a
Caption defined in the table. For example, Fieldname [CompanyNbr] has a
Caption of "Bank Nbr". How do I change the Column Heading in a query, since
I don't like either name (CompanyNbr or Bank Nbr)? I've tried both "CoNbr:
[CompanyNbr]" and "CoNbr: [Bank Nbr]", and neither option works. I've also
tried the SQL version (SELECT [CompanyNbr] as "CoNbr" FROM Directory. I
receive a new table daily, so it's not pratical for me to have to manually
delete the Caption's from each of the fields.

It's best not to use either a table datasheet OR a query datasheet for
displaying and editing data. For displaying onscreen use a Form; for printing
use a Report. On these you can use Label controls to set any captions or
identifiers you wish.

You say "neither option works" without saying HOW they fail. You would use the
actual fieldname though in the query:

CoNbr: [CompanyNbr]

in a vacant Field cell should work, so long as there isn't already a field
named CoNbr. What symptoms do you get?

Forgive me for butting in please John. It's Monday!

John, is there a VBA method for changing the Caption Property in a table?
I'm presuming that the Field Names are always the same.
A code which cycles throught the says
If Field Name = CompanyNbr Then
Caption = CoNbr

If there was, he could have that in a Module to run whenever he receives a
new version of this table.

Jhr, using John's suggestion, to change the Caption in a query, you use the
field name rather than the caption name.
So if your field (ie not the caption) was called CompanyNbr and you wanted
the 'Caption' CoNbr in your Excel Export, your query would have

CoNbr:[CompanyNbr]


Evi

 

[jhrBanker]

Evi: Please read my entire post. It doesn't work.
When I use CoNbr: [CompanyNbr] in the query to change the FieldName
CompanyNbr to CoNbr it doesn't work. The query ColumnHeader is Bank Nbr,
which is the CaptionName for the FieldName CompanyNbr.

I receive a db that has a single table with 52 Fields. Each Field has a
Caption defined in the table. For example, Fieldname [CompanyNbr] has a
Caption of "Bank Nbr". How do I change the Column Heading in a query, since
I don't like either name (CompanyNbr or Bank Nbr)? I've tried both "CoNbr:
[CompanyNbr]" and "CoNbr: [Bank Nbr]", and neither option works. I've also
tried the SQL version (SELECT [CompanyNbr] as "CoNbr" FROM Directory. I
receive a new table daily, so it's not pratical for me to have to manually
delete the Caption's from each of the fields.

It's best not to use either a table datasheet OR a query datasheet for
displaying and editing data. For displaying onscreen use a Form; for printing
use a Report. On these you can use Label controls to set any captions or
identifiers you wish.

You say "neither option works" without saying HOW they fail. You would use the
actual fieldname though in the query:

CoNbr: [CompanyNbr]

in a vacant Field cell should work, so long as there isn't already a field
named CoNbr. What symptoms do you get?

Forgive me for butting in please John. It's Monday!

John, is there a VBA method for changing the Caption Property in a table?
I'm presuming that the Field Names are always the same.
A code which cycles throught the says
If Field Name = CompanyNbr Then
Caption = CoNbr

If there was, he could have that in a Module to run whenever he receives a
new version of this table.

Jhr, using John's suggestion, to change the Caption in a query, you use the
field name rather than the caption name.
So if your field (ie not the caption) was called CompanyNbr and you wanted
the 'Caption' CoNbr in your Excel Export, your query would have

CoNbr:[CompanyNbr]


Evi

 

[Duane Hookom]

You can add a function to your standard modules and then call it with your
table name. You could write another function that would loop through all
tables to run the function.

Function RemoveCaptions(pstrTable As String) As Boolean

'---------------------------------------------------------------------------------------
' Procedure : RemoveCaptions
' Author : Duane Hookom
' Date : 11/3/2008
' Purpose : remove the caption property of fields in a local table
' Argument : pstrTable is the name of a table in your local MDB
' Comments : if the table is linked, the code would need to be changed to
' use OpenDatabase rather than Currentdb

'---------------------------------------------------------------------------------------
'
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field

On Error GoTo RemoveCaptions_Error

Set db = CurrentDb
Set td = db.TableDefs(pstrTable)

For Each fd In td.Fields
fd.Properties("Caption") = fd.Name
Next
Set fd = Nothing
Set td = Nothing
Set db = Nothing

On Error GoTo 0
RemoveCaptions = True
Exit Function

RemoveCaptions_Error:
Select Case Err.Number
Case 3270 'property not found
'ignore
Resume Next
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure RemoveCaptions of Module basStringFunctions"
End Select

End Function

--
Duane Hookom
Microsoft Access MVP

Evi: Please read my entire post. It doesn't work.
When I use CoNbr: [CompanyNbr] in the query to change the FieldName
CompanyNbr to CoNbr it doesn't work. The query ColumnHeader is Bank Nbr,
which is the CaptionName for the FieldName CompanyNbr.

On Sun, 2 Nov 2008 11:29:01 -0800, jhrBanker

I receive a db that has a single table with 52 Fields. Each Field has a
Caption defined in the table. For example, Fieldname [CompanyNbr] has a
Caption of "Bank Nbr". How do I change the Column Heading in a query, since
I don't like either name (CompanyNbr or Bank Nbr)? I've tried both "CoNbr:
[CompanyNbr]" and "CoNbr: [Bank Nbr]", and neither option works. I've also
tried the SQL version (SELECT [CompanyNbr] as "CoNbr" FROM Directory. I
receive a new table daily, so it's not pratical for me to have to manually
delete the Caption's from each of the fields.

It's best not to use either a table datasheet OR a query datasheet for
displaying and editing data. For displaying onscreen use a Form; for printing
use a Report. On these you can use Label controls to set any captions or
identifiers you wish.

You say "neither option works" without saying HOW they fail. You would use the
actual fieldname though in the query:

CoNbr: [CompanyNbr]

in a vacant Field cell should work, so long as there isn't already a field
named CoNbr. What symptoms do you get?

Forgive me for butting in please John. It's Monday!

John, is there a VBA method for changing the Caption Property in a table?
I'm presuming that the Field Names are always the same.
A code which cycles throught the says
If Field Name = CompanyNbr Then
Caption = CoNbr

If there was, he could have that in a Module to run whenever he receives a
new version of this table.

Jhr, using John's suggestion, to change the Caption in a query, you use the
field name rather than the caption name.
So if your field (ie not the caption) was called CompanyNbr and you wanted
the 'Caption' CoNbr in your Excel Export, your query would have

CoNbr:[CompanyNbr]


Evi

 

[Evi]

Wow! I did read your post but I didn't realise that even a formula directly
based on the field would inherit the caption! What a drag!
Good thing Duane has come up with a solution. It wipes out the captions
which means you can then do that formula.
Evi

Evi: Please read my entire post. It doesn't work.
When I use CoNbr: [CompanyNbr] in the query to change the FieldName
CompanyNbr to CoNbr it doesn't work. The query ColumnHeader is Bank Nbr,
which is the CaptionName for the FieldName CompanyNbr.

On Sun, 2 Nov 2008 11:29:01 -0800, jhrBanker

I receive a db that has a single table with 52 Fields. Each Field has a
Caption defined in the table. For example, Fieldname [CompanyNbr] has a
Caption of "Bank Nbr". How do I change the Column Heading in a

query,
since

I don't like either name (CompanyNbr or Bank Nbr)? I've tried both "CoNbr:
[CompanyNbr]" and "CoNbr: [Bank Nbr]", and neither option works.

I've
also

tried the SQL version (SELECT [CompanyNbr] as "CoNbr" FROM

Directory.
I

receive a new table daily, so it's not pratical for me to have to manually
delete the Caption's from each of the fields.

It's best not to use either a table datasheet OR a query datasheet for
displaying and editing data. For displaying onscreen use a Form; for printing
use a Report. On these you can use Label controls to set any captions or
identifiers you wish.

You say "neither option works" without saying HOW they fail. You would

use
the

actual fieldname though in the query:

CoNbr: [CompanyNbr]

in a vacant Field cell should work, so long as there isn't already a field
named CoNbr. What symptoms do you get?

Forgive me for butting in please John. It's Monday!

John, is there a VBA method for changing the Caption Property in a table?
I'm presuming that the Field Names are always the same.
A code which cycles throught the says
If Field Name = CompanyNbr Then
Caption = CoNbr

If there was, he could have that in a Module to run whenever he receives a
new version of this table.

Jhr, using John's suggestion, to change the Caption in a query, you use the
field name rather than the caption name.
So if your field (ie not the caption) was called CompanyNbr and you wanted
the 'Caption' CoNbr in your Excel Export, your query would have

CoNbr:[CompanyNbr]


Evi

 

[John W. Vinson]

I didn't realise that even a formula directly
based on the field would inherit the caption! What a drag!

Me neither, hence my incorrect answer!!! Drag indeed. Thanks (again, including
many past posts from which I've learned!) Duane!

 

[Duane Hookom]

This is just another reason why I don't ever set the following Field
Properties in table designs:
Format
Input Mask
Look Up (shudder)
Caption