Formula returns error I need to assign value 0 to #Error

[haridharmajan]

In my form i have two fields say fld1 and fld2. fld1 have data like 1,2,3,8,20. now I need to calculate the length of fld1 and assign it to fld2 he in this example fld2 must have 5. I used

=Len([fld1])-Len(Replace([fld1],",",""))+1
to do that but when fld1 contains null value it returns an error in fld2 like "#Error". I need 0 in fld2 if theres no value in fld1


help me please to solve this

[Stewart Ross]

Hi. You can use the Nz function to return an empty string in place of the null value for fld1:

=Len(Nz([fld1]))-Len(Replace(Nz([fld1]),",",""))+1

This will resolve the immediate errors. However, it is better to guard against empty strings and nulls explicitly, and in the long run it would be simpler to replace the above with a user-defined function in VBA which you could call.

As I do not know the purpose of your expression I will leave it for the moment, but I would advise as a general principle that once expressions start to get complicated it is worth considering writing a custom function to return the value instead of trying to do so in an in-line expression.

By the way, the length of the string without the commas is 6, not 5, in the example you give ("1,2,3,8,20" -> "123820". Length 6.).

-Stewart

[missinglinq]

He doesn't really mean the "length" of the string as in Len(string) Stewart. He has a string made up of dates that a person has missed from work during a given month, i.e. "01, 11,14,15,16" and he wants a month's total of the number of days, in this case 5.

Using Nz() won't work here, because even if the field is null, you'll still get 1 as the number of absences. He'll need to check for null first, then act accordingly:

If Not IsNull([fld1]) Then
  MonthTotal = Len([fld1]) - Len(Replace([fld1], ",", "")) + 1
Else
  MonthTotal = 0
End If

Linq ;0)>

[ADezii]

He doesn't really mean the "length" of the string as in Len(string) Stewart. He has a string made up of dates that a person has missed from work during a given month, i.e. "01, 11,14,15,16" and he wants a month's total of the number of days, in this case 5.

Using Nz() won't work here, because even if the field is null, you'll still get 1 as the number of absences. He'll need to check for null first, then act accordingly:

If Not IsNull([fld1]) Then
  MonthTotal = Len([fld1]) - Len(Replace([fld1], ",", "")) + 1
Else
  MonthTotal = 0
End If

Linq ;0)>

Couldn't you use the following formula to get the number of Absenses providing [fld1] is not Null?

UBound(Split(Me![fld1], ",")) + 1)

[missinglinq]

You could if you removed the final right-handed parenthesis!

Linq ;0)>

[ADezii]

You could if you removed the final right-handed parenthesis!

Linq ;0)>

Just wanted to see if you were paying attention! (LOL).

[missinglinq]

That's what my trig teacher always said when we caught him out!

Linq ;0)>

[haridharmajan]

He doesn't really mean the "length" of the string as in Len(string) Stewart. He has a string made up of dates that a person has missed from work during a given month, i.e. "01, 11,14,15,16" and he wants a month's total of the number of days, in this case 5.

Using Nz() won't work here, because even if the field is null, you'll still get 1 as the number of absences. He'll need to check for null first, then act accordingly:

If Not IsNull([fld1]) Then
  MonthTotal = Len([fld1]) - Len(Replace([fld1], ",", "")) + 1
Else
  MonthTotal = 0
End If

Linq ;0)>

Hi Friend this also returns Error. note that I am using Access form control to evaluate and not using any Front end like VB VC++..... help me pls

[NeoPa]

Harid,

If you ignore certain posts then it will be harder to help you (ignoring the manners for now).

Is there a reason why the modified version (without the trailing closing parenthesis) of ADezii's code in post #4 will not work for you?

[haridharmajan]

Harid,

If you ignore certain posts then it will be harder to help you (ignoring the manners for now).

Is there a reason why the modified version (without the trailing closing parenthesis) of ADezii's code in post #4 will not work for you?

Hi NeoPad

Sorry to say that i didn't get that i don't know where it went wrong but I got what i wanted from Stewart Ross Inverness post ie the 1st post I just modified the code and added IIF()

=IIf(IsNull([fld1]),"0",(Len([fld1])-Len(Replace([fld1],",",""))+1))


this works fine for me
anyway thanx for all who provided me the information

[NeoPa]

Interesting.

Is what you're after actually a count of items as proposed (by MissingLinq in post #3) or is it actually the count of all digits? The solution you've gone for seems to be the latter.

IE. With the following data - which answer would you be looking for?
1,5,8,20,22,3,15

1. 7
2. 10

[haridharmajan]

Interesting.

Is what you're after actually a count of items as proposed (by MissingLinq in post #3) or is it actually the count of all digits? The solution you've gone for seems to be the latter.

IE. With the following data - which answer would you be looking for?
1,5,8,20,22,3,15

1. 7
2. 10

Hi NeoPa

Here with given example I am Looking for the Answer A ie 7
Actually my case is I have a Table Leave in which 12 months are there Now I need to add the no: of leaves takes by each person on each month ie if a person takes 4 leaves in January like the followin dates 02,15,16,29 . I need January's total and I need 4 as my answer. hope u understood my situation

[NeoPa]

I think you're missing the point somewhat Harid. I wasn't looking for a fuller description of your overall problem. I was trying to simplify the question, and get at least that part clear.

This thread is a perfect example of how much time and effort can be wasted when the question is not clear. Most of the posters (even Stewart I suspect from his comment) were focused on answering a different question than the one you intended.

Where Stewart got the answer right was simply in changing your existing code (which is quite a clever, if hard to read, way of counting the fields) to handle the situation where [Fld1] has a Null value. It's clear he still thought you were counting the digits rather than the number of separate numbers in the list.

Unfortunately, if [Fld1] can be Null, or even if it needs to be worked out in SQL code rather than in VBA, the clever solution that ADezii provided cannot work (unless embedded with some extra code in a public function).