IIf Statement

[Gambler]

When I type these iif statement into my reports format EXPRESSION, I get an error message YOU MAY HAVE ENTERED A COMMA W/O PRECEDING VALUE OR IDENTIFIER.
Here is my IIF Statement.
=iif(abs([ndist]-[npp1dis])>1.5,"",[npp1dis])

This one also gives me the same error message.
=iif([tsurf]="T",[nped],"")
Any help would be appreciated.

Thanks
ed

[Rick]

I use them in queries.

They look good to me except for the = at the beginning of each.

[Rick]

I just looked at something on reports and see that the = is required in reports.

You could always do it in a query first.

[Gambler]

When I put them in a query, I assume I put the formula in the design view field. But how would I get that query field into a report field formats expression?
Thanks for the help
ed

[Rick]

This is what I would try doing.

NewFieldName: iif(abs([ndist]-[npp1dis])>1.5,"",[npp1dis])

I am the hands on type, I just start playing with it until I get it to work. Sometimes I don't get the results I want and have to reverse the order to get it to work.

[AwolAtHTR]

Tue 19 Apr 05

hey Gambler,

here is a hunch

next time you get error change the "" to " ", ie insert a BLANK

this is the old issue of NULL or EMPTY
sometimes the double quotes yields NULL and other times EMPTY

I have resolved some problems by changing the field default value
from Not Set (maybe NULL but should be EMPTY)
to the 'empty' double quotes
but another problem needed the BLANK to get VAL(-field-) to work
when the query ran and found data that had not been set

aah, SOMETIMES, a table default value change got the query to work.

Maybe Donnie could tell us what and where the difference is
between NULL and EMPTY?


good luck with your search for the 'right' formula

AWOL

[fred4now]

not sure what abs is, but if you are creating text boxes to show something your expression needs to be in the "control source" not the "format"

Here is an example of K>=100 or pdiff <=4 it will show an *

=IIf([nkrat]>=100 Or [npdif]<=4,"*","")

[MikeDee]

Ed

I didn't try it myself but try putting it in your query that you are using for your report.

You need to give it a name for example xDIST

so int the query design put

xDIST: iif(abs([ndist]-[npp1dis])>1.5,"",[npp1dis])

After you save the query. Open your report in the design view and the field xDIST should be available to you to put in your report.

[Gambler]

Thanks for all the help. I will see if I can get it to work now.
ABS is absolute value. It sees all numbers as positive.
thanks again
ed

[Gambler]

I put xDIST:iif(abs([ndist]-[npp1dis])>1.5,"",[npp1dis]) ina field in my query. When i tried to save it, it said ndist was in more than one table. This is true. So I put ALL-HX4 In that fields table when I tried to save it, the ALL-HX4 in that query field was removed. So I put PL4 in its place because ndist also appears in PL4. Now I got the message I had to many ( parentheses. I tried to change the ( location, but no help.
I also let a space between""--No help.

Any more suggestions would be appreciated. I thought it would work in a query. Maybe Im identifing the table ndist is coming from wrong. I sure dont know.
Thanks for all the help
ed

[Rick]

What tables are you using.

I will see what I can do with it.

[Gambler]

My report comes from a query that uses the tables ALL_HX4, ALL_HX4, and several queries.
Thanks for helping
ed

[Rick]

Two ALL_HX4 tables?

Those fields come from the tables, not the queries?

Except the one you are trying to create?

[Rick]

xDIST: IIf(Abs([all_hx4].[ndist]-[npp1dis])>1.5,"",[npp1dis])

Works for me.

[Gambler]

I didnt get any error messages this time, but I didnt get any output for xdist. I changed all_hx4 to all_hx5 because those fields came from hx5. I checked in my query and the [ndist] and [npp1dis] have numbers in them. Could my formula be wrong?
Thanks for helping
ed

[Rick]

I got results in my query. I don't know if the results I got would be what you wanted but it would be something to start with.

The one I posted as saying it worked from me was copies straight from the query.

I only had the HX4 and HX5 tables in my query.

If you aren't getting anything I would check the fields in the tables and make sure they are defined correctly.

Worse case, if you want me to take a look at it, make a copy of your database, delete all the records in the copy and do a Compact and repair. Then you can email that to me and I will take a look at it

ricks@homebased2.com

[MikeDee]

I would put the table names in the equation to eliminate any errors like so


xDIST: IIf(Abs([all_hx4]![ndist]-[all_hx5]![npp1dis])>1.5,"",[all_hx5]![npp1dis])

[Gambler]

Thanks Mike I will try that.
ed

[Gambler]

It worked!!!!!!!!!!!!!!!!!!!!!!!!! I was getting ready to try and figure out how I could send my report to Rick. I will go over it thoroughly and see if all the numbers are correct. At least I got an output. I will let you know if is what I wanted.
Thanks again to both Mike AND Rick
ed

[Gambler]

ALL IS GREAT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!
THANKS A MILLION
ED