Dlookup in a report

[SamuelR]

I have a report in ms access where I use a query as it's source the problem i'm having is with the memo field being truncated at 255 character.
please note i utilse an expression on the memo field in the query

Rem: Max(IIf(IsNull([Pst_Remarks])=True,[Allow_code],IIf(IsNull([Allow_code])=True,[Pst_Remarks],[Allow_code] & " " & [Pst_Remarks])))

how can i make the report show all character

 

[dhookom]

Are all of these fields in your report's record source? You could create a user-defined function to replace the nested IIF()s.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SamuelR]

No the fields are in the query not in report. If I want to make changes to query access give me the run out memory error. So editing query is complex. So was wondering how to fix the limit given cause its a memo field but report see the expression as a text field

 

[dhookom]

Why not use:
=[Allow_code] & " " & [Pst_Remarks]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SamuelR]

Will try this code and see if it works.

below is the database link

https://drive.google.com/file/d/1Hs5LfprN1WZZbnRq5k4zil8n2I4zj3Ws/view?usp=share_link

the report giving the problem is CG-Establishment keep truncating the memo field

 

[dhookom]

I can't even open the CG-Establishment report in any mode.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SamuelR]

Okay that's weird when it opens
I know it will ask for different prompts also the query is the same name as report will upload a different copy tomorrow if problem still occur

 

[dhookom]

That is one complex query. I typically build the where clause dynamically with only the criteria the user requests. Also, the query is a totals query which will truncate long text to 255 characters. Did you check the query values for the two fields?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SamuelR]

So is it best to have a where query as the main and a sub query that will reference from the criteria chosen by the user trying to figure what's best so it won't truncate the pstremark

 

[dhookom]

I'm not even sure how to answer that without knowing a lot more about application and specifications. From the table structures and lack of relationships etc, I'm not sure I would want to open myself up to that.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SamuelR]

Oh I did over the query without the where clause and just utilize the first function and it gave me everything thus far, time now to put in back all those where clause

 

[dhookom]

The where clause is a complex affair. As I stated earlier, I like to build a where clause based on whether or not a control on a form is relevant or not. I think someone has a powerful where clause/filter builder in one of the FAQs in Tek-Tips.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SamuelR]

Duly Noted, I made the change in the report and it work problem lies now due to the fact I've inserted a field that's not part of the query grouping is off.
So control source groups the data but now the report shows the remarks separate once I group multiple records.

 

[dhookom]

Grouping in the query will limit the number of
Characters in any field to 256. You could possibly use a subreport if there is some unique field combination to link master/child.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016