Comparing Columns of numbers with IIf function

[emspence]

I have 2 sets of numbers five columns each set each number in seperate column. I want to compare the numbers in the second set to the numbers in the first set. If they match the result is 1, if not 0. First set columns named field 1 thru field 5. Second set of numbers named F3 thru F7. So essentially I want to compare F3 against Fields 1 thru 5. If it matches either field result would be 1. I keep getting syntax. So I tried to do the same with individual columns but thats a lot of unnecessary calculations I think. Thanks in advance for any help.

[June7]

What do you want?

If any of fields 1 thru 5 match any of F3 thru F7 then TRUE?

[emspence]

yes that is exactly what i am trying to get. And true equals 1, false equals 0. Unfortunately I keep getting syntax.

[June7]

Well, does sound like it would be a very long nested IIf expression. Probably want to build a custom function in VBA and call the function from query or textbox.

Show the expression you have attempted.

[emspence]

I tried many different so I cannot show exactly what you're asking for, but at current I have: NM1A: IIf([F3]=[Field1],1,0) That only compares F3 against Field 1, so I have to do this 4 more times, 1 for each field and them sum all 5 fields. And then I would have to repeat for F4 through F7. I know this is Access and not Excel but I have done this in Excel and it works there with an If statement. But I cannot automate steps after this in Excel, so I really need to get this working here. Of course once I get this comparing working then I can filter out all the results that are zero and reduce the number of records that my query is retrieving. Right now it is somewhere around 44 million records which are causing me some "not responding" issues. Thank you some much for your help.

[June7]

Why would you need to do sum?

Is this what you are looking for:

IIf([F3]=[Field1] Or [F3]=[Field2] Or [F3]=[Field3] Or [F3]=[Field4] Or [F3]=[Field5] Or
[F4]=[Field1] Or [F4]=[Field2] Or [F4]=[Field3] Or [F4]=[Field4] Or [F4]=[Field5] Or
[F5]=[Field1] Or [F5]=[Field2] Or [F5]=[Field3] Or [F5]=[Field4] Or [F5]=[Field5] Or
[F6]=[Field1] Or [F6]=[Field2] Or [F6]=[Field3] Or [F6]=[Field4] Or [F6]=[Field5] Or
[F7]=[Field1] Or [F7]=[Field2] Or [F7]=[Field3] Or [F7]=[Field4] Or [F7]=[Field5], 1, 0)

[emspence]

i will try what you show, but I will need to sum the 5 results fields i.e reults of F3 against Fields 1 thru 5, the the results of F4 against fields 1 thru 5, etc...., but I was not using "Or" in between, so hopefully that was my issue. Again thanks very much.

[emspence]

Okay it worked. Thank you. Such a simple solution. Well I won't have to worry about paying for a haircut anytime soon.