INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Query not pulling through all field characters limited to 255

Access Query not pulling through all field characters limited to 255

(OP)
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 smile

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.


CODE --> SELECT

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 

CODE --> FROM

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 

CODE --> GROUPBY

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 

CODE --> HAVING

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

RE: Access Query not pulling through all field characters limited to 255

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

RE: Access Query not pulling through all field characters limited to 255

(OP)
Thanks I will try it out bigsmile 2thumbsup

Thank you,

Kind regards

Triacona

RE: Access Query not pulling through all field characters limited to 255

(OP)
Thanks for your help smile
Thanks again2thumbsup

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.

CODE --> SELECT

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 

CODE --> FROM

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 

CODE --> WHERE

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

RE: Access Query not pulling through all field characters limited to 255

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

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close