Alias a Caption in Access 2007

Question

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?

Answer 1

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)

Answer 2

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

Answer 3

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.