Iif expression based on combobox entry

  • Thread starter Thread starter kirby
  • Start date Start date
K

kirby

Please help, I just can't seem to wrap my head around this one:

I am using a form with a query as a source to bring up a report
detailing every job one person has worked on in a given period of time.
The user chooses a name in a combobox, enters the time span they would
like to see, and it brings everything up just fine.
One of my query fields is designed to show the person's role in the
job. If their name is in either the [Translator] or [Translator 2]
fields, then the field should read "Translator." If their name is not
in either of those fields, it should read "Reviewer." This works fine
when I insert someone's name like this:
IIf([Translator]="Last, First" Or [Translator 2]="Last,
First","Translator","Reviewer")

But since the name is determined by what is selected in the form's
combobox, I can't use this method. So I tried this:
IIf([Translator]="[Forms]![frmpersonalstats]![cboName]" Or [Translator
2]="[Forms]![frmpersonalstats]![cboName]","Translator","Reviewer")

But for some reason that returns "Reviewer" for every single job, which
is not the case.

Here is my SQL Statement (which I am not too familiar with, I must
admit)
SELECT [Q and D Database].[Source/Target], [Q and D
Database].Translator, [Q and D Database].[Translator 2], [Q and D
Database].Reviewer, [Q and D Database].[Reviewer 2], [Q and D
Database].Description, [Q and D Database].[Project Type], [Q and D
Database].Comments, [Q and D Database].[Word Count], [Q and D
Database].[Out Date], [Q and D Database].[Type of Work],
IIf([Translator]="[Forms]![frmpersonalstats]![cboName]" Or [Translator
2]="[Forms]![frmpersonalstats]![cboName]","Translator","Reviewer") AS
Expr1
FROM [Q and D Database]
WHERE ((([Q and D
Database].Translator)=[Forms]![frmpersonalstats]![cboName]) AND (([Q
and D Database].[Out Date]) Between [Enter beginning out date
(XX/XX/XX):] And [Enter ending out date (XX/XX/XX):])) OR ((([Q and D
Database].[Translator 2])=[Forms]![frmpersonalstats]![cboName]) AND
(([Q and D Database].[Out Date]) Between [Enter beginning out date
(XX/XX/XX):] And [Enter ending out date (XX/XX/XX):])) OR ((([Q and D
Database].Reviewer)=[Forms]![frmpersonalstats]![cboName]) AND (([Q and
D Database].[Out Date]) Between [Enter beginning out date (XX/XX/XX):]
And [Enter ending out date (XX/XX/XX):])) OR ((([Q and D
Database].[Reviewer 2])=[Forms]![frmpersonalstats]![cboName]) AND (([Q
and D Database].[Out Date]) Between [Enter beginning out date
(XX/XX/XX):] And [Enter ending out date (XX/XX/XX):]));


Thank you every so much!
 
You should consider normalizing your table structure so you don't use field
names to store data. "Translator" should be a value and not a field name.
The same goes for your other fields that might store a person's name.

If you can't change your table structure, I would create a normalizing union
query so that your report's record source query would be much simpler.

BTW: you must remove the ""s around
"[Forms]![frmpersonalstats]![cboName]"


--
Duane Hookom
MS Access MVP

kirby said:
Please help, I just can't seem to wrap my head around this one:

I am using a form with a query as a source to bring up a report
detailing every job one person has worked on in a given period of time.
The user chooses a name in a combobox, enters the time span they would
like to see, and it brings everything up just fine.
One of my query fields is designed to show the person's role in the
job. If their name is in either the [Translator] or [Translator 2]
fields, then the field should read "Translator." If their name is not
in either of those fields, it should read "Reviewer." This works fine
when I insert someone's name like this:
IIf([Translator]="Last, First" Or [Translator 2]="Last,
First","Translator","Reviewer")

But since the name is determined by what is selected in the form's
combobox, I can't use this method. So I tried this:
IIf([Translator]="[Forms]![frmpersonalstats]![cboName]" Or [Translator
2]="[Forms]![frmpersonalstats]![cboName]","Translator","Reviewer")

But for some reason that returns "Reviewer" for every single job, which
is not the case.

Here is my SQL Statement (which I am not too familiar with, I must
admit)
SELECT [Q and D Database].[Source/Target], [Q and D
Database].Translator, [Q and D Database].[Translator 2], [Q and D
Database].Reviewer, [Q and D Database].[Reviewer 2], [Q and D
Database].Description, [Q and D Database].[Project Type], [Q and D
Database].Comments, [Q and D Database].[Word Count], [Q and D
Database].[Out Date], [Q and D Database].[Type of Work],
IIf([Translator]="[Forms]![frmpersonalstats]![cboName]" Or [Translator
2]="[Forms]![frmpersonalstats]![cboName]","Translator","Reviewer") AS
Expr1
FROM [Q and D Database]
WHERE ((([Q and D
Database].Translator)=[Forms]![frmpersonalstats]![cboName]) AND (([Q
and D Database].[Out Date]) Between [Enter beginning out date
(XX/XX/XX):] And [Enter ending out date (XX/XX/XX):])) OR ((([Q and D
Database].[Translator 2])=[Forms]![frmpersonalstats]![cboName]) AND
(([Q and D Database].[Out Date]) Between [Enter beginning out date
(XX/XX/XX):] And [Enter ending out date (XX/XX/XX):])) OR ((([Q and D
Database].Reviewer)=[Forms]![frmpersonalstats]![cboName]) AND (([Q and
D Database].[Out Date]) Between [Enter beginning out date (XX/XX/XX):]
And [Enter ending out date (XX/XX/XX):])) OR ((([Q and D
Database].[Reviewer 2])=[Forms]![frmpersonalstats]![cboName]) AND (([Q
and D Database].[Out Date]) Between [Enter beginning out date
(XX/XX/XX):] And [Enter ending out date (XX/XX/XX):]));


Thank you every so much!
Click to expand...
 
Back
Top