X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Multi Field Search, Access 2016    
post Oct 11 2019, 04:29 PM

Posts: 10
Joined: 30-October 11

I need to design a query that will return all records in my database that have the same value in 3 different fields. In other words I would want to find all records where the value "ACCT" occurs in any one of 3 fields of a record. "ACCT" will never be present twice on any one record, but it may be present in any of the 3 fields from record to record. Any ideas on where to start?
Go to the top of the page
Doug Steele
post Oct 11 2019, 04:32 PM

UtterAccess VIP
Posts: 22,203
Joined: 8-January 07
From: St. Catharines, ON (Canada)

SELECT T.Field1, T.Field2, T.Field3, T.Field4, T.Field5
FROM Table1 AS T
WHERE (T.Field1 = 'ACCT')
OR (T.Field2 = 'ACCT')
OR (T.Field3 = 'ACCT');

To do this in the graphical query builder, put ACCT as the criterion under Field1, then put it on a different line under Field2 and finally put it on yet a different line under Field3.

Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
post Oct 11 2019, 04:38 PM

UA Admin
Posts: 35,844
Joined: 20-June 02
From: Newcastle, WA

I assume, like Doug, that you want an exact match, i.e. "ACCT" and not "ACCT_One", or "My_ACCT" or even "Bank ACCT". If any value containing ACCT is possible, you need a different parameter.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Oct 11 2019, 11:30 PM

Posts: 1,424
Joined: 2-April 09
From: somewhere out there...

you can also try:
SELECT * FROM table WHERE (Instr(1, [Field1] & [Field2] & [Field3], "ACCT") > 0);

Never stop learning, because life never stops teaching.
Go to the top of the page
Jeff B.
post Oct 12 2019, 07:57 AM

UtterAccess VIP
Posts: 10,300
Joined: 30-April 10
From: Pacific NorthWet

Why would the same value (?ACCT) show up in more than one field?

More info, please...


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    15th October 2019 - 03:45 AM