Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Alias (A97 SR2)

    I want to create a combo box on a form showing headings. I have specified the headings using the AS [HeadingName] in the combo box rowsource SQL statement, but as the base table column has a caption defined, it is the caption from the table which appears, and not my Alias name. Is there an easy way to ignore the Caption from the table without amending the table design ?

    Thanks for your help

    Nick

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Alias (A97 SR2)

    Charlotte

    Your reply seems to have got lost, but here's some more info :

    My SQL was :

    SELECT Sectors.TechLogNumber AS TLP,
    tblAircraft.Registration AS Reg,
    Sectors.[Actual Departure Date] AS [Date],
    [Sectors]![Departure Airport] & "-" & [Sectors]![Arrival Airport] AS Route
    FROM tblAircraft ...etc

    In the comboBox header, the 2nd column appeared as "Aircraft Registration", which is the Caption field in tblAircraft for field Registration, and not as "Reg", which was what I wanted to see.

    HTH

    Nick

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Alias (A97 SR2)

    The alias appies to the field, not its caption. When you set the field alias, that is used as if it were the field name elsewhere in the query and in any form or report based on the query. The easiest workaround is to make sure you use a saved query as the row source for you combobox. Go into the saved query and set the caption property for the field in question to the caption you want to use instead of the table field's caption.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Alias (A97 SR2)

    Charlotte

    Thanks for your reply but I'm not sure how to go about what you are suggesting. Could you show me how to do it in this sample SQL :
    SELECT tblAircraft.Registration AS Reg FROM tblAircraft
    The Field Caption is "Aircraft Registration"

    Thanks

    Nick

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Alias (A97 SR2)

    You don't do it in the SQL, you do it from the query grid. If you're creating the query in code, then I don't know how you would do it easily. It would be possible to do it using DAO on a saved querydef, but that would be overkill.

    On a saved query, open it in design view and bring up the properties dialog for the field you aliased. You'll see a caption property, which you can set there. Otherwise, it defaults to the caption in the table.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Alias (A97 SR2)

    Thank you Charlotte

    It now works perfectly

    Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •