By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

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

P: 8
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
Aug 2 '08 #1
Share this Question
Share on Google+
12 Replies


Expert Mod 2.5K+
P: 2,545
Hi. You can use the Nz function to return an empty string in place of the null value for fld1:
Expand|Select|Wrap|Line Numbers
  1. =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
Aug 2 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull([fld1]) Then
  2.   MonthTotal = Len([fld1]) - Len(Replace([fld1], ",", "")) + 1
  3. Else
  4.   MonthTotal = 0
  5. End If
Linq ;0)>
Aug 2 '08 #3

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull([fld1]) Then
  2.   MonthTotal = Len([fld1]) - Len(Replace([fld1], ",", "")) + 1
  3. Else
  4.   MonthTotal = 0
  5. End If
Linq ;0)>
Couldn't you use the following formula to get the number of Absenses providing [fld1] is not Null?
Expand|Select|Wrap|Line Numbers
  1. UBound(Split(Me![fld1], ",")) + 1)
Aug 2 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
You could if you removed the final right-handed parenthesis!

Linq ;0)>
Aug 2 '08 #5

ADezii
Expert 5K+
P: 8,597
You could if you removed the final right-handed parenthesis!

Linq ;0)>
Just wanted to see if you were paying attention! (LOL).
Aug 2 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
That's what my trig teacher always said when we caught him out!

Linq ;0)>
Aug 2 '08 #7

P: 8
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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull([fld1]) Then
  2.   MonthTotal = Len([fld1]) - Len(Replace([fld1], ",", "")) + 1
  3. Else
  4.   MonthTotal = 0
  5. 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
Aug 4 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
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?
Aug 4 '08 #9

P: 8
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
Aug 4 '08 #10

NeoPa
Expert Mod 15k+
P: 31,186
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
Aug 4 '08 #11

P: 8
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
Aug 5 '08 #12

NeoPa
Expert Mod 15k+
P: 31,186
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).
Aug 5 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.