Len Function RRS feed

  • Question

  • I would like to add the value of len function from 13 different fields.

    The filed which has a null value does not give any answer then the remaining come blank

    I am trying to create a marksheet

    Tuesday, June 20, 2017 9:22 AM

All replies

  • You need to handle NULL values, cause otherwise it will propagate. Use the Nz() function for each column which is nullable.


    TotalLen: Len(NonNullableStringColumn1) + 
      Len(NonNullableStringColumn2) + 
      Len(Nz(NullableStringColumn3, "")) + 
      Len(Nz(NullableStringColumn4, ""))

    Tuesday, June 20, 2017 9:31 AM
  • ciao Mahnam,

    if you are working in VBA :

    a="pippo": b="2":c=null
    ? len(a & vbnullstring) +len(b & vbnullstring) + len(c & vbnullstring)
    ? len(c & vbnullstring)
    ? len(c)

    you could concatenate the variable with vbnullstring constant,

    otherwise for instance in a query:

    totLength: Len([stringa1] & "")+Len([stringa2] & "")+Len([stringa3] & "")


    Ciao, Sandro.

    Tuesday, June 20, 2017 11:08 AM
  • Thank you Stefan will try this.
    Tuesday, June 20, 2017 12:01 PM
  • Thank you Sandro,

    Working in only MS access

    Tuesday, June 20, 2017 12:01 PM
  • Dear Stefan,

    The function you have sent is not working.

    I would love to pick your brain for this if its ok with you. 

    If its ok you can send me your mail address where i can send you the file I have prepared. or you can mail me on 


    Thanks for your help in advance

    Tuesday, June 20, 2017 12:09 PM
  • ciao Mahnam,

    the first example I showed is taken from debug window, Access' debug window no elseWhere.

    Anyway, you can public an example on OneDrive or DropBox or on any other file sharing program.

    awaiting your link :-)

    Ciao, Sandro

    Tuesday, June 20, 2017 5:09 PM
  • Try this --

    TotalLen: Nz(NameCol_1, 0) + Nz(NameCol_2, 0) + Nz(NameCol_3, 0) +  .... Nz(NameCol_13, 0) 

    Build a little, test a little

    Wednesday, June 21, 2017 3:42 AM
  • It works.

    It's basically the expression you can enter in the query designer. Just check whether you need semicolons instead of commas.

    Just describe your table structure, pictures can help also.

    Wednesday, June 21, 2017 7:50 AM
  • how do I send you the file
    Friday, June 23, 2017 6:26 AM
  • Hello,

    I suggest you post your issue here and many community member would be involved to help you.

    If you don't get it work with Nz function. What code do you use now and what error do you get?

    I suggest you share some sample data or screenshots here to help clarify your situation.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 23, 2017 8:51 AM