What's wrong with this IIF statement ?

[Guest]

I have a database field that stores numbers 1,2 and 3 representing different
values. In the form, I try to display the text value instead of the stored
numeric value. The statement is as follows:

=IIF([Type]=1,"Regular",IIF([Type]=2,"LumpSum","Special"))

but this won't work. Anyone know what's wrong with the above statement, I
followed the format from a textbook.

Thanks a lot.

 

[Guest]

Here is a more robust approach to the root problem:

Make a PaymentType table like this:

PaymentTypeID (integer) PaymentType
1 Regular
2 Lump Sum
3 Special

Set up the relationship between the transactional table and this table on
the PaymentTypeID field. Now, instead of using an IIF, you can just look up
the PaymentType directly from the PaymentType table

=Dlookup("[PaymentType]","[PaymentType]","[PaymentTypeID] = " & [Type])

Better yet, just make the type field have two columns, widths 0,1, bound
Column 1, Column1 being bound to the numeric Type field in your transactional
table, Column 2 looking up the corresponding PaymentType (text). This way,
the user can just select by the text description, but the number is what gets
saved.

 

[Guest]

Thanks very much for your advice Brian, but I was just wondering why that
statement won't work. Every reference book I have checked showed the same
statement format.

Here is a more robust approach to the root problem:

Make a PaymentType table like this:

PaymentTypeID (integer) PaymentType
1 Regular
2 Lump Sum
3 Special

Set up the relationship between the transactional table and this table on
the PaymentTypeID field. Now, instead of using an IIF, you can just look up
the PaymentType directly from the PaymentType table

=Dlookup("[PaymentType]","[PaymentType]","[PaymentTypeID] = " & [Type])

Better yet, just make the type field have two columns, widths 0,1, bound
Column 1, Column1 being bound to the numeric Type field in your transactional
table, Column 2 looking up the corresponding PaymentType (text). This way,
the user can just select by the text description, but the number is what gets
saved.

I have a database field that stores numbers 1,2 and 3 representing different
values. In the form, I try to display the text value instead of the stored
numeric value. The statement is as follows:

=IIF([Type]=1,"Regular",IIF([Type]=2,"LumpSum","Special"))

but this won't work. Anyone know what's wrong with the above statement, I
followed the format from a textbook.

Thanks a lot.

 

[TC]

Albert, it doesn't help to say that something "doesn't work", unless
you say /how/ it does not work. Does it return no records? Or the wrong
record? Does it fail with an error? If so, what is the message?

Here are some tips for how to your questions effectively:
http://tc2.atspace.com/0012-UsingUsenet.htm#PostSubject

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Sorry I didn't answer that the first time - the syntax looks correct to me.
Are you sure there is a control on your form called "Type" and that it has a
numeric value (not a number stored as text), because this is where the IIF
will look for the value.

Also, you might want to stay away from using "Type" as a field name because
it the name of a property of several different object types in Access.

Thanks very much for your advice Brian, but I was just wondering why that
statement won't work. Every reference book I have checked showed the same
statement format.

Here is a more robust approach to the root problem:

Make a PaymentType table like this:

PaymentTypeID (integer) PaymentType
1 Regular
2 Lump Sum
3 Special

Set up the relationship between the transactional table and this table on
the PaymentTypeID field. Now, instead of using an IIF, you can just look up
the PaymentType directly from the PaymentType table

=Dlookup("[PaymentType]","[PaymentType]","[PaymentTypeID] = " & [Type])

Better yet, just make the type field have two columns, widths 0,1, bound
Column 1, Column1 being bound to the numeric Type field in your transactional
table, Column 2 looking up the corresponding PaymentType (text). This way,
the user can just select by the text description, but the number is what gets
saved.

I have a database field that stores numbers 1,2 and 3 representing different
values. In the form, I try to display the text value instead of the stored
numeric value. The statement is as follows:

=IIF([Type]=1,"Regular",IIF([Type]=2,"LumpSum","Special"))

but this won't work. Anyone know what's wrong with the above statement, I
followed the format from a textbook.

Thanks a lot.

 

[Guest]

Yes Brian, there is a control on the form, and if I remove the IIF statement,
it will display the value corretly. By the way, this field was entered in an
input form which uses an option group where you select 1 of the 3 options,
and will store the value 1, 2 or 3 in the table. Is this causing the problem?

Sorry I didn't answer that the first time - the syntax looks correct to me.
Are you sure there is a control on your form called "Type" and that it has a
numeric value (not a number stored as text), because this is where the IIF
will look for the value.

Also, you might want to stay away from using "Type" as a field name because
it the name of a property of several different object types in Access.

Thanks very much for your advice Brian, but I was just wondering why that
statement won't work. Every reference book I have checked showed the same
statement format.

Here is a more robust approach to the root problem:

Make a PaymentType table like this:

PaymentTypeID (integer) PaymentType
1 Regular
2 Lump Sum
3 Special

Set up the relationship between the transactional table and this table on
the PaymentTypeID field. Now, instead of using an IIF, you can just look up
the PaymentType directly from the PaymentType table

=Dlookup("[PaymentType]","[PaymentType]","[PaymentTypeID] = " & [Type])

