Results 1 to 9 of 9
  1. #1
    ZipDoc is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    5

    Report TextBox printing ID Number, not text

    Ok, this is probably something really simple, but it's driving me to drink (more). I have a report based on a simple query that lists a lectures based on overall subject topics. I would like to also list the course section within which the lecture is given:



    Overall Subject Heading
    Section Title
    Lecture Title
    When I run the query, it's perfect; everything displays the way it should. However, when I run the report, the Section Title displays as a number (I assume the ID). At first I found the field in the main table for Section Title was set to number. Changed it to text, no effect.

    What am I overlooking!? Thanks in advance.

    Zipdoc

  2. #2
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    Check your report recordsource, it probably imcludes if ID, not the name.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Any chance you might be using Lookup Fields?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  4. #4
    ZipDoc is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    5
    Yes, I am using lookup fields within the original table. I have read that this is not necessarily the best, but I thought the idea behind these drop down lists was to maintain data integrity. Nonetheless, I have changed these lookup fields back to a textbox. The form made for data entry has kept the drop down.

    I am still left with the IDnumber in the report, not the section name. When I add the appropriate table to the query that has the section list, I get the "ambiguous outer join" error. I had used a similar technique on other queries and reports, and never had run into any problem. This is the SQL statement if it helps:

    SELECT tblMainLectureList.CourseSection, tblNBMEList.NBMELabel, tblMainLectureList.LectureDate, tblMainLectureList.FacultyLastName, tblMainLectureList.LectureTitle, qryFullNBMEList.Class1, qryFullNBMEList.Class2, qryFullNBMEList.Class3, qryFullNBMEList.Class4
    FROM tblSectionList INNER JOIN (tblMainLectureList RIGHT JOIN (qryFullNBMEList INNER JOIN (tblNBMEList LEFT JOIN tblNBMETag ON tblNBMEList.NBMELabelID = tblNBMETag.NBME_Classification) ON qryFullNBMEList.NBMELabel = tblNBMEList.NBMELabel) ON tblMainLectureList.LectureID = tblNBMETag.LectureID) ON tblSectionList.SectionID = tblMainLectureList.CourseSection;

    Thanks in advance, and perhaps I should repost in the queries forum? I should note that the query looked fine in datasheet view. Of course, now that I have changed the lookup fields, the query displays the ID number as well. Sorry to be so dense.

    Zipdoc

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    All that Lookup Fields in tables accomplish is putting the ComboBox on your form (where it belongs and can easily be done with the cbo wizard) and then basically "hide" what is really in the table most of the time. In other words it just confuses the programmer. As for the query, I would use the query builder and add the appropriate table and then pull the name from the other table instead of the current field you are pulling for the Section Title.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    ZipDoc is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    5
    Thank you for your speedy reply and your patience. I removed the lookups, and rebuilt the query. It runs fine (though I have not yet tried to use the report). I want the query to list ALL the subject headings, including those that do not have any Section or Lectures associated with them. This is where I continue to get my SQL error.
    Zipdoc

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Are you talking about the "ambiguous outer join" error? If so then rebuild the query one table at a time with the Query Builder until youn get the error again. That should tell you what is causing the error.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  8. #8
    ZipDoc is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    5
    Thanks, RuralGuy. I'll rebuild and try and narrow down the issue and repost if I run into a snag.
    Cheers,
    Zipdoc

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    We'll keep an eye out for ya!
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Printing report from Form
    By cotri in forum Access
    Replies: 8
    Last Post: 05-24-2013, 12:01 PM
  2. Replies: 3
    Last Post: 03-28-2009, 06:06 PM
  3. Report Printing Error
    By gjohnson71 in forum Reports
    Replies: 4
    Last Post: 03-07-2009, 12:36 PM
  4. Keeping text in a report textbox
    By Hawkx1 in forum Reports
    Replies: 2
    Last Post: 11-13-2008, 04:11 PM
  5. Report will not print a bound textbox
    By vvrt in forum Reports
    Replies: 0
    Last Post: 03-16-2006, 02:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums