Access Query not pulling through all field characters limited to 255 1

[Triacona]

Dear All,

I have a problem with my Access query.

Please see code below.

The problem is that the original table UNI7LIVE_DCAPPL has a field called PROPOSAL.
When you open the original table the entire contents of this field displays > 255 characters.
But when running a query off this table it is limited to 255 characters (<= 255)
Is there a way to bypass this, so all the data in the field is displayed?
Please help, I have been on this for quite some time and seem to be getting no-where.
Thanks

After more attempts still no success...but have managed to find this out so far I hope it helps...
If I run the query just using the main table UNI7LIVE_DCAPPL and no joining tables it also outputs the entire field.

SELECT 
UNI7LIVE_DCAPPL.REFVAL, 
UNI7LIVE_DCAPPL.DATEAPRECV AS DateReceived, 
UNI7LIVE_DCAPPL.DATEWKLIST AS WeeklyListDate, 
UNI7LIVE_DCAPPL.DATEAPVAL AS DateValid, 
UNI7LIVE_DCAPPL.DCAPPTYP AS AppType, 
DcAppTypeDsc.CODETEXT AS DcAppTypDes, 
UNI7LIVE_DCAPPL.DCSTAT AS Status, 
UNI7LIVE_DCAPPL.DECSN AS Decision, 
UNI7LIVE_DCAPPL.DATEDECISN AS DecDate, 
UNI7LIVE_DCAPPL.DATEDECISS AS DateDecIss, 
UNI7LIVE_DCAPPL.DTYPNUMBCO AS [Dev Type], 
UNI7LIVE_DCAPPL.PACLOSEDD, 
UNI7LIVE_DCAPPL.OFFCODE, 
UNI7LIVE_CNOFFICER.NAME AS Officer, 
OneLineAddress([ADDRESS]) AS Addr, 
UNI7LIVE_DCAPPL.DATE8WEEK AS TargetDate, 
UNI7LIVE_DCAPPL.WARD, 
UNI7LIVE_CNWARD.WARDNAME, 
UNI7LIVE_DCAPPL.APPNAME, 
UNI7LIVE_DCAPPL.PROPOSAL, 
DcDecType.LISTNAME AS DecListN, 
DcDecType.CODEVALUE AS DecTypCL, 
UNI7LIVE_DCAPPL.DECTYPE AS DecisionType, 
DcDecType.CODETEXT AS DecTypeTxt

FROM (((
UNI7LIVE_DCAPPL LEFT JOIN UNI7LIVE_CNOFFICER ON UNI7LIVE_DCAPPL.OFFCODE = UNI7LIVE_CNOFFICER.OFFCODE) 
LEFT JOIN DcAppTypeDsc ON UNI7LIVE_DCAPPL.DCAPPTYP = DcAppTypeDsc.CODEVALUE) 
LEFT JOIN UNI7LIVE_CNWARD ON UNI7LIVE_DCAPPL.WARD = UNI7LIVE_CNWARD.WARD) 
LEFT JOIN DcDecType ON UNI7LIVE_DCAPPL.DECTYPE = DcDecType.LISTNAME

GROUP BY 
UNI7LIVE_DCAPPL.REFVAL, 
UNI7LIVE_DCAPPL.DATEAPRECV, 
UNI7LIVE_DCAPPL.DATEWKLIST, 
UNI7LIVE_DCAPPL.DATEAPVAL, 
UNI7LIVE_DCAPPL.DCAPPTYP, 
DcAppTypeDsc.CODETEXT, 
UNI7LIVE_DCAPPL.DCSTAT, 
UNI7LIVE_DCAPPL.DECSN, 
UNI7LIVE_DCAPPL.DATEDECISN, 
UNI7LIVE_DCAPPL.DATEDECISS, 
UNI7LIVE_DCAPPL.DTYPNUMBCO, 
UNI7LIVE_DCAPPL.PACLOSEDD, 
UNI7LIVE_DCAPPL.OFFCODE, 
UNI7LIVE_CNOFFICER.NAME, 
OneLineAddress([ADDRESS]), 
UNI7LIVE_DCAPPL.DATE8WEEK, 
UNI7LIVE_DCAPPL.WARD, 
UNI7LIVE_CNWARD.WARDNAME, 
UNI7LIVE_DCAPPL.APPNAME, 
UNI7LIVE_DCAPPL.PROPOSAL, 
DcDecType.LISTNAME, 
DcDecType.CODEVALUE, 
UNI7LIVE_DCAPPL.DECTYPE, 
DcDecType.CODETEXT

