Access 2013 - Look for a value in multiple fields in a single Table based on what is entered in a Text Box


In my Access 2013 Database, I have a Table entitled, "Inventory", where I collect among other things, the PC's IP Address, Physical MAC Address and Wireless MAC Address.   I am trying to develop a Query where the "Inventory" Table is searched within those fields for whatever value the User puts into a particular Text Box of which would be an IP Address or a MAC Address.  If found, it would list that entire data row.  I know how to do it if looking for a value within a single field of a Table but not for multiple fields within a Table.  The IP or MAC Address entered would be unique as the Table is set to not allow duplications within any of those fields so if what the User entered in the Text Box is in the "Inventory" Table, it would only be listed once in one of those three fields.

Any ideas?  The closest I've come had something to do with an "Except" Statement but I don't think that fits my particular situation.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Select * from tblInventory Where IPAddress = Forms!yourform!yourstring OR Physical MACAddress = Forms!yourform!yourstring OR WirelessMACAddress = Forms!yourform!yourstring;
Robert ShermanOwnerCommented:
Be sure to place brackets around any field names that contain spaces.  [Physical Mac Address]
XailonAuthor Commented:
Hey Pat,

I thought that I had tried that before but went ahead and entered that statement anyways.  The actual statement that fits all of my particular parameters looks like this:

Search: (Select * from [Inventory] Where [Inventory]![IP_ADDRESS] = Forms![Reports]![txtIPAddress] OR [Inventory]![MAC_ADDRESS_PHYSICAL] = Forms![Reports]![txtIPAddress] OR [Inventory]![MAC_ADDRESS_WIRELESS] = Forms![Reports]![txtIPAddress])

However, when I run the Query, I get the following error:  "You have written a subquery that can return more than one field without using the EXISTS reserved work in the main query's FROM clause.  Revise the SELECT statement of the subquery to request only one field."

I've lookup up examples of "EXISTS" Statements but can't seem to find how it would fit my particular issue as the examples I found referenced multiple Tables.  Would you happen to have any other ideas?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Robert ShermanOwnerCommented:
Where are you trying to put the results?   More specifically, where are you putting the query?   The error you are getting suggests that the recipient of the query results is only expecting a single value, not an entire table row.
Do you really have parentheses around the whole query?  That may be what is causing Access to think it is a sub query.

Select * from Inventory Where Inventory.IP_ADDRESS = Forms![Reports]![txtIPAddress] OR Inventory.MAC_ADDRESS_PHYSICAL = Forms![Reports]![txtIPAddress] OR Inventory.MAC_ADDRESS_WIRELESS = Forms![Reports]![txtIPAddress]

You ONLY need the brackets if you use poor naming standards that include spaces and special characters.  Otherwise, the square brackets are just noise that make the query harder to read.
XailonAuthor Commented:
Hey Robert,

The results are not being put into a place at all, I just want them displayed like it would normally do in a generic query.  I plan on later attaching a report to display it but it's not like I'm trying to insert it into a Table or anything.

If I were to explain what I'm attempting in simple terms like as if I were talking to a computer from Star Trek I would say:  "Look at the value that was placed in the Text Box entitled "IP_Address" from the Form entitled, "Reports".  Now take that value and look in the Table entitled, "Inventory".  Within the "Inventory" Table there are three fields within each data row entitled, "IP_ADDRESS", "MAC_ADDRESS_PHYSICAL" and "MAC_ADDRESS_WIRELESS".  If the value entered in the "IP_Address" Text Box were to exist in the "Inventory" Table, it would exist in one of those three fields.  If you find it, simply display then entire data row where it is found."  Too bad I can't simply do that, huh?
XailonAuthor Commented:

I have it exactly as I displayed it.  The brackets are just what Access 2013 put in when I selected those fields via a mouse click from the query options themselves.  I did it that way so to eliminate the chance that I was simply misspelling or not correctly structuring the code.  I didn't put them there myself.
Yes, but the outer parentheses are most likely what is causing the query engine to think this is a sub query.  Did you remove them?  Try running the query as I pasted it back.
XailonAuthor Commented:
Hey Pat,

I put it in exactly like this:  Search: (Select * from Inventory Where IP_ADDRESS = Forms!Reports!txtIPAddress OR MAC_ADDRESS_PHYSICAL = Forms!Reports!txtIPAddress OR MAC_ADDRESS_WIRELESS = Forms!Reports!txtIPAddress)

Unfortunately, I got the same error as before.  I don't know why it keeps telling me that it could produce multiple results as it never gets to the part where it asks for information and there isn't a possibility it could ever produce multiple results as whatever I would put in would be unique.  Weird.
Robert ShermanOwnerCommented:
Judging from the "Search: " you have before the SQL statement, are you specifying that in a column of the query designer?

This is what I meant when I asked where you were putting the query that you specified in your question.   If you are specifying this SQL inside of a field column in the query designer, you will get the error you are getting because by requesting a "SELECT *" you are asking for more than a single field.  See the screenshot below where I attempted a simplified version:

You will find that if you change the asterisk to a single fieldname from your Inventory table, it would work.

If what you are looking for is a query that will return entire records from the Inventory table where any of a number of fields match some criteria from a form you can do that in Query Design mode by placing each possible field in a column of the query design and then place the form control name under Criteria, where you will place the name in different rows under Criteria (see how it says "or:" under Criteria, so for each field column, you would put the form control name in a separate "or" row), see below for example:

EDIT: to extend the example below, you could uncheck the "Show" mark for the criteria fields, and add a single * field column to display the entire record.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
XailonAuthor Commented:
Pat...thanks for sticking with me.  I think that helped redefine the issue which helped Robert see the solution.

Robert..that was EXACTLY it.  It works perfectly now.  I wish I would have understood your question of where I was putting the query earlier. may have the correct idea but if you don't structure it JUST right...ugg.  Frustrating.  Thanks again Robert.
Robert ShermanOwnerCommented:
Well, I must say that all we did is take a very round-about path which you'll find lead back to Pat's original suggestion...   If you construct the query in Query Designer in the way that I demonstrated, and then switch over to SQL View, what you end up is going to be very similar to Pat's first comment.  

What Access wasn't happy with was the attempt to specify the SQL statement as a single column in the Query Designer.  

The one bit of advice I would hope anyone reading takes from this, would be that if you are having trouble running something from the Query Design mode, it often helps to switch to SQL View to get a better idea of what's really being run when you click that exclamation icon.   Some things the Query Designer will happily accept as valid input, when in fact what gets generated in terms of the underlying SQL might be far from what you were intending.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.