CODEWITHSUNDEEP
ms-access
RolandTumble
RolandTumble

Reputation: 4703

Alias a Caption in Access 2007

All right, I've got two tables in my Access 2007 database (well, more than that, but the question applies to these two...):

part
  part_no
  ...
  supplier_id
  special_supplier_id
  ...

And

supplier
  supplier_id
  supplier_nm      (NB: Caption="Supplier Name")
  special_supplier_flg

Now I've built a query :

SELECT p.part_no, s.supplier_nm, ss.supplier_nm AS special_supplier_nm
FROM
  part AS p INNER JOIN
  supplier AS s ON p.supplier_id = s.supplier_id INNER JOIN 
  supplier AS ss on p.supplier_id = ss.supplier_id

All well and good, and it seems to work for the simple select (if there are any errors in the query itself, it's an artifact of retyping it for the question, and not what the question is about), and should work for code applications as well.

The problem, though, is that if I open the datasheet view of the query, both of the name fields are labeled "Supplier Name". Is there any way around this?

Upvotes: 1

Views: 3296

Answers (4)

Viggenboy
Viggenboy

Reputation: 1

WORKAROUND THAT WORKS

OK this one has been driving me bonkers because I'm using lots of data from linked (imported) tables from our company's finance, HR and CRM systems all set up by the same developer who thought captioning every Index "ID:" was a great idea. but when you have an aggregating which brings many of these together you get a big error prone mess because the subsequent query can't work out which "ID:" is which. He's my solution:

Rather than simply passing the values through, which simply passes the caption: If the field you want to re-caption is a value field, just re-label it and multiply it by 1 like so (in this my per_id field is captioned ID:):

 Perid: 1*[pe_id]

This results in a new field labelled Perid

If it is a text field just concatenate it with nothing like this:

Perid: ""&[fname]

It works.

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

Try this:

 SELECT p.part_no, s.supplier_nm, (ss.supplier_nm + '') AS special_supplier_nm. . .

However, if you're using the query builder you may need to be careful not to build the query with the "default" alias (from the caption) first, because that seems to stick around in some not-visible metadata.

Also, if you can edit this query in Access' query builder, if you right click on the field in question there is an option to set the caption property inside the query, and this will override whatever caption was on the original table column.

Upvotes: 4

HansUp
HansUp

Reputation: 97101

I see the same thing with Access 2003 for even a single-table query ... if the source field has a Caption assigned, that caption is used as the column header in the query datasheet view, regardless of whether or not I give the field an alias in the query definition.

Other than removing the Caption from the source table definition, the only way I could find to work around it was by manually re-assigning the field's Caption property with VBA. Query1 includes a field named id which has "Foo ID" as its Caption in the source table:

CurrentDb.QueryDefs("Query1").Fields("id").Properties("Caption") = "foo_id"

That command did cause the query datasheet view to use foo_id as the column header.

If you assign an alias to a query field, that alias is used as the name in the query's Fields collection. You can examine your query's field names and their captions with this procedure:

Public Sub InspectQueryFieldCaptions(ByVal pQuery As String)
    Dim fld As DAO.Field
    Dim strMsg As String

On Error GoTo ErrorHandler

    For Each fld In CurrentDb.QueryDefs(pQuery).Fields
        Debug.Print "Field: " & fld.Name, "Caption: " & fld.Properties("Caption")
    Next fld

ExitHere:
    Set fld = Nothing
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3270 ' Property not found. '
        Debug.Print "Field: " & fld.Name, "no caption"
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure InspectQueryFieldCaptions"
        MsgBox strMsg
        GoTo ExitHere
    End Select
End Sub

Upvotes: 3

Mohgeroth
Mohgeroth

Reputation: 1627

Do you have captions set in your table definition? Those definitions will show instead of what you alias. Since you are selecting a column that will have the same caption (reguardless of alias), you will see the same caption for both in the datasheet view.

Do you have the option to change the caption field? Leaving it blank will use its given name (or alias in the event of a view)

Upvotes: 0

Related Questions