Results 1 to 7 of 7
  1. #1
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212

    Form field sometimes calculates correctly and sometimes says #size

    Has anyone experienced this? Form field sometimes calculates correctly and sometimes says #size. I'm using Access 16. Windows 10. I'm testing the field to see if I can improve the syntax, but if I change something, and it fixes it. The next moment I open it and its #size again. Appreciate any help.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If it's hit and miss, it might be due to Nulls in one or more fields. You should post the expression for the calculated control so we can see if you're summing or concatenating and how.
    EDIT - this isn't on a subform is it?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    When I got the intermittent results, I was using the same data, over and over.
    Below is the control source in a field on the form.
    Code:
    text120=DLookUp("TotalJBorrowerpaid","QCDPage2BReport","Page2id=" & [Forms]![closeform]![titleid])
    Below is building on that code to calculate another field.
    Code:
    =-(Nz([text120])+Nz([Lendercredits]))
    Thanks for response and help.

  4. #4
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    two more examples - this gives me a #size error
    Code:
    =IIf(Nz([tenminusincrease])>0,Nz([tenminusincrease]),0)
    This gives me a #type error
    Code:
    =(CCur(DLookUp("TotalDLoanEstimatetolerance","QCDPage2BReport","Page2id=" & [Forms]![closeform]![titleid])))

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Start by fixing your use of Nz. When you don't provide the value if null the result can be a variant or IIRC, an empty string - either of which seems likely to cause a #size error. Research Nz.

  6. #6
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    Quote Originally Posted by Micron View Post
    Start by fixing your use of Nz. When you don't provide the value if null the result can be a variant or IIRC, an empty string - either of which seems likely to cause a #size error. Research Nz.
    I think it more likely that the form or form fields are corrupted in some way. For the last ten months, Windows updates, have been corrupting records in Access (Windows 10, Access 365 or 16) and I have to help my customers fix a corrupt record everytime they do a Windows Update on their system. I have tried to research Nz, and can find nothing which talks about using Nz in form fields. I have to say that I use Nz extensively in my code which has been used by customers for the last 25 years, and cannot remember having to us IsNull. I believe that nz is a function now available in Access. We had at one time, written our own version of Nz. How does my system know which one to use?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    cannot remember having to us IsNull
    If that means you think I suggested it, I didn't. If you've been using Nz as posted here then it's been that long that you've been allowing it to decide what to return. I don't know what you mean by your own version of Nz - a function by the same name? Can't imagine that code would compile.

    I stand by my original suggestion because =Nz(someField) without the parameter returns either a variant (Null) or an empty string. Nulls cannot be summed (+) and a string is invalid in a numeric field. It's no surprise to me that you have such errors.

    In the end, it may prove to be something else, or even a combination of 2 or more things. It's easy to assume that if you fix one and the issue remains, then fix another and it's fixed, that it was the 2nd thing that was the problem. Quite possibly they worked in tandem. You can always upload a db copy if you think it will help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-20-2018, 03:40 PM
  2. Replies: 7
    Last Post: 09-15-2017, 05:44 PM
  3. Replies: 6
    Last Post: 10-06-2016, 02:17 AM
  4. Replies: 15
    Last Post: 12-10-2012, 06:37 PM
  5. Replies: 6
    Last Post: 10-21-2011, 10:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums