Len Function

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

Answer 1

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

E.g.

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

Answer 2

ciao Mahnam,

if you are working in VBA :

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

you could concatenate the variable with vbnullstring constant,

otherwise for instance in a query:

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

HTH.

Ciao, Sandro.

Answer 3

Thank you Stefan will try this.

Answer 4

Thank you Sandro,

Working in only MS access

Answer 5

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 

[e-mail]

Thanks for your help in advance

Answer 6

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

Answer 7

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

Answer 8

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.

Answer 9

how do I send you the file

Answer 10

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.

Regards,

Celeste

---

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 [e-mail].