conditional display of a form item - how please

[Pat McCann]

Hi All

Firstly, I'm no expert - but thanks to help from the group I have now
established a club membership database that can do most of what I
need. The bit I'm stuck at now is this:-

I have a numeric field that contains a 4 digit code. I want the form
that displays all fields to display the real meaning of the code
rather than the code itself. for example code 4000 is an adult 7day
member whilst code 4016 is an adult 5 day member etc. (there are about
8 codes). They are held in a table called 'Additional Data' in a field
called 'code' and are part of a two table record for each member.

I've tried to blag my way through and in the properties of the form
field for 'code' i tried to build one expression to test my logic
=IIf(

=4000,"adult 7 day",[code]) (on the basis that if it worked
I would nest 8 IIf statements to get all 8 codes)

However, all i get is #error in the form field when displayed. I've
tried variants of this expression but I'm obviously barking up the
wrong tree.

Question - Am I using the right approach with IIf? if so , what am I
doing wrong.
or
Is there a better way of doing this?, and if so, how please?

Thanks in anticipation
Pat McCann, Wakefield ,England.
Access '97

(btw - this field comes from an external system so I am stuck with
these numeric codes)

 

[Jim/Chris]

Try using the Dlookup function on the form

Jim

 

[Adrian Jansen]

You can also set up a query which includes both tables, joined on the 4
digit code, and get the text description as part of the recordset. Then
just create a textbox on the form to show the description. No code or
Dlookup at all. But you have to be a bit careful, or you might end up with
a non-updateable query.


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Try using the Dlookup function on the form

Jim

-----Original Message-----
Hi All

Firstly, I'm no expert - but thanks to help from the group I have now
established a club membership database that can do most of what I
need. The bit I'm stuck at now is this:-

I have a numeric field that contains a 4 digit code. I want the form
that displays all fields to display the real meaning of the code
rather than the code itself. for example code 4000 is an adult 7day
member whilst code 4016 is an adult 5 day member etc. (there are about
8 codes). They are held in a table called 'Additional Data' in a field
called 'code' and are part of a two table record for each member.

I've tried to blag my way through and in the properties of the form
field for 'code' i tried to build one expression to test my logic
=IIf(

=4000,"adult 7 day",[code]) (on the basis that if it worked
I would nest 8 IIf statements to get all 8 codes)

However, all i get is #error in the form field when displayed. I've
tried variants of this expression but I'm obviously barking up the
wrong tree.

Question - Am I using the right approach with IIf? if so , what am I
doing wrong.
or
Is there a better way of doing this?, and if so, how please?

Thanks in anticipation
Pat McCann, Wakefield ,England.
Access '97

(btw - this field comes from an external system so I am stuck with
these numeric codes)

.
[/QUOTE][/QUOTE]

 

[Pat McCann]

Thanks to Jim (and Adrian) for responding. After a bit of trial and
error I have use Dlookup as suggested and all works fine.

Many thanks

Pat

Try using the Dlookup function on the form

Jim

-----Original Message-----
Hi All

Firstly, I'm no expert - but thanks to help from the group I have now
established a club membership database that can do most of what I
need. The bit I'm stuck at now is this:-

I have a numeric field that contains a 4 digit code. I want the form
that displays all fields to display the real meaning of the code
rather than the code itself. for example code 4000 is an adult 7day
member whilst code 4016 is an adult 5 day member etc. (there are about
8 codes). They are held in a table called 'Additional Data' in a field
called 'code' and are part of a two table record for each member.

I've tried to blag my way through and in the properties of the form
field for 'code' i tried to build one expression to test my logic
=IIf(

=4000,"adult 7 day",[code]) (on the basis that if it worked
I would nest 8 IIf statements to get all 8 codes)

However, all i get is #error in the form field when displayed. I've
tried variants of this expression but I'm obviously barking up the
wrong tree.

Question - Am I using the right approach with IIf? if so , what am I
doing wrong.
or
Is there a better way of doing this?, and if so, how please?

Thanks in anticipation
Pat McCann, Wakefield ,England.
Access '97

(btw - this field comes from an external system so I am stuck with
these numeric codes)

.
[/QUOTE][/QUOTE]