iif statement

[Devon]

I have a database with several tables. I have created two separate queries,
ABC query and XYZ query. I am then atempting to write an IIF statement to
compare the data on the two queries to state that if they are the same, put
in the word match, and if they are different, put in the word unmatch.

If the data is the same, (e.g. Apple), then it is working correctly and I am
getting the output I want. I am using the statement below.

IIF ([table1]![Fruits]=[table2]![Fruits], match, unmatched)

However, using a slightly different scenario. how do I create the IIF
statement if the fields in the two tables are not the same? For example,
what if a combo box is used. The values for the combo box would be 1=Cash,
2=Shares. What if one table shows a 1, and the other shows cash? The IIF
statement should show Matched, but I am not sure how to accomplish.

 

[Duane Hookom]

If I understand correctly, you only need to add the lookup table to the query
that has the numeric value. You aren't using lookup fields defined in tables
are you?

 

[Duane Hookom]

There is at least one reply in another news group where you asked the same
question. Please keep your questions to a single news group.

 

[Devon]

Duane, sort of. Essentially I have 6 tables, 3 for each query. Data for the
transactions are entered through a form which populates multiple Access
tables. I then take this data and enter it into another system, which behind
the scenes populates mainframe tables. It is a very ineffficient way of
doing things, but it is what it is.

I am attempting to do the following. I have one query that pull in all the
data from the Access Tables, and a separate query that pulls in all the data
from the mainframe tables. I am then creating a compare query using the two
previously created queries to make sure that what is entered into both the
Access tables and the Mainframe tables are correct.

I have approximately 30 fields to compare, of which 95% of them I can run an
IIF statement similar to this and get what I am looking. Either the fields
match, or they don't match.

IIF ([table1]![Fruits]=[table2]![Fruits], match, unmatched)

However, for the remaining 5%, I want to see if the fields match, but first,
I have to compare like fields. In other words, if 1 = Yes, and 2 = No, on
the Access tables, it may show 1, and on the Mainframe tables it may show
Yes. Logically, I know this is a match, but if I use the IIF statement
above, it shows it as unmatched.

I want to know if there is a way to somehow use an IIF statement to first
say that 1=Yes and 2=No, and then say something similar to the IIF statement
above so that the query will know that 1 and Yes are a match, as well as 2
and No, while 1 and No and 2 and Yes are not a match.

Hope that makes sense.

thanks in advance

If I understand correctly, you only need to add the lookup table to the query
that has the numeric value. You aren't using lookup fields defined in tables
are you?
--
Duane Hookom
Microsoft Access MVP

I have a database with several tables. I have created two separate queries,
ABC query and XYZ query. I am then atempting to write an IIF statement to
compare the data on the two queries to state that if they are the same, put
in the word match, and if they are different, put in the word unmatch.

If the data is the same, (e.g. Apple), then it is working correctly and I am
getting the output I want. I am using the statement below.

IIF ([table1]![Fruits]=[table2]![Fruits], match, unmatched)

However, using a slightly different scenario. how do I create the IIF
statement if the fields in the two tables are not the same? For example,
what if a combo box is used. The values for the combo box would be 1=Cash,
2=Shares. What if one table shows a 1, and the other shows cash? The IIF
statement should show Matched, but I am not sure how to accomplish.

 

[Duane Hookom]

The solution I would use involves lookup tables that could be joined into one
of the queries ie:

tblLookupYesNo
==============
AccessVal MainframeVal
1 Yes
2 No

I prefer maintaining data rather than complex expressions.
--
Duane Hookom
Microsoft Access MVP

Duane, sort of. Essentially I have 6 tables, 3 for each query. Data for the
transactions are entered through a form which populates multiple Access
tables. I then take this data and enter it into another system, which behind
the scenes populates mainframe tables. It is a very ineffficient way of
doing things, but it is what it is.

I am attempting to do the following. I have one query that pull in all the
data from the Access Tables, and a separate query that pulls in all the data
from the mainframe tables. I am then creating a compare query using the two
previously created queries to make sure that what is entered into both the
Access tables and the Mainframe tables are correct.

I have approximately 30 fields to compare, of which 95% of them I can run an
IIF statement similar to this and get what I am looking. Either the fields
match, or they don't match.

