Why are my IIf clauses not working?!

[Guest]

I don't understand this. As far as I can see, these should be working. I like
to make test databases every so often to test out some new things I will have
to do for an actual database my company needs, this way if I mess up, I mess
up with a test database and not the real one.

In my test database, I am trying to create a report and use several IIf
clauses. The test database is a list of people I know. Several of my IIf
clauses work, several don't.

The report is only reading from one table and the whole table is in the
record source for the report. Why are these not working?! Any help would be
greatly appreciated. I just cannot understand this.

Here is one that does work:

=IIf([Lname],[Lname],Null) & ", " & IIf([Fname],[Fname],Null) & " "

This one prints the persons last name if they had a last name entered, then
a comma, then their first name of they had one entered, then a space. If
either a first name or last name doesn't exist, it is left blank (Null).

Here are the ones that do not:

=IIf([YrsKnown] > 5,"Wow!",[YrsKnown])

This one should display "Wow!" if the person knew me more than 5 years, and
simply display the number of years they have known me if it is less. Also,
just to try it out, I also used =5 instead of >5 and that didn't work either.
All I get either way is #Error

=IIf([RelToPaul]="Cat","=^..^=",[RelToPaul])

In this one, if their relationship to me is cat, it should display that
little kitty emoticon, otherwise it should just display whatever was entered
as their relationship to me. That also only produces #Error.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[Guest]

Nevermind. I figured it out on my own. Because I was using the report wizard
and first entering all the data, it named the detail box the same as the
field. For example, the detail box in the one example was named "YrsKnown,"
so when I referenced "YrsKnown" Access thought it meant that, so it wouldn't
work. When I changed the name of that detail box to something else, it knew
that "YrsKnown" was referencing my table and not that very box.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

 

[MA]

I don't understand this. As far as I can see, these should be
working. I like to make test databases every so often to test out
some new things I will have to do for an actual database my company
needs, this way if I mess up, I mess up with a test database and not
the real one.

In my test database, I am trying to create a report and use several
IIf clauses. The test database is a list of people I know. Several of
my IIf clauses work, several don't.

The report is only reading from one table and the whole table is in
the record source for the report. Why are these not working?! Any
help would be greatly appreciated. I just cannot understand this.

Here is one that does work:

=IIf([Lname],[Lname],Null) & ", " & IIf([Fname],[Fname],Null) & " "

You can use this istruction but I think is better
(Lane +", ") & (Fname + " ")

This one prints the persons last name if they had a last name
entered, then a comma, then their first name of they had one entered,
then a space. If either a first name or last name doesn't exist, it
is left blank (Null).

Here are the ones that do not:

=IIf([YrsKnown] > 5,"Wow!",[YrsKnown])

This one should display "Wow!" if the person knew me more than 5
years, and simply display the number of years they have known me if
it is less. Also, just to try it out, I also used =5 instead of >5
and that didn't work either. All I get either way is #Error

it must work,
better can be
iif(nz(YrsKnown>5), 0, "wow!", [YrsKnow])

=IIf([RelToPaul]="Cat","=^..^=",[RelToPaul])

In this one, if their relationship to me is cat, it should display
that little kitty emoticon, otherwise it should just display whatever
was entered as their relationship to me. That also only produces
#Error.

The istruction looks correct, he problem can be in other place

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it

 

[Guest]

Cool. Thanks for the help. Just out of curiosity, in your example below:

iif(nz(YrsKnown>5), 0, "wow!", [YrsKnow])

What does nz mean? Also, why do you have a 0 in there?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

I don't understand this. As far as I can see, these should be
working. I like to make test databases every so often to test out
some new things I will have to do for an actual database my company
needs, this way if I mess up, I mess up with a test database and not
the real one.

In my test database, I am trying to create a report and use several
IIf clauses. The test database is a list of people I know. Several of
my IIf clauses work, several don't.

The report is only reading from one table and the whole table is in
the record source for the report. Why are these not working?! Any
help would be greatly appreciated. I just cannot understand this.

Here is one that does work:

=IIf([Lname],[Lname],Null) & ", " & IIf([Fname],[Fname],Null) & " "

You can use this istruction but I think is better
(Lane +", ") & (Fname + " ")

This one prints the persons last name if they had a last name
entered, then a comma, then their first name of they had one entered,
then a space. If either a first name or last name doesn't exist, it
is left blank (Null).

Here are the ones that do not:

=IIf([YrsKnown] > 5,"Wow!",[YrsKnown])

This one should display "Wow!" if the person knew me more than 5
years, and simply display the number of years they have known me if
it is less. Also, just to try it out, I also used =5 instead of >5
and that didn't work either. All I get either way is #Error

it must work,
better can be
iif(nz(YrsKnown>5), 0, "wow!", [YrsKnow])

=IIf([RelToPaul]="Cat","=^..^=",[RelToPaul])

In this one, if their relationship to me is cat, it should display
that little kitty emoticon, otherwise it should just display whatever
was entered as their relationship to me. That also only produces
#Error.

The istruction looks correct, he problem can be in other place

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it

 

[MA]

Cool.

In italian the sound "cool" looks "lucky"

Thanks for the help. Just out of curiosity, in your example
below:

iif(nz(YrsKnown>5), 0

here you need a )

nz mens Null is zero. Null if different by Zero

,"wow!", [YrsKnow])

What does nz mean? Also, why do you have a 0 in there?

I don't understand this. As far as I can see, these should be
working. I like to make test databases every so often to test out
some new things I will have to do for an actual database my company
needs, this way if I mess up, I mess up with a test database and not
the real one.

In my test database, I am trying to create a report and use several
IIf clauses. The test database is a list of people I know. Several
of my IIf clauses work, several don't.

The report is only reading from one table and the whole table is in
the record source for the report. Why are these not working?! Any
help would be greatly appreciated. I just cannot understand this.

Here is one that does work:

=IIf([Lname],[Lname],Null) & ", " & IIf([Fname],[Fname],Null) & " "

You can use this istruction but I think is better
(Lane +", ") & (Fname + " ")

This one prints the persons last name if they had a last name
entered, then a comma, then their first name of they had one
entered, then a space. If either a first name or last name doesn't
exist, it is left blank (Null).

Here are the ones that do not:

=IIf([YrsKnown] > 5,"Wow!",[YrsKnown])

This one should display "Wow!" if the person knew me more than 5
years, and simply display the number of years they have known me if
it is less. Also, just to try it out, I also used =5 instead of >5
and that didn't work either. All I get either way is #Error

it must work,
better can be
iif(nz(YrsKnown>5), 0, "wow!", [YrsKnow])

=IIf([RelToPaul]="Cat","=^..^=",[RelToPaul])

In this one, if their relationship to me is cat, it should display
that little kitty emoticon, otherwise it should just display
whatever was entered as their relationship to me. That also only
produces #Error.

The istruction looks correct, he problem can be in other place

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it