Problem with a IIF statement

[Genzo]

Hi

Right now im using a command =sum([X]) but if that doesn't return a value it gives back an error which ruins the whole form
I tried to put in a IIF statement, to check if it returns a value and if it doesn't just set the value to 0, but it doesn't quite work
here is what i have tries

= IIf([3]; =sum([3]); 0)

[alansidman]

Why don't you explain specifically what you are attempting to do? Is this formula in a form or in a query? Is 3 a field name? Your example is confusing and not clear enough to offer a valid solution. The syntax for an IIF statement is:

=IIF(Criteria, RESULT IF TRUE, RESULT IF FALSE)

Your second = sign is not needed.

Alan

[Genzo]

Im using a danish access version which makes it a bit harder, sorry

its in a form the [3] refers to a label that get its data through some database.
If i run the formula =sum([3]) and the label is empty i get an error message that looks like #Name? (translated)

So what i want that if the label happend to return #name? by using =sum([3]) instead it should say 0(zero) so its a value instead of a string?

[rpeare]

How does that formula work? you're not comparing the value in your label [3] to anything.

if you want to check to see if [3] is not null you would do something like:

=iif(not isnull([3]); sum(nz([3],0));0)

The NZ function allows you to replace a null value with whatever you want (the default is a 0 I think)

[Genzo]

How does that formula work? you're not comparing the value in your label [3] to anything.

if you want to check to see if [3] is not null you would do something like:

=iif(not isnull([3]); sum(nz([3],0));0)

The NZ function allows you to replace a null value with whatever you want (the default is a 0 I think)

Hi i got on the right track thanks to you, but it produces an error, although i was able to get something to work with

=IIf(IsNumeric([3]);Sum([3]);0)

but the problem is now, im only allowed it seems, to use it once per form, and i need it the formula at least 4 times in 4 sepperate textboxes
like so

=IIf(IsNumeric([1]);Sum([1]);0)
=IIf(IsNumeric([3]);Sum([3]);0)
=IIf(IsNumeric([5]);Sum([5]);0)
=IIf(IsNumeric([7]);Sum([7]);0)

if i have more then 1, they all fail

[Genzo]

Wouldn't it be better just do it in VBA? on form_load()

make a script that runs and sets the textboxes to what i need?

[rpeare]

Ok what exactly are you doing, I expect you are trying to perform a sum on a field that has null values so your sum is coming out as null.

If that's the case you would have

=sum(nz([3],0))

You can't check a single value in a range of values and perform a sum, you basically want to make *all* null values 0 then perform the sum

[Genzo]

as you can see under total i get a :fejl because i didn't remove the =IIf(IsNumeric([1]);Sum([1]);0)

as you can see the collumn 3 has a #navn because there is no data from the database, and i then do a =sum([3]) i get an error since i can't do that

the numers top are being joined together from 4 diffrent tabels, and sometimes a code just doesn't exist for a given month.


How do i make the textboxes return a 0 if there is a #navn in a collumn and the sum([x]) if there is a number

[rpeare]

The formula I gave you should work. Check the NAME of the calculation field, if it is named for an existing field in your database then you can also get the #name? error. If your summation of field [3] does have a name of a field in your table then just change it and you should get the sum.

[Genzo]

the command didn't work
=Sum(nz([3];0))

i get a :fejl which is :error i assume


I guess there is just no way to do that then

[RayMilhon]

I have had this type of error in my case it was caused by the Field name and the text box name being the same. I fixed it by going through each txtbox and making sure that they were different. for example if field name is = 3 then the textbox should be txt_3. Then use =Sum(nz([3];0)) or =Sum(nz([txt_3];0)) Assuming this is the problem either would work.