HAVING (((
UNI7LIVE_DCAPPL.DATEWKLIST) Between [forms]![MainScreen]![txtStartDate] And [forms]![MainScreen]![txtEndDate]) 
AND ((UNI7LIVE_DCAPPL.DATEAPVAL) Is Not Null) 
AND ((UNI7LIVE_DCAPPL.DCAPPTYP) Not In ("COMP","DROPKB","PREAPP","PREAPO","PREAPH","PD","AMEND","PDENQ","DUMMY","COND","INF","MCP")) 
AND ((UNI7LIVE_DCAPPL.DECSN) Is Null) 
AND ((UNI7LIVE_DCAPPL.DATEDECISS) Is Null))
ORDER BY 
UNI7LIVE_DCAPPL.DCAPPTYP, 
UNI7LIVE_CNOFFICER.NAME;

Thank you,

Kind regards

Triacona

 

[dhookom]

Remove Proposal from the group by phrase since this will truncate the results. Then try use some aggregate like First or Max on the Proposal field in the select phrase.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Thanks I will try it out

Thank you,

Kind regards

Triacona

 

[Triacona]

Thanks for your help
Thanks again

I have figured out the groupby has to be completely removed and sorting removed. You also, if generating a report from the query, have to remove sorting/grouping so the limiting isn't on the report as well.

Weird bug MS.

SELECT UNI7LIVE_DCAPPL.REFVAL, 
UNI7LIVE_DCAPPL.DATEWKLIST AS WeeklyListDate, 
UNI7LIVE_DCAPPL.DATEAPVAL AS DateValid, 
UNI7LIVE_DCAPPL.PROPOSAL AS Proposal, 
OneLineReplace([ADDRESS]) AS Addr, 
UNI7LIVE_DCAPPL.DCAPPTYP AS AppType, 
DcAppTypeDsc.CODETEXT AS DcAppTypDes, 
UNI7LIVE_DCAPPL.DCSTAT AS Status, 
UNI7LIVE_DCAPPL.DTYPNUMBCO AS [Dev Type], 
UNI7LIVE_DCAPPL.OFFCODE, 
UNI7LIVE_CNOFFICER.NAME AS Officer, 
UNI7LIVE_DCAPPL.WARD,
UNI7LIVE_CNWARD.WARDNAME, 
UNI7LIVE_DCAPPL.APPNAME, 
DcDecType.CODEVALUE AS DecTypCL, 
UNI7LIVE_DCAPPL.DECTYPE AS DecisionType, 
DcDecType.CODETEXT AS DecTypeTxt, 
UNI7LIVE_DCAPPL.DECSN AS Decision, 
UNI7LIVE_DCAPPL.DATEDECISN AS DecDate, 
UNI7LIVE_DCAPPL.DATEDECISS AS DateDecIss, 
UNI7LIVE_DCAPPL.DATE8WEEK AS TargetDate, 
UNI7LIVE_DCAPPL.PACLOSEDD, 
UNI7LIVE_DCAPPL.AGTNAME, 
OneLineReplace([AGTADDRESS]) AS AgntAddr

FROM 
(((
UNI7LIVE_DCAPPL LEFT JOIN UNI7LIVE_CNOFFICER ON UNI7LIVE_DCAPPL.OFFCODE = UNI7LIVE_CNOFFICER.OFFCODE) 
LEFT JOIN DcAppTypeDsc ON UNI7LIVE_DCAPPL.DCAPPTYP = DcAppTypeDsc.CODEVALUE) 
LEFT JOIN UNI7LIVE_CNWARD ON UNI7LIVE_DCAPPL.WARD = UNI7LIVE_CNWARD.WARD) 
LEFT JOIN DcDecType ON UNI7LIVE_DCAPPL.DECTYPE = DcDecType.LISTNAME

WHERE 
(((
UNI7LIVE_DCAPPL.DATEWKLIST) Between [forms]![MainScreen]![txtStartDate] And [forms]![MainScreen]![txtEndDate]) 
AND ((UNI7LIVE_DCAPPL.DATEAPVAL) Is Not Null) 
AND ((UNI7LIVE_DCAPPL.DCAPPTYP) Not In ("COMP","DROPKB","PREAPP","PREAPO","PREAPH","PD","AMEND","PDENQ","DUMMY","COND","INF","MCP")) 
AND ((UNI7LIVE_DCAPPL.DECSN) Is Null) 
AND ((UNI7LIVE_DCAPPL.DATEDECISS) Is Null
));

Thank you,

Kind regards

Triacona

 

[dhookom]

It's not a bug but a limitation when building group by queries.

Duane
Hook'D on Access
MS Access MVP