SQL syntax to check if a value exists in any of 3 fields

[BruceCovey]

A bit rusty on my Access and needing to add functionality to an existing database… Any help would be greatly appreciated.



I added 3 fields to my listSystems table ( funcSysName01, funcSysName02, funcSysName03 ) where a system/device can be a member of up to 3 Functional System groups. In the SQL below, the WHERE works fine when I enter a search value, but I really need to check all 3 fields with an OR. What is the correct syntax for this?


SELECT listSystems.systemName, listSystems.frameName, [locationFacility] & "." & [locationBuilding] & "." & [locationFloor] & "." & [roomName] & "." & [locationRack] AS Location
FROM listSystems
WHERE (((listSystems.funcSysName01)=[enter FuncSysName]));

Also… Any way in the Access Query Designer to have the entry screen for the WHERE to contain a drop-down list of values from a listTable?

[Minty]

You should probably store those as child records of the original table. If you ever get a 4th or 6th Functional group you will have to rebuild every thing.
If they were in a child table it would be simple to check the child table for the presence of any records for the header record.

If you want to pursue the current design (and I wouldn't) then something like;

WHERE listSystems.funcSysName01 = [enter FuncSysName] 
OR listSystems.funcSysName02 = [enter FuncSysName] 
OR listSystems.funcSysName03 = [enter FuncSysName] 

To answer your second question you could put the available system functions names in a lookuptable and use a combo box on a form to refer to them and use the combo in the query.

[Welshgasman]

Learn to use the query design grid.
Then you would put the criteria on to different rows for each funcSysName.

As mentioned, anytime you start adding numbers to the same named field, your design is flawed.

About the only time I would do that, is for Address fields, 1 to 6 plus a PostCode field.

[mike60smart]

Hi Bruce
Can you upload a copy of your database?