Results 1 to 7 of 7
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94

    Access 2016 Query truncating Large Text field - No Grouping or Distinct

    Hello - I have a shared database with a query that pulls data from a table to create a report based on the selected user and date. There are 3 fields in the table that are Large Text - "AuditComments", "PositiveFeedback", & "Opportunity for Improvement". The query will truncate these fields but can't figure out the reason. Below is the sql for the query:



    Code:
    SELECT tblSalvageAudits.UserName, tblSalvageAudits.ClaimNumber, tblSalvageAudits.COL, tblSalvageAudits.[ISO & NMVTIS], tblSalvageAudits.StateCompliance, tblSalvageAudits.TitleWork, tblSalvageAudits.Proceeds, tblSalvageAudits.Activities, tblSalvageAudits.LogNotes, tblSalvageAudits.ProblemResolution, tblSalvageAudits.[Improve Recovery Opportunities], tblSalvageAudits.[Salvage Tab Entry], tblSalvageAudits.AuditComments, tblSalvageAudits.PositiveFeedback, tblSalvageAudits.[Opportunity for Improvement], tblSalvageAudits.OverallScore, tblSalvageAudits.AuditNameFROM tblSalvageAudits
    WHERE (((tblSalvageAudits.UserName)=[Forms]![frmSalvageReports]![cboEmployeeQuarter]) AND ((tblSalvageAudits.OverallScore) Is Not Null) AND ((tblSalvageAudits.DateAudited) Between [Enter Start Date] And [Enter End Date]));
    Thank you for any advice you can give me!

    A little additional information -I have also checked the following:
    Remove anything from the Format property of:

    • the field in table design (lower pane);
    • the field in query design (properties sheet);
    • the text box on your form/report.
    Last edited by jbeets; 11-12-2020 at 01:41 PM. Reason: Additional information

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    its a microsoft flaw they never fixed. but why would anybody want ALL their data? (thanks a lot msoft)

    I have seen a way to get all the data in the long text field. But I can find it this moment. will keep looking.

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    HAHA I love these ongoing bugs that never get fixed! Thanks for checking!

    I saw someone mentioned using =DLookup in the report text box - do you think thats a viable option?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dcount does seem to give me all chars past 255
    but 1 of my queries on the field does too. no truncate.

    is it possible all your data is there, but on separate lines?

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try creating a table with 1 memo field named as (e.g.) "NewText". Do not enter any data in this new table.
    Create a UNION query such as

    SELECT NewText FROM tblNewTable UNION ALL SELECT tblSalvageAudits...

    I've never tried this trick and don't have anything to test it on at the moment.
    P.S. your sql as formatted in your post makes it very difficult to look for other reasons such as the use of built in functions. You might be interested in shortening your sql by using table aliases. Shortened (aliased) and formatted it could look like this
    Code:
    SELECT TSA.UserName, TSA.ClaimNumber, TSA.COL, TSA.[ISO & NMVTIS], TSA.StateCompliance, 
    TSA.TitleWork, TSA.Proceeds, TSA.Activities, TSA.LogNotes, TSA.ProblemResolution, 
    TSA.[Improve Recovery Opportunities], TSA.[Salvage Tab Entry], TSA.AuditComments, 
    TSA.PositiveFeedback, TSA.[Opportunity for Improvement], TSA.OverallScore, TSA.AuditName 
    FROM tblSalvageAudits As TSA 
    WHERE (((TSA.UserName)=[Forms]![frmSalvageReports]![cboEmployeeQuarter]) AND 
    ((TSA.OverallScore) Is Not Null) AND 
    ((TSA.DateAudited) Between [Enter Start Date] And [Enter End Date]));
    Too bad there's no sql formatter in this forum
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    ranman256 - I checked and the data is not on a separate line sadly.

    Micron - Thank you for the advice on the code formatting. I always appreciate any help I can get!

    I tried your suggestion on the UNION query but the text still cut off in the results.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can you post a compacted and zipped copy? I tried to replicate your issue in a test db and I am getting all of the text when I query. I copied the result to Word to do a character count and it is over 1600. Then again, I have no joins and perhaps that is a cause. Or you could experiment and copy the query and remove everything that doesn't belong to the table with the memo fields and see what happens for you.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-29-2020, 06:05 AM
  2. Replies: 4
    Last Post: 11-04-2019, 06:53 PM
  3. Replies: 3
    Last Post: 11-14-2018, 01:20 PM
  4. Access 2013 truncating Long Text fields in Reports
    By TundraMonkey in forum Reports
    Replies: 13
    Last Post: 04-18-2017, 08:11 PM
  5. Query is Truncating Memo Field
    By Huddle in forum Queries
    Replies: 1
    Last Post: 07-24-2012, 02:24 PM

Tags for this Thread

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