Control Not Showing All the Data

[Tophan]

Good afternoon,

I have a control on a report that is not showing all the data. In the Properties for the control, I have Can Grow set to yes. I tried changing the Text Format from plain text to rich text but that is not a valid option for this control. Any suggestions on how to correct this?

 

[CJ_London]

if the control overlaps another, it will disable cangrow

 

[Tophan]

I'm not seeing any overlaps. It's just cutting off mid sentence when there is room to complete the sentence.

I also checked the section and that too has the Can Grow set to yes.

 

[Minty]

Is it a memo field , and what is the SQL of the report source query?

 

[CJ_London]

can you upload a screenshot to demonstrate the issue

Also what version of access are you using?

 

[Tophan]

Hi..yes it is a memo field. The SQL is as follows (hope I'm doing this correctly)

SELECT [Copy Of QRYSmryDwgRegRpt].ContractNo, [Copy Of QRYSmryDwgRegRpt].ContractName, [Copy Of QRYSmryDwgRegRpt].IssuedBy, [Copy Of QRYSmryDwgRegRpt].DrawingNo, [Copy Of QRYSmryDwgRegRpt].DwgNo, [Copy Of QRYSmryDwgRegRpt].DrawingTitle, [Copy Of QRYSmryDwgRegRpt].MaxOfDateIssued, Max([Copy Of QRYSmryDwgRegRpt].LastOfRev1) AS MaxOfLastOfRev1, Last([Copy Of QRYSmryDwgRegRpt].LastOfRevNotes) AS LastOfLastOfRevNotes, [Copy Of QRYSmryDwgRegRpt].DrawingID
FROM [Copy Of QRYSmryDwgRegRpt] INNER JOIN TBLDwgRegisterDtls ON (TBLDwgRegisterDtls.Rev = [Copy Of QRYSmryDwgRegRpt].LastOfRev1) AND ([Copy Of QRYSmryDwgRegRpt].DrawingNo = TBLDwgRegisterDtls.DrawingNo)
GROUP BY [Copy Of QRYSmryDwgRegRpt].ContractNo, [Copy Of QRYSmryDwgRegRpt].ContractName, [Copy Of QRYSmryDwgRegRpt].IssuedBy, [Copy Of QRYSmryDwgRegRpt].DrawingNo, [Copy Of QRYSmryDwgRegRpt].DwgNo, [Copy Of QRYSmryDwgRegRpt].DrawingTitle, [Copy Of QRYSmryDwgRegRpt].MaxOfDateIssued, [Copy Of QRYSmryDwgRegRpt].DrawingID;

Please let me know if this is what you mean when you asked for SQL

 

[Tophan]

I've attached a screeshot of what the report is showing and circled the field that the text is incomplete.

The full text should be as follows

"ABM Room - revert to original position for table and overhead shelving. Manager (116) - indicated existing gypsum enclosure for AC line; relocated glass partition and door changed to swing door. Omit glass returns to existing perimeter windows, partition to butt against nearest window mullion"

The part in bold is what is cutting off

 

[Tophan]

The query is somewhat complex (for me at least) and is based on 2 or 3 other queries. It is a summary showing the last entries of a construction drawing register. The detailed drawing register which shows all drawing revisions to date is displaying correctly.

 

[Minty]

It's not the control - it's truncation of the memo field. Have a look here http://allenbrowne.com/ser-63.html

 

[Tophan]

I've tried the different workarounds but none are working or are applicable.

I was wondering if the database itself is getting too big or is becoming corrupt as I have noticed a few other quirks recently. I have been using this for 3 or 4 years now and this is the first time I have encountered this problem.

Do you think this could be the problem?

 

[JHB]

..
I was wondering if the database itself is getting too big or is becoming corrupt as I have noticed a few other quirks recently...
Do you think this could be the problem?

To big, no that is not the problem.
It could be corrupt, (then do a "Compact & Repair"), but the problem you've is like Minty pointed out "it's truncation of the memo field".
Could you post a stripped down version of your database, zip it, + name of the report in which you've the problem.

 

[Minty]

The normal way to get around this is to remove the memo field from your initial query. Do all your grouping in the initial query without the memo field(s) - then using that query in another query join the memo field(s) back in for display purposes.

 

[Tophan]

Good morning...sorry for the delay in responding (5 hour time difference).

I have edited the database as much as possible attach the compressed file.

The issue is the Drawing Register. When the main menu comes up, go to drawing registers and select Contract Scotiabank. Then select Summary Drawing Register on the right. The drawing number I noticed the issue with was Drawing A02.

If you go back to the register and then select drawing number 20170106-A02 you will see what was actually entered for revision B.

Really appreciate everyone's help on this.

 

[Tophan]

Forgot to mention...I'm using Access 2010

 

[Minty]

As discussed in the link I provided - if you include memo fields in a grouped query they will truncate.

Personally I would add a NoteID autonumber Primary Key to your drawing notes table. You can then use a simple subquery to include the last note id for any drawing in your initial grouped query data.
Then in your final report query simply link the drawing note back using that last ID.

 

[Tophan]

Thanks...that I understand how to do! I really wasn't understanding how to correct the problem from the notes provided yesterday.

Thank you so much for your help

 

[CJ_London]

I'm wondering why the query needs to be grouped in the first place.

Assuming the memo field is

Last([Copy Of QRYSmryDwgRegRpt].LastOfRevNotes) AS LastOfLastOfRevNotes

a)last only means last based on a defined order, but you are also grouping by

[Copy Of QRYSmryDwgRegRpt].DrawingID

which implies the lowest order of granularity (i.e. not grouped)

 

[Tophan]

The query is grouped because drawings can have several revisions throughout the course of a project. The detailed drawing register lists each revision and what the changes were where as the summary only shows the most current changes. When I was building the database, the only way to show the correct information was to group and show last drawing revision letter/number and last drawing revision notes. I couldn't find another way to display only the most recent information.

 

[Tophan]

Hi again.

I managed to fix the truncation error and now all information is showing in both the report and the source query. The only problem I am having now is a parameter box that keeps popping up when I run the report that keeps referencing the memo control I was having the issue with in the first place. But it is not referencing the current control name, it is referencing what it was...before I turned off the grouping. The correct field is referenced in the report, I checked the source query, there are no hidden fields in the report...I just can't get the parameter box to go away.

Any suggestions what/where else to search?

 

[CJ_London]

in the query design, go to the sql view - do you see PARAMETERS.... as the first line of the query?

Alternatively click on the parameters option in the ribbon