Conditional Display

[Allison]

Access 2003, Win XP

I would like to display a contact phone number for some contacts, and for
others I'd like to say "see x", where "x" is a related contact entry. Sort
of like in telephone books if you look up "movie theater" it tells you to
"see theaters".

My contacts table (tblContact) contains a field to identify it as part of
another group (GroupID). The group table (tblGroup) contains the GroupID and
other group details.

For example, contact "John Anderson" could be part of a group with GroupID
248 called "The Andersons", contact "Richard Bloom" could belong to no group,
and contact "ACME Dental" could be part of a group called "ACME".

So, I'd want to display:

NAME - CONTACT INFO
John Anderson - See "The Andersons"
Richard Bloom - contact info
ACME Dental - See "ACME"
ACME - contact info
The Andersons - contact info

What would be my conditional statement to make that happen?

Thank you for your help.

Allison

 

[Jeff Boyce]

Allison

You posted to a 'forms'-related newsgroup, so I'll assume you want to do
this in a form.

If this were mine, I'd do it in a query! You can use the IIF() statement in
a query to chose either contact info or "see X", then use that query as the
source for your form.

NOTE: you can use this same approach to 'feed' a report as well.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allison]

Thanks Jeff, but I knew that part. I need to know the syntax.

For example, I know how to write the statement to say "If the groupID is
filled, display [insert words here]", but I do not know how to write it in
order to populate [insert words here] with the variable information.

In other words, it will not always say "See June Bug", so I cannot write the
statement to say "If the GroupID is filled, display 'See June Bug' ". It has
to be something like, "If the GroupID is filled, display [name associated
with this specific GroupID in a different table]".

Allison

 

[Jeff Boyce]

Allison

Sorry if I repeated something you already knew ... I didn't know you knew...

Again, I'd use a query. In the query, I'd join the tables that contain the
specific information you want to use.

I'm assuming that one of your tables has a field that contains that
'alternate language' you want to use.

Because I can't see your raw data/tables, the following is just a rough idea
(untested) of what you'd add as a new field in your query:

YourNewContactInfoField:
IIF(Nz([GroupID],"")<>"",[YourAlternateLanguageField],[YourContactInfoField])

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, but I knew that part. I need to know the syntax.

For example, I know how to write the statement to say "If the groupID is
filled, display [insert words here]", but I do not know how to write it in
order to populate [insert words here] with the variable information.

In other words, it will not always say "See June Bug", so I cannot write
the
statement to say "If the GroupID is filled, display 'See June Bug' ". It
has
to be something like, "If the GroupID is filled, display [name associated
with this specific GroupID in a different table]".

Allison

Allison

You posted to a 'forms'-related newsgroup, so I'll assume you want to do
this in a form.

If this were mine, I'd do it in a query! You can use the IIF() statement
in
a query to chose either contact info or "see X", then use that query as
the
source for your form.

NOTE: you can use this same approach to 'feed' a report as well.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allison]

Jeff - sorry but I didn't give you everything you needed in order to help me.
My fault.

I have three tables: tblContact (holds contact detail), tblGroup (holds
group detail), and tblGroupContact (holds detail about which contacts belong
to which group). The first two are related one to many to the third.

Then my desired outcome needs to be "If the contact appears in the
GroupContact table, display the GroupName, else display the contact phone
number".

I tried setting a query but Access said there were ambiguous joins, so I had
to do it in two queries. First query gathered the ContactID, GroupID,
GroupTypeID, and GroupName. Second query took this query plus the tblContact
and tblGroupContact and I made a new field called Refer, thus:
Refer:IIf([GroupTypeID]="Contacts","See " & [GroupName],[tblClient].[Phone]).

Ain't pretty but it works. Is there a more-efficient way to do this?

Allison

Sorry if I repeated something you already knew ... I didn't know you knew...

Again, I'd use a query. In the query, I'd join the tables that contain the
specific information you want to use.

I'm assuming that one of your tables has a field that contains that
'alternate language' you want to use.

Because I can't see your raw data/tables, the following is just a rough idea
(untested) of what you'd add as a new field in your query:

YourNewContactInfoField:
IIF(Nz([GroupID],"")<>"",[YourAlternateLanguageField],[YourContactInfoField])

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, but I knew that part. I need to know the syntax.

For example, I know how to write the statement to say "If the groupID is
filled, display [insert words here]", but I do not know how to write it in
order to populate [insert words here] with the variable information.

In other words, it will not always say "See June Bug", so I cannot write
the
statement to say "If the GroupID is filled, display 'See June Bug' ". It
has
to be something like, "If the GroupID is filled, display [name associated
with this specific GroupID in a different table]".

Allison

Allison

You posted to a 'forms'-related newsgroup, so I'll assume you want to do
this in a form.

If this were mine, I'd do it in a query! You can use the IIF() statement
in
a query to chose either contact info or "see X", then use that query as
the
source for your form.

NOTE: you can use this same approach to 'feed' a report as well.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Access 2003, Win XP

I would like to display a contact phone number for some contacts, and
for
others I'd like to say "see x", where "x" is a related contact entry.
Sort
of like in telephone books if you look up "movie theater" it tells you
to
"see theaters".

My contacts table (tblContact) contains a field to identify it as part
of
another group (GroupID). The group table (tblGroup) contains the
GroupID
and
other group details.

For example, contact "John Anderson" could be part of a group with
GroupID
248 called "The Andersons", contact "Richard Bloom" could belong to no
group,
and contact "ACME Dental" could be part of a group called "ACME".

So, I'd want to display:

NAME - CONTACT INFO
John Anderson - See "The Andersons"
Richard Bloom - contact info
ACME Dental - See "ACME"
ACME - contact info
The Andersons - contact info

What would be my conditional statement to make that happen?

Thank you for your help.

Allison

 

[Jeff Boyce]

If, by "efficient", you mean "pretty", probably...

But I'm a big fan of "if it works, use it!"

Congratulations on finding a solution.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - sorry but I didn't give you everything you needed in order to help
me.
My fault.

I have three tables: tblContact (holds contact detail), tblGroup (holds
group detail), and tblGroupContact (holds detail about which contacts
belong
to which group). The first two are related one to many to the third.

Then my desired outcome needs to be "If the contact appears in the
GroupContact table, display the GroupName, else display the contact phone
number".

I tried setting a query but Access said there were ambiguous joins, so I
had
to do it in two queries. First query gathered the ContactID, GroupID,
GroupTypeID, and GroupName. Second query took this query plus the
tblContact
and tblGroupContact and I made a new field called Refer, thus:
Refer:IIf([GroupTypeID]="Contacts","See " &
[GroupName],[tblClient].[Phone]).

Ain't pretty but it works. Is there a more-efficient way to do this?

Allison

Sorry if I repeated something you already knew ... I didn't know you
knew...

Again, I'd use a query. In the query, I'd join the tables that contain
the
specific information you want to use.

I'm assuming that one of your tables has a field that contains that
'alternate language' you want to use.

Because I can't see your raw data/tables, the following is just a rough
idea
(untested) of what you'd add as a new field in your query:

YourNewContactInfoField:
IIF(Nz([GroupID],"")<>"",[YourAlternateLanguageField],[YourContactInfoField])

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, but I knew that part. I need to know the syntax.

For example, I know how to write the statement to say "If the groupID
is
filled, display [insert words here]", but I do not know how to write it
in
order to populate [insert words here] with the variable information.

In other words, it will not always say "See June Bug", so I cannot
write
the
statement to say "If the GroupID is filled, display 'See June Bug' ".
It
has
to be something like, "If the GroupID is filled, display [name
associated
with this specific GroupID in a different table]".

Allison

:

Allison

You posted to a 'forms'-related newsgroup, so I'll assume you want to
do
this in a form.

If this were mine, I'd do it in a query! You can use the IIF()
statement
in
a query to chose either contact info or "see X", then use that query
as
the
source for your form.

NOTE: you can use this same approach to 'feed' a report as well.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Access 2003, Win XP

I would like to display a contact phone number for some contacts,
and
for
others I'd like to say "see x", where "x" is a related contact
entry.
Sort
of like in telephone books if you look up "movie theater" it tells
you
to
"see theaters".

My contacts table (tblContact) contains a field to identify it as
part
of
another group (GroupID). The group table (tblGroup) contains the
GroupID
and
other group details.

For example, contact "John Anderson" could be part of a group with
GroupID
248 called "The Andersons", contact "Richard Bloom" could belong to
no
group,
and contact "ACME Dental" could be part of a group called "ACME".

So, I'd want to display:

NAME - CONTACT INFO
John Anderson - See "The Andersons"
Richard Bloom - contact info
ACME Dental - See "ACME"
ACME - contact info
The Andersons - contact info

What would be my conditional statement to make that happen?

Thank you for your help.

Allison