none
when i create a report based on query it is not showing lookup value instead it is showing id numbers in ms access2010

    Question

  • In ms access , i have created a table in issue tracking web database, the table has a lookupfiled from user table

    In query it is showing the lookup value

    but when i create a report based on this query it is not showing values , it is displaying the value related  ID

    Could you please help me on how to display the value instead of the ID.

    Thanks in advance.


    Rasna

    Wednesday, January 30, 2013 9:29 AM

Answers

  • Hi Rasna,

    Welcome to the MSDN forum.

    Since there is none combo box control for the report in web Access, you may try the following way:

    Note: Here I use "Northwind sales web database.accdb" as the demo database.

    1. Create a query based on the main table and the look up table. 

    2. Create a report based on the query created in Step 1. 

    Hope it helps.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 05, 2013 8:27 AM

All replies

  • put your report into design view;  delete the field that is not working correctly; and then from the field list - drag in that field.   if it displays in design view like a combobox - then when you change back to report view it will probably work.

    its important that you drag in the field new from the field list and not short cut by perhaps copying another field and renaming its record source.....

    if this doesn't fix it - then you'll have to go to the record source of the report and modify this query to include the looked up table - and bring in the field you really want from this added table - and then of course modify your report to have this new field display rather than the original field.....

    hope this fixes it for you...

    Wednesday, January 30, 2013 10:42 PM
  • Hello,

    In form design, open the Format tab on the Properties window. Make Column Widths property 0 in the first place like 0";2" if you have 2 columns where first column is ID and second column - value.

    Nadia

    • Proposed as answer by KCDW Thursday, January 31, 2013 3:20 AM
    Thursday, January 31, 2013 1:45 AM
  • hii thanks for reply

    but it is not working for me , still i am getting the same problem


    Rasna

    Friday, February 01, 2013 11:10 AM
  • Can you post the SQL of the Report. Also verify the RecordSource is correct. It is important to ensure the ID field in the recordsource is listed first and the value field listed second in the SQL for Nadias answer to be correct. Also you should check to see which field is the Bound Column (should be the ID field).

    Chris Ward

    Friday, February 01, 2013 8:20 PM
  • Hi Rasna,

    Welcome to the MSDN forum.

    Since there is none combo box control for the report in web Access, you may try the following way:

    Note: Here I use "Northwind sales web database.accdb" as the demo database.

    1. Create a query based on the main table and the look up table. 

    2. Create a report based on the query created in Step 1. 

    Hope it helps.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 05, 2013 8:27 AM
  • In your report you may have to use an unbound textbox and use a formula similar to this

    =[cboManufacturerLookup].[Column](2)

    Replace [cboManufacturerLookup] with [YourFieldNameHere].[Column](2)

    Replace the Column # with whatever column is more appropriate for the data you wish displayed in the textbox.

    HTH


    Chris Ward

    Tuesday, February 05, 2013 4:26 PM
  • I'd strongly recommend that you follow Yoyo Jiang's advice of basing the report on a query which joins the tables.  Combo boxes only serve a useful purpose when entering data in forms, not when viewing it in reports.

    Ken Sheridan, Stafford, England

    Tuesday, February 05, 2013 5:26 PM
  • I don't necessarily disagree.

    I do have a different view having had to find solutions on my own. I can tell you that if you have a combo box on your report it will in fact work and give you the ability to have unbound textboxes to draw information from the cbo that is bound

    Example: with this SQL as the Reports RecordSource I can bring the information to the report.

    SELECT Products.SubmittalDate, Products.PEBNumber, Products.BoardDate, Products.ProductName, Products.ProductDescription, Products.RecommendedUses, Products.cboManufacturerLookup, Products.cboPrimaryDistributor
    FROM Products
    WHERE (((Products.BoardDate) Like [Board Date]))
    ORDER BY Products.PEBNumber;

    In the cbo control's RowSource I can bring more specifically the data needed for the unbound text boxes

    SELECT Company.CompanyID, Company.Company, Company.[Location/Name], Company.[Last Name], Company.[First Name], Company.[E-mail Address], Company.[Job Title], Company.[Business Phone], Company.[Home Phone], Company.[Mobile Phone], Company.[Fax Number], Company.Address, Company.City, Company.[State/Province], Company.[ZIP/Postal Code], Company.[Country/Region], Company.[Web Page]
    FROM Company
    ORDER BY Company.Company, Company.[State/Province], Company.City, Company.[First Name];

    in unbound textboxes with simple formulae Like;


    =[cboManufacturerLookup].[Column](1) This will show the CompanyID

    =[cboManufacturerLookup].[Column](2) This will show the Company Name

    and something like this

    =[cboManufacturerLookup].[Column](12) & " " & [cboManufacturerLookup].[Column](13) & " " & [cboManufacturerLookup].[Column](14)

    will show the City, Sate and Zip

    This is a valid method of showing the specific data in the report you are looking for.


    Chris Ward

    Tuesday, February 05, 2013 5:58 PM
  • Yes, it will work in most cases, but as Yoyo Jiang points out, not all.  Nevertheless, whatever the context, I still don't think it's a necessary or appropriate solution.

    Ken Sheridan, Stafford, England

    Tuesday, February 05, 2013 7:03 PM
  • Ken, I respect your words as I can see you have more knowledge and experience than I. Can you please explain how or when this would not work and how other solutions would be better. I know you have a lot of examples of things on your Skydrive, do you have examples of better methods for bringing the information to Reports?

    Thanks in advance.


    Chris Ward

    Tuesday, February 05, 2013 7:40 PM
  • While it's kind of you to say so, Chris, I don't think there's much evidence to support your first sentence.  I'm just a simple Irish bogtrotter who has used Access over a number of years to model the sort of scientific data I've worked with as an environmental planning officer.  I'd never dream of calling myself an experienced developer and I know how little I do know compared with many others here.

    I'm going by what Yoyo Jiang said, who is clearly going to be more conversant with the subject than I, that Access web databases don't support the use of a combo box in reports.  In contexts where they do I just feel that in a report, using a control which is designed for selecting from a list of values in a form, is like using a spade when you should be using a shovel.  It'll do the job, but is not what it's designed for.  While a form for data entry must be based on an updatable recordset, a report doesn't so using a query which joins the tables is not going to give rise to non-updatability problems.  It also means a report can be restricted on the text values, so if you want to report all your contacts named Smith you can do so easily as the value is the same in each row, whereas with a combo box the ContactID value for John Smith will not be the same as for Jane Smith.  I've seen many a post in the forums where people have tried to filter in this way and wondered why they get no results.

    Ken Sheridan, Stafford, England

    Tuesday, February 05, 2013 9:03 PM
  • Hi Rasna,

    I temporarily marked the reply as answer and you can unmark it if it provides no help.
    Please feel free to let us know if you have any concern.

    Thanks for your understanding and have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 08, 2013 6:28 AM