none
Is there a textbox limit of number of characters. Query displays fine, but form with textbox does not RRS feed

  • Question

  • Hi MSDN

    I am unsure if I am missing something basic here, 

    In the query, using a formula, I assemble together a long string.

    But in the text box within a form, it seems to truncate.

    Is there anything I have overlooked.

    Thank you kindly

    The formula for interest in the query is:

    Dashboard: [Calls].[Job Number] & Chr(13) & Chr(10) & IIf([Calls].[Call Back]="Yes","Call Back" & Chr(13) & Chr(10),"") & [Customers].[Full Name] & Chr(13) & Chr(10) & IIf(IsNull([Customers].[Business Phone]),"",[Customers].[Business Phone] & Chr(13) & Chr(10)) & IIf(IsNull([Customers].[Home Phone]),"",[Customers].[Home Phone] & Chr(13) & Chr(10)) & IIf(IsNull([Customers].[Mobile Phone]),"",[Customers].[Mobile Phone] & Chr(13) & Chr(10)) & IIf([Calls].[Call Before]="Yes","Call: " & [Calls].[Call Before] & Chr(13) & Chr(10),"") & [Customers].[City] & Chr(13) & Chr(10) & [Category].[Category] & Chr(13) & Chr(10) & [Calls].[Status]


    Thank you in Advance

    Wednesday, August 1, 2018 5:02 AM

Answers

  • Is it worth raising this in useraccessvoice.com.  It is the second deficiency that an MVP / consultant has indicated towards an shortcoming in access.  

    Oh, there are lots of shortcomings in Access, some quite well-known.  This one, at least is not a bug so much it is undesirable behavior that is "by design".

    It's hard to say whether this particular issue is likely to be addressed by the Access product team if you put it up on UserVoice. I don't know whether the truncation is in some way integral to the internal operation of crosstab queries, or whether it's just due to some oversight.  Years ago, back before Access 2007, I'd have said the chances of getting non-destructive bugs in the JET database engine were very small, because the Access product team didn't "own" that exclusively.  But with the introduction of the Access Database Engine (called "ACE" for some reason) in Access 2007, the team took complete control of the database engine used by Access.

    All of which boils down to:  sure, feel free to post this issue on UserVoice, and see what reaction you get.  At least you'll have made sure they know about it.  Even if they acknowledge it, they may not prioritize it over other issues, but you never can tell.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 3, 2018 1:09 AM

All replies

  • I believe the limit is 255 characters depending on the Data Type of the table field.
    Wednesday, August 1, 2018 1:09 PM
  • Thank you Lawrence, 

    You are correct that is it is 255 characters upon the field, 

    so it must take this as the limit from one of the many fields that are added into this formula field.

    The field itself is a formula field in the query, and not an actual table field with a particular data type assigned to it.

    I am presuming this... if you have any feedback.

    Thank you.


    Thank you in Advance

    Thursday, August 2, 2018 1:07 AM
  • An Access text box can display up to 65,535 characters (as stated in the online help under "specifications"), so that isn't the cause of the truncation.  If the text box is bound to a field, it is limited to the size of that field, so if the field is short text, then the text box will be limited to 255 characters, while if the field is long text, 65,535 is smaller than the maximum size of a long text field, so that's the limit.

    When dealing with query results, though, there is often a problem where a calculated field may be construed as short text, rather than the long text you want.  Also, any field that you group by in the query will automatically be truncated to short text.  But your thread title suggests that if you open the form's recordsource query directly, as a datasheet, the field isn't truncated.  Is that true?  If so, I can't see why it would be truncated.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 2, 2018 4:26 AM
  • Hi Dirk, 

    Thank you for your explanation.  I reviewed the query, and sure enough, it was in the query itself, so nothing to do with the text box itself.

    So the first part of your explanation seems to be what is happening... is there any way to make it act as long text.

    "When dealing with query results, though, there is often a problem where a calculated field may be construed as short text, rather than the long text you want.  Also, any field that you group by in the query will automatically be truncated to short text.  "


    Thank you in Advance

    Thursday, August 2, 2018 6:25 AM
  • If you open the query "Dashboard View Start UPDATE - Employee" as a datasheet, are the calculated fields truncated?  I'm going to guess that they aren't, so it's the crosstab query that is truncating them. If so, I think you may have run into a limitation of crosstab queries. The only way I can think of to work around the problem is to eliminate the crosstab query by redesigning the queries and maybe the form, possibly by using a temporary table to hold intermediate values.  I don't understand your domain well enough to be more detailed than that, but I think that's how I'd approach it.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 2, 2018 3:59 PM
  • Hi Dirk, 

    Thank you kindly for your review.  You are correct, it is the crosstab query that is truncating them.

    Is it worth raising this in useraccessvoice.com.  It is the second deficiency that an MVP / consultant has indicated towards an shortcoming in access.  

    The other one was

    https://social.msdn.microsoft.com/Forums/en-US/94ee722d-fed5-4a98-b2d9-aa03bab08320/use-of-timevalue-on-datetime-field?forum=accessdev

    Please see below screen captures as observation of the truncation in the crosstab query.

    I have some luxury of reducing the string, in order to fit, however, it should be raised with access.

    Thank you kindly.


    Thank you in Advance

    Thursday, August 2, 2018 11:48 PM
  • Is it worth raising this in useraccessvoice.com.  It is the second deficiency that an MVP / consultant has indicated towards an shortcoming in access.  

    Oh, there are lots of shortcomings in Access, some quite well-known.  This one, at least is not a bug so much it is undesirable behavior that is "by design".

    It's hard to say whether this particular issue is likely to be addressed by the Access product team if you put it up on UserVoice. I don't know whether the truncation is in some way integral to the internal operation of crosstab queries, or whether it's just due to some oversight.  Years ago, back before Access 2007, I'd have said the chances of getting non-destructive bugs in the JET database engine were very small, because the Access product team didn't "own" that exclusively.  But with the introduction of the Access Database Engine (called "ACE" for some reason) in Access 2007, the team took complete control of the database engine used by Access.

    All of which boils down to:  sure, feel free to post this issue on UserVoice, and see what reaction you get.  At least you'll have made sure they know about it.  Even if they acknowledge it, they may not prioritize it over other issues, but you never can tell.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 3, 2018 1:09 AM
  • Thank you kindly for the feedback

    https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/35018062-field-limit-formula


    Thank you in Advance

    Friday, August 3, 2018 6:18 AM