locked
Concatenate a Lookup Fields with a ShortText field RRS feed

  • Question

  • I hope there is an easy way to calculate these two fields to display correctly. I have Locations table with a lookup field [FloorID] to the [FloorID] of the Floors table. I have a calculated field [Location] in the Locations table =[FloorID]+"-"+[SpacecID]. It works except since the [FloorID] is a lookup it returns the numeric ID and not the display value. Example: "3-201A" instead of "ARL-04-201A". Is there a way to display this way?

    Thank you


    John Fletcher

    Wednesday, June 24, 2015 11:15 PM

Answers

  • Hi John,

    Thanks for the detail information for this issue.

    As far as I test, this function also could only get the value of lookup field. As a workaround, we can make a join query for two tables to show the specific field using the Contact function.

    If you want the calculated field in Access 2013 web app could get the display text, I would suggest that you submit the feedback from the "Smile Face" at the right corner of Access 2013.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 29, 2015 7:26 AM

All replies

  • I hope there is an easy way to calculate these two fields to display correctly. I have Locations table with a lookup field [FloorID] to the [FloorID] of the Floors table. I have a calculated field [Location] in the Locations table =[FloorID]+"-"+[SpacecID]. It works except since the [FloorID] is a lookup it returns the numeric ID and not the display value. Example: "3-201A" instead of "ARL-04-201A". Is there a way to display this way?

    Hi John,

    As far as I understand your problem, you do not need a calculated field Location in the Locations table. You can always calculate the Location in forms and reports using Location = [Floor] + "-" + [SpaceID],

    assuming [Floor] = "ARL-4" in record with [FloorID] = 3.

    Imb.

    Thursday, June 25, 2015 9:22 AM
  • Thank you for the reply. I realize I can do this in a query or form but it is an Access Web App. Other programs are connected to this table.

    John Fletcher

    Thursday, June 25, 2015 2:50 PM
  • Hi John,

    >>Concatenate a Lookup Fields with a ShortText field<<

    Yes, we can create calculated filed to contact the fields we wanted using concat function. It will concat the value of lookup filed and here is a sample for your reference:

    Concat([FloorID],"-",[SpacecID])

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 26, 2015 2:34 AM
  • Hi Fei,

    I get this error message.  Since [FloorID] is a Lookup is it not trying to "concat" the numeric value and not the display text value which is what I want.


    John Fletcher

    Friday, June 26, 2015 3:06 PM
  • Hi John,

    Not sure if this will work. haven't had time to test it. It seems you need to get to the column the data is in, in the hidden table made by Access for the LookUP Field. You can try the following;

    Concat([FloorID].[Column](2),"-",[SpacecID])

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012



    • Edited by KCDW Friday, June 26, 2015 6:53 PM
    Friday, June 26, 2015 6:51 PM
  • Hi John,

    Thanks for the detail information for this issue.

    As far as I test, this function also could only get the value of lookup field. As a workaround, we can make a join query for two tables to show the specific field using the Contact function.

    If you want the calculated field in Access 2013 web app could get the display text, I would suggest that you submit the feedback from the "Smile Face" at the right corner of Access 2013.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 29, 2015 7:26 AM