IIF ([table1]![Fruits]=[table2]![Fruits], match, unmatched)

However, for the remaining 5%, I want to see if the fields match, but first,
I have to compare like fields. In other words, if 1 = Yes, and 2 = No, on
the Access tables, it may show 1, and on the Mainframe tables it may show
Yes. Logically, I know this is a match, but if I use the IIF statement
above, it shows it as unmatched.

I want to know if there is a way to somehow use an IIF statement to first
say that 1=Yes and 2=No, and then say something similar to the IIF statement
above so that the query will know that 1 and Yes are a match, as well as 2
and No, while 1 and No and 2 and Yes are not a match.

Hope that makes sense.

thanks in advance

If I understand correctly, you only need to add the lookup table to the query
that has the numeric value. You aren't using lookup fields defined in tables
are you?
--
Duane Hookom
Microsoft Access MVP

I have a database with several tables. I have created two separate queries,
ABC query and XYZ query. I am then atempting to write an IIF statement to
compare the data on the two queries to state that if they are the same, put
in the word match, and if they are different, put in the word unmatch.

If the data is the same, (e.g. Apple), then it is working correctly and I am
getting the output I want. I am using the statement below.

IIF ([table1]![Fruits]=[table2]![Fruits], match, unmatched)

However, using a slightly different scenario. how do I create the IIF
statement if the fields in the two tables are not the same? For example,
what if a combo box is used. The values for the combo box would be 1=Cash,
2=Shares. What if one table shows a 1, and the other shows cash? The IIF
statement should show Matched, but I am not sure how to accomplish.

 

[Devon]

Thanks Duane. I will give it a try.

The solution I would use involves lookup tables that could be joined into one
of the queries ie:

tblLookupYesNo
==============
AccessVal MainframeVal
1 Yes
2 No

I prefer maintaining data rather than complex expressions.
--
Duane Hookom
Microsoft Access MVP

Duane, sort of. Essentially I have 6 tables, 3 for each query. Data for the
transactions are entered through a form which populates multiple Access
tables. I then take this data and enter it into another system, which behind
the scenes populates mainframe tables. It is a very ineffficient way of
doing things, but it is what it is.

I am attempting to do the following. I have one query that pull in all the
data from the Access Tables, and a separate query that pulls in all the data
from the mainframe tables. I am then creating a compare query using the two
previously created queries to make sure that what is entered into both the
Access tables and the Mainframe tables are correct.

I have approximately 30 fields to compare, of which 95% of them I can run an
IIF statement similar to this and get what I am looking. Either the fields
match, or they don't match.

IIF ([table1]![Fruits]=[table2]![Fruits], match, unmatched)

However, for the remaining 5%, I want to see if the fields match, but first,
I have to compare like fields. In other words, if 1 = Yes, and 2 = No, on
the Access tables, it may show 1, and on the Mainframe tables it may show
Yes. Logically, I know this is a match, but if I use the IIF statement
above, it shows it as unmatched.

I want to know if there is a way to somehow use an IIF statement to first
say that 1=Yes and 2=No, and then say something similar to the IIF statement
above so that the query will know that 1 and Yes are a match, as well as 2
and No, while 1 and No and 2 and Yes are not a match.

Hope that makes sense.

thanks in advance

If I understand correctly, you only need to add the lookup table to the query
that has the numeric value. You aren't using lookup fields defined in tables
are you?
--
Duane Hookom
Microsoft Access MVP


:

I have a database with several tables. I have created two separate queries,
ABC query and XYZ query. I am then atempting to write an IIF statement to
compare the data on the two queries to state that if they are the same, put
in the word match, and if they are different, put in the word unmatch.

If the data is the same, (e.g. Apple), then it is working correctly and I am
getting the output I want. I am using the statement below.

IIF ([table1]![Fruits]=[table2]![Fruits], match, unmatched)

However, using a slightly different scenario. how do I create the IIF
statement if the fields in the two tables are not the same? For example,
what if a combo box is used. The values for the combo box would be 1=Cash,
2=Shares. What if one table shows a 1, and the other shows cash? The IIF
statement should show Matched, but I am not sure how to accomplish.