Better yet, just make the type field have two columns, widths 0,1, bound
Column 1, Column1 being bound to the numeric Type field in your transactional
table, Column 2 looking up the corresponding PaymentType (text). This way,
the user can just select by the text description, but the number is what gets
saved.


:

I have a database field that stores numbers 1,2 and 3 representing different
values. In the form, I try to display the text value instead of the stored
numeric value. The statement is as follows:

=IIF([Type]=1,"Regular",IIF([Type]=2,"LumpSum","Special"))

but this won't work. Anyone know what's wrong with the above statement, I
followed the format from a textbook.

Thanks a lot.

 

[Guest]

Let me clarify: The Iif must refer not just to the contents of a field in the
table, but more specifically to the contents of a control on the form, and it
cannot refer to itself.

In your example, there must be two controls on the form: the control named
"Type" and the control containing the IIf statement. If you put the Iif
inside the Type control, then you have created a circular-reference or
recursion-type error because it tries to calculate its own value, then
recalculate on the recalculated value, etc.

You can have an invisible control called Type, bound to the Type field, then
have your IIf statement referring to the Type control in a visible text box
called TypeDescription. I would do this if it is just a lookup form; if it is
an input form, my prior suggestion will be more appropriate.

Yes Brian, there is a control on the form, and if I remove the IIF statement,
it will display the value corretly. By the way, this field was entered in an
input form which uses an option group where you select 1 of the 3 options,
and will store the value 1, 2 or 3 in the table. Is this causing the problem?

Sorry I didn't answer that the first time - the syntax looks correct to me.
Are you sure there is a control on your form called "Type" and that it has a
numeric value (not a number stored as text), because this is where the IIF
will look for the value.

Also, you might want to stay away from using "Type" as a field name because
it the name of a property of several different object types in Access.

Thanks very much for your advice Brian, but I was just wondering why that
statement won't work. Every reference book I have checked showed the same
statement format.


:

Here is a more robust approach to the root problem:

Make a PaymentType table like this:

PaymentTypeID (integer) PaymentType
1 Regular
2 Lump Sum
3 Special

Set up the relationship between the transactional table and this table on
the PaymentTypeID field. Now, instead of using an IIF, you can just look up
the PaymentType directly from the PaymentType table

=Dlookup("[PaymentType]","[PaymentType]","[PaymentTypeID] = " & [Type])

Better yet, just make the type field have two columns, widths 0,1, bound
Column 1, Column1 being bound to the numeric Type field in your transactional
table, Column 2 looking up the corresponding PaymentType (text). This way,
the user can just select by the text description, but the number is what gets
saved.


:

I have a database field that stores numbers 1,2 and 3 representing different
values. In the form, I try to display the text value instead of the stored
numeric value. The statement is as follows:

=IIF([Type]=1,"Regular",IIF([Type]=2,"LumpSum","Special"))

but this won't work. Anyone know what's wrong with the above statement, I
followed the format from a textbook.

Thanks a lot.

 

[Guest]

Thank you Brian, it's clearly understood now. It's very nice of you, cheers.

Let me clarify: The Iif must refer not just to the contents of a field in the
table, but more specifically to the contents of a control on the form, and it
cannot refer to itself.

In your example, there must be two controls on the form: the control named
"Type" and the control containing the IIf statement. If you put the Iif
inside the Type control, then you have created a circular-reference or
recursion-type error because it tries to calculate its own value, then
recalculate on the recalculated value, etc.

You can have an invisible control called Type, bound to the Type field, then
have your IIf statement referring to the Type control in a visible text box
called TypeDescription. I would do this if it is just a lookup form; if it is
an input form, my prior suggestion will be more appropriate.

Yes Brian, there is a control on the form, and if I remove the IIF statement,
it will display the value corretly. By the way, this field was entered in an
input form which uses an option group where you select 1 of the 3 options,
and will store the value 1, 2 or 3 in the table. Is this causing the problem?

Sorry I didn't answer that the first time - the syntax looks correct to me.
Are you sure there is a control on your form called "Type" and that it has a
numeric value (not a number stored as text), because this is where the IIF
will look for the value.

Also, you might want to stay away from using "Type" as a field name because
it the name of a property of several different object types in Access.

:

Thanks very much for your advice Brian, but I was just wondering why that
statement won't work. Every reference book I have checked showed the same
statement format.


:

Here is a more robust approach to the root problem:

Make a PaymentType table like this:

PaymentTypeID (integer) PaymentType
1 Regular
2 Lump Sum
3 Special

Set up the relationship between the transactional table and this table on
the PaymentTypeID field. Now, instead of using an IIF, you can just look up
the PaymentType directly from the PaymentType table

=Dlookup("[PaymentType]","[PaymentType]","[PaymentTypeID] = " & [Type])

Better yet, just make the type field have two columns, widths 0,1, bound
Column 1, Column1 being bound to the numeric Type field in your transactional
table, Column 2 looking up the corresponding PaymentType (text). This way,
the user can just select by the text description, but the number is what gets
saved.


:

I have a database field that stores numbers 1,2 and 3 representing different
values. In the form, I try to display the text value instead of the stored
numeric value. The statement is as follows:

=IIF([Type]=1,"Regular",IIF([Type]=2,"LumpSum","Special"))

but this won't work. Anyone know what's wrong with the above statement, I
followed the format from a textbook.

Thanks a lot.