many fields, one criteria

[Guest]

Build a query with all ten fields plus another record
identifying field if it exists. As a criterion for the
first serial number field, enter:
Like "*"&[Serial number?]&"*"

As a criterion second field IN THE NEXT CRITERIA ROW,
enter EXACTLY the same string: Like "*"&[Serial number?]
&"*" (you can copy the first one and paste to make sure
you get an exact copy of the string).

Repeat for each field, placing the string in its own
criterion row. This is so the test will be an OR test
rather than and AND test. You may need to insert rows to
get a row for each field (Insert/Rows).

Access will treat [Serial number?] as a single parameter,
prompting only once. The record that matches any instance
will be returned.

Hope this works for you.

 

[Tom Wickerath]

Consider re-designing your database before you spend too much more time with your current design.
You currently have an Access spreadsheet. You should not have several fields that describe the
same type of data (serial number).

You should spend some time gaining an understanding of database design and normalization before
attempting to build something in Access (or any RDBMS software for that matter). Here are some
links to get you started. Don't underestimate the importance of gaining a good understanding of
database design. Brew a good pot of coffee and enjoy reading!

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208


Since you appear to be new at this game, now would be a good time to commit yourself to using
naming conventions. They're not that hard to learn. You will be able to avoid errors that
others encounter if you use naming conventions and avoid the use of special characters and
reserved words.

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335



Tom
___________________________________________


Build a query with all ten fields plus another record
identifying field if it exists. As a criterion for the
first serial number field, enter:
Like "*"&[Serial number?]&"*"

As a criterion second field IN THE NEXT CRITERIA ROW,
enter EXACTLY the same string: Like "*"&[Serial number?]
&"*" (you can copy the first one and paste to make sure
you get an exact copy of the string).

Repeat for each field, placing the string in its own
criterion row. This is so the test will be an OR test
rather than and AND test. You may need to insert rows to
get a row for each field (Insert/Rows).

Access will treat [Serial number?] as a single parameter,
prompting only once. The record that matches any instance
will be returned.

Hope this works for you.

___________________________________________

-----Original Message-----

I have a table with 10 feilds (possibly expanding to 16). Each field has a serial number. For
the database, there will be one serial number in ONE of the fields in ONE of the records. How do
I find which record has that serial number without knowing which of the 10 fields it will be in?

 

[Guest]

You're welcome. Glad to help.

-----Original Message-----
Thanks, anonymous. It worked, would have never found solution on my
own!!!!!!!!!1

Build a query with all ten fields plus another record
identifying field if it exists. As a criterion for the
first serial number field, enter:
Like "*"&[Serial number?]&"*"

As a criterion second field IN THE NEXT CRITERIA ROW,
enter EXACTLY the same string: Like "*"&[Serial number?]
&"*" (you can copy the first one and paste to make sure
you get an exact copy of the string).

Repeat for each field, placing the string in its own
criterion row. This is so the test will be an OR test
rather than and AND test. You may need to insert rows to
get a row for each field (Insert/Rows).

Access will treat [Serial number?] as a single parameter,
prompting only once. The record that matches any instance
will be returned.

Hope this works for you.

-----Original Message-----
I have a table with 10 feilds (possibly expanding to 16). Each field has a
serial number. For the database, there will be one serial number in ONE of
the fields in ONE of the records. How do I find which record has that serial
number without knowing which of the 10 fields it will

be
in?

.

.