Comparing and returning records from one field with 4 other fields

[Stewart Ryan]

I have a table with 4 fields (Name1, Name2, Name3, Name4)
that are all selected from a fixed Combo box list for
each record. Only Name 1 is a required field, the others
may be Null or filled.

I have a form that will select one name from the same
Combo box list.

I want to compare the name selected on the form and
compare it to the 4 name fields in the table and return
all records where the form selected name matches with ANY
of the Name1-4 fields.

Is there an easy syntax for this? Using the criteria
(=Forms!NameDropDown or Is NULL) in a query works for one
field but not all four.

Thanks,

Stewart

 

[John Vinson]

I have a table with 4 fields (Name1, Name2, Name3, Name4)

Then you have an improperly normalized table. If you have a one
(something) to many (name) relationship, you should have TWO tables in
a one to many relationship.

that are all selected from a fixed Combo box list for
each record. Only Name 1 is a required field, the others
may be Null or filled.

I have a form that will select one name from the same
Combo box list.

I want to compare the name selected on the form and
compare it to the 4 name fields in the table and return
all records where the form selected name matches with ANY
of the Name1-4 fields.

Is there an easy syntax for this? Using the criteria
(=Forms!NameDropDown or Is NULL) in a query works for one
field but not all four.

It works IF you put the four criteria on four separate OR lines in the
grid. The SQL would be

([Name1] = Forms!NameOfForm!NameDropDown) OR ([Name2] =
Forms!NameOfForm!NameDropDown) OR ([Name3] = ... <etc>

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps