Conditional Function within a report

[mlewis]

I would like to have one field provide me the value of another field in
another table. For example:

Look at LAY 1 - if LAY 1 = 1 then give me the value of a field in another
table. If LAY 2 = 2 then give me the value a different field in another table

 

[Duane Hookom]

Looks like you could simply join "another table" into your report's record
source and join the "Lay 1" field to some field in "another table". Then add
the "field in another table" to the query grid so you can view it in the
report.

 

[mlewis]

I decided to do it with this function; however, I've got a funny result:

=IIf([DUE]="1/1/2001","") And IIf([LAY 1]="1","100.00","") & IIf([LAY
1]="2","175.00","") & IIf([LAY 1]="3","200.00","") & IIf([LAY
1]="4","200.00","") & IIf([LAY 1]="5","200.00","")

My current result is "-1" in PROD FEE when the [DUE] field = 1/1/2001 and
LAY 1 = 4
PROD FEE is blank in all other records where [DUE] is different.

 

[Duane Hookom]

Your expression expects [DUE] and [LAY 1] to both be text and your
expression returns a text value. Is this what you have and want?

Also, I would never use a nested IIf() like this. Is there a reason you
didn't follow my suggestion? I would like to think I answer questions here
for some benefit.

Since you have a [Lay 1] field, do you also have a [Lay 2] and possibly
others?


--
Duane Hookom
MS Access MVP

I decided to do it with this function; however, I've got a funny result:

=IIf([DUE]="1/1/2001","") And IIf([LAY 1]="1","100.00","") & IIf([LAY
1]="2","175.00","") & IIf([LAY 1]="3","200.00","") & IIf([LAY
1]="4","200.00","") & IIf([LAY 1]="5","200.00","")

My current result is "-1" in PROD FEE when the [DUE] field = 1/1/2001 and
LAY 1 = 4
PROD FEE is blank in all other records where [DUE] is different.

Looks like you could simply join "another table" into your report's
record
source and join the "Lay 1" field to some field in "another table". Then
add
the "field in another table" to the query grid so you can view it in the
report.

 

[mlewis]

I'm sorry I'm not familiar with joining the fields and tables as you mentioned.

Your expression expects [DUE] and [LAY 1] to both be text and your
expression returns a text value. Is this what you have and want?

Also, I would never use a nested IIf() like this. Is there a reason you
didn't follow my suggestion? I would like to think I answer questions here
for some benefit.

Since you have a [Lay 1] field, do you also have a [Lay 2] and possibly
others?


--
Duane Hookom
MS Access MVP

I decided to do it with this function; however, I've got a funny result:

=IIf([DUE]="1/1/2001","") And IIf([LAY 1]="1","100.00","") & IIf([LAY
1]="2","175.00","") & IIf([LAY 1]="3","200.00","") & IIf([LAY
1]="4","200.00","") & IIf([LAY 1]="5","200.00","")

My current result is "-1" in PROD FEE when the [DUE] field = 1/1/2001 and
LAY 1 = 4
PROD FEE is blank in all other records where [DUE] is different.

Looks like you could simply join "another table" into your report's
record
source and join the "Lay 1" field to some field in "another table". Then
add
the "field in another table" to the query grid so you can view it in the
report.


--
Duane Hookom
MS Access MVP

I would like to have one field provide me the value of another field in
another table. For example:

Look at LAY 1 - if LAY 1 = 1 then give me the value of a field in
another
table. If LAY 2 = 2 then give me the value a different field in
another
table

 

[Duane Hookom]

Joining fields is creating a basic query with more than a single table. You
can create joins that describe relationships between tables. Check most
queries in the Northwind sample database.

--
Duane Hookom
MS Access MVP

I'm sorry I'm not familiar with joining the fields and tables as you
mentioned.

Your expression expects [DUE] and [LAY 1] to both be text and your
expression returns a text value. Is this what you have and want?

Also, I would never use a nested IIf() like this. Is there a reason you
didn't follow my suggestion? I would like to think I answer questions
here
for some benefit.

Since you have a [Lay 1] field, do you also have a [Lay 2] and possibly
others?


--
Duane Hookom
MS Access MVP

I decided to do it with this function; however, I've got a funny result:

=IIf([DUE]="1/1/2001","") And IIf([LAY 1]="1","100.00","") & IIf([LAY
1]="2","175.00","") & IIf([LAY 1]="3","200.00","") & IIf([LAY
1]="4","200.00","") & IIf([LAY 1]="5","200.00","")

My current result is "-1" in PROD FEE when the [DUE] field = 1/1/2001
and
LAY 1 = 4
PROD FEE is blank in all other records where [DUE] is different.


:

Looks like you could simply join "another table" into your report's
record
source and join the "Lay 1" field to some field in "another table".
Then
add
the "field in another table" to the query grid so you can view it in
the
report.


--
Duane Hookom
MS Access MVP

I would like to have one field provide me the value of another field
in
another table. For example:

Look at LAY 1 - if LAY 1 = 1 then give me the value of a field in
another
table. If LAY 2 = 2 then give me the value a different field in
another
table