G
Guest
I am using Access 2000 and have created a client database that has a
Companies table containing company information, Contacts table containing
contact information which also contains a lookup field to the Companies
table, multiple event tables which contains a lookup field to the Contacts
table and 12 fields (Rlwyr, L2, L3, etc.) which contain initials of people
within the company who want to invite a Contact to an event. I have one
slight problem. One person in the company (Person A) does not want to use
Mr., Ms. and Mrs. on any names except for the ones that he has specified he
wants to use. The only problem is that from one event to another, the
inviter(s) to an event is not always the same for a Contact. I also had a
problem when I was asked to pull all the female Contacts for certain persons
within our company for a special event. Because the Prefix field (which
contains the Mr., Ms. and Mrs.) is blank for all the Contacts that Person A
have invited to an event are blank, I was not able to get a complete list
without doing some editing after the fact.
To try to rectify this problem, I created an additional field in the
Contacts database which is a checkbox type field called Possible Prefix. If
Person A has invited an individual to an event, I have checked this box. The
reason I did this is because Person A sometimes uses a prefix.
I use a query to combine all the information together to do a merge in Word
for printing labels and other relevant tables (i.e. invitee lists, RSVP
lists, labels, etc.). On one event which only had one person for each
invitee, I was able to use a IIF statement for the Prefix field to not
include the Prefix if Person A was the inviter. Person A's initials is HDM.
Prefix: IIf([Contacts].[HDM Prefix]=Yes,IIf([Edmonton An Evening at the
Races].[Rlwyr]="HDM","",[Contacts].[Prefix]),[Contacts].[Prefix])
The query results showed the prefix for the names that I wanted and not for
the ones that Person A was inviting.
However I am having problems with other events that could have up to 12
inviters. The IIF statement I used in the query is:
Prefix: IIf([Contacts].[HDM Prefix]=Yes,IIf([Edmonton Golf
Tournament].[Rlwyr]="HDM","",IIf([Edmonton Golf
Tournament].[L2]="HDM","",IIf([Edmonton Golf
Tournament].[L3]="HDM","",IIf([Edmonton Golf
Tournament].[L4]="HDM","",IIf([Edmonton Golf
Tournament].[L5]="HDM","",IIf([Edmonton Golf
Tournament].[L6]="HDM","",IIf([Edmonton Golf
Tournament].[L7]="HDM","",IIf([Edmonton Golf
Tournament].[L8]="HDM","",IIf([Edmonton Golf
Tournament].[L9]="HDM","",IIf([Edmonton Golf
Tournament].[L10]="HDM","",IIf([Edmonton Golf
Tournament].[L11]="HDM","",IIf([Edmonton Golf
Tournament].[L12]="HDM","",[Contacts].[Prefix]),""),""),""),""),""),""),""),""),""),""),""),[Contacts].[Prefix])
I get an error message: The expression you entered has a function
containing the wrong number of arguments.
Is there a better way of doing this? Am I using too many IIF statements?
Companies table containing company information, Contacts table containing
contact information which also contains a lookup field to the Companies
table, multiple event tables which contains a lookup field to the Contacts
table and 12 fields (Rlwyr, L2, L3, etc.) which contain initials of people
within the company who want to invite a Contact to an event. I have one
slight problem. One person in the company (Person A) does not want to use
Mr., Ms. and Mrs. on any names except for the ones that he has specified he
wants to use. The only problem is that from one event to another, the
inviter(s) to an event is not always the same for a Contact. I also had a
problem when I was asked to pull all the female Contacts for certain persons
within our company for a special event. Because the Prefix field (which
contains the Mr., Ms. and Mrs.) is blank for all the Contacts that Person A
have invited to an event are blank, I was not able to get a complete list
without doing some editing after the fact.
To try to rectify this problem, I created an additional field in the
Contacts database which is a checkbox type field called Possible Prefix. If
Person A has invited an individual to an event, I have checked this box. The
reason I did this is because Person A sometimes uses a prefix.
I use a query to combine all the information together to do a merge in Word
for printing labels and other relevant tables (i.e. invitee lists, RSVP
lists, labels, etc.). On one event which only had one person for each
invitee, I was able to use a IIF statement for the Prefix field to not
include the Prefix if Person A was the inviter. Person A's initials is HDM.
Prefix: IIf([Contacts].[HDM Prefix]=Yes,IIf([Edmonton An Evening at the
Races].[Rlwyr]="HDM","",[Contacts].[Prefix]),[Contacts].[Prefix])
The query results showed the prefix for the names that I wanted and not for
the ones that Person A was inviting.
However I am having problems with other events that could have up to 12
inviters. The IIF statement I used in the query is:
Prefix: IIf([Contacts].[HDM Prefix]=Yes,IIf([Edmonton Golf
Tournament].[Rlwyr]="HDM","",IIf([Edmonton Golf
Tournament].[L2]="HDM","",IIf([Edmonton Golf
Tournament].[L3]="HDM","",IIf([Edmonton Golf
Tournament].[L4]="HDM","",IIf([Edmonton Golf
Tournament].[L5]="HDM","",IIf([Edmonton Golf
Tournament].[L6]="HDM","",IIf([Edmonton Golf
Tournament].[L7]="HDM","",IIf([Edmonton Golf
Tournament].[L8]="HDM","",IIf([Edmonton Golf
Tournament].[L9]="HDM","",IIf([Edmonton Golf
Tournament].[L10]="HDM","",IIf([Edmonton Golf
Tournament].[L11]="HDM","",IIf([Edmonton Golf
Tournament].[L12]="HDM","",[Contacts].[Prefix]),""),""),""),""),""),""),""),""),""),""),""),[Contacts].[Prefix])
I get an error message: The expression you entered has a function
containing the wrong number of arguments.
Is there a better way of doing this? Am I using too many IIF statements?