Search any field

[tryit]

Hi All,
I am searching my table as follows:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));

The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:

[Table1]![NEWFIELD] Like "*" & ... etc.

Is there any way I can just put some sort of wild card in here?, like:

[Table1]![*] Like "*" & ... etc.

And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.


Thanks,
TI

 

[Jerry Whittle]

You have discovered a basic inner mystery of good database and table design.
If you have fields for particular data such as different years, you will have
problems searching for it. If you add fields, such as for year 2010, you will
need to change your queries, forms, and reports. And it will never get easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876

you want a Years column with the data down like:

Years Amount
2009 $2341
2010 $9876

Always keep the same data in one field in one table. Another way to look at
it, if you have data, such as the Year, in the field name, you probably are
going the wrong way with the table design especially if you see similar named
fields going across.

If you have all the same data in one field in one table, finding it is simple.

 

[tryit]

I'm not sure what you mean, Jerry. You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in. So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*". Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.


Thanks,
TI

You have discovered a basic inner mystery of good database and table design.
If you have fields for particular data such as different years, you will have
problems searching for it. If you add fields, such as for year 2010, you will
need to change your queries, forms, and reports. And it will never get easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341        $9876

you want a Years column with the data down like:

Years   Amount
2009     $2341
2010     $9876

Always keep the same data in one field in one table. Another way to look at
it, if you have data, such as the Year, in the field name, you probably are
going the wrong way with the table design especially if you see similar named
fields going across.

If you have all the same data in one field in one table, finding it is simple.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi All,
I am searching my table as follows:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1]  Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));

The problem is that I want this search to search all fields in my
table.  So, every time I add a field, I have to add a new field and a
new line like:

     [Table1]![NEWFIELD]  Like "*" & ... etc.

Is there any way I can just put some sort of wild card in here?, like:

     [Table1]![*]  Like "*" & ... etc.

And be done with it???  --Or the equivalent?  Putting a star there
doesn't seem to work.

Thanks,
TI

 

[Jerry Whittle]

Looks like I owe you an appology as it seems that you don't have data across.

Probably the best way to do a free form search over all fields is using the
Find button on the ribbon or menu bar. It can be set to search all fields and
parts of strings like a wildcard search. However it can be really, really
slow with a large number of records and fields plus only returns one record
at a time.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm not sure what you mean, Jerry. You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in. So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*". Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.


Thanks,
TI

You have discovered a basic inner mystery of good database and table design.
If you have fields for particular data such as different years, you will have
problems searching for it. If you add fields, such as for year 2010, you will
need to change your queries, forms, and reports. And it will never get easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876

you want a Years column with the data down like:

Years Amount
2009 $2341
2010 $9876

Always keep the same data in one field in one table. Another way to look at
it, if you have data, such as the Year, in the field name, you probably are
going the wrong way with the table design especially if you see similar named
fields going across.

If you have all the same data in one field in one table, finding it is simple.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi All,
I am searching my table as follows:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));

The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:

[Table1]![NEWFIELD] Like "*" & ... etc.

Is there any way I can just put some sort of wild card in here?, like:

[Table1]![*] Like "*" & ... etc.

And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.

Thanks,
TI

 

[Duane Hookom]

I think what Jerry was responding to in part was your comment "So, every time
I add a field" which makes it sound like this is a regular occurance like add
a new column for a month or year or department or whatever. These would all
suggest an un-normalized table structure.

You shouldn't generally need to add fields to tables, at least not on a
regular basis.

You can concatenate field names so your search would be:
WHERE [Field1] & [Field2] & [Field3] Like "*" & Forms!frmSearch!txtSearch &
"*"

--
Duane Hookom
Microsoft Access MVP

I'm not sure what you mean, Jerry. You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in. So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*". Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.


Thanks,
TI

You have discovered a basic inner mystery of good database and table design.
If you have fields for particular data such as different years, you will have
problems searching for it. If you add fields, such as for year 2010, you will
need to change your queries, forms, and reports. And it will never get easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876

you want a Years column with the data down like:

Years Amount
2009 $2341
2010 $9876

Always keep the same data in one field in one table. Another way to look at
it, if you have data, such as the Year, in the field name, you probably are
going the wrong way with the table design especially if you see similar named
fields going across.

If you have all the same data in one field in one table, finding it is simple.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi All,
I am searching my table as follows:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));

The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:

[Table1]![NEWFIELD] Like "*" & ... etc.

Is there any way I can just put some sort of wild card in here?, like:

[Table1]![*] Like "*" & ... etc.

And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.

Thanks,
TI

 

[Jeff Boyce]

If you 'have not spread the same data across fields', why do you need to
search in multiple fields?

Even if you have employee names, project names, ...in their respective
fields, what will being able to find "something name" allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm not sure what you mean, Jerry. You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in. So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*". Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.


Thanks,
TI

You have discovered a basic inner mystery of good database and table
design.
If you have fields for particular data such as different years, you will
have
problems searching for it. If you add fields, such as for year 2010, you
will
need to change your queries, forms, and reports. And it will never get
easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876

you want a Years column with the data down like:

Years Amount
2009 $2341
2010 $9876

Always keep the same data in one field in one table. Another way to look
at
it, if you have data, such as the Year, in the field name, you probably
are
going the wrong way with the table design especially if you see similar
named
fields going across.

If you have all the same data in one field in one table, finding it is
simple.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi All,
I am searching my table as follows:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));

The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:

[Table1]![NEWFIELD] Like "*" & ... etc.

Is there any way I can just put some sort of wild card in here?, like:

[Table1]![*] Like "*" & ... etc.

And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.

Thanks,
TI

 

[tryit]

Hi Jeff,

I want the user to be able to enter a single search term in a single
box without being bothered with worrying about which field of my table
he's searching in. You know, like when you search in google, you
don't specify which types of pages you want to search, you just
search. That's all.

Is that wrong design?

Best regards,
TI

If you 'have not spread the same data across fields', why do you need to
search in multiple fields?

Even if you have employee names, project names, ...in their respective
fields, what will being able to find "something name" allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure what you mean, Jerry.  You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in.  So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*".  Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.

Thanks,
TI

You have discovered a basic inner mystery of good database and table
design.
If you have fields for particular data such as different years, you will
have
problems searching for it. If you add fields, such as for year 2010, you
will
need to change your queries, forms, and reports. And it will never get
easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876

you want a Years column with the data down like:

Years Amount
2009 $2341
2010 $9876

Always keep the same data in one field in one table. Another way to look
at
it, if you have data, such as the Year, in the field name, you probably
are
going the wrong way with the table design especially if you see similar
named
fields going across.

If you have all the same data in one field in one table, finding it is
simple.

Hi All,
I am searching my table as follows:
SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));
The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:
[Table1]![NEWFIELD] Like "*" & ... etc.
Is there any way I can just put some sort of wild card in here?, like:
[Table1]![*] Like "*" & ... etc.
And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.
Thanks,
TI

 

[tryit]

I think what Jerry was responding to in part was your comment "So, every time
I add a field" which makes it sound like this is a regular occurance likeadd
a new column for a month or year or department or whatever. These would all
suggest an un-normalized table structure.

You shouldn't generally need to add fields to tables, at least not on a
regular basis.

You can concatenate field names so your search would be:
WHERE [Field1] & [Field2] & [Field3] Like "*" & Forms!frmSearch!txtSearch&
"*"

Thanks. Too bad there's no way to specify a wild card for any field.


TI

 

[Jerry Whittle]

Using the Google analogy, when you search for "Smith" you sure get back a lot
of unneeded junk. If you searched every column in a table for "Smith" in your
database, it could return the last name of Smith, Smith Street addresses, the
city of Smith, Smithton, etc., Smith College, Smith & Wesson gun, and a whole
bunch of other stuff that might just confuse your users. Even worse what if
they searched for the number 2? The might be looking for an item quantity,
but can get back part numbers, locations, etc.

If they mostly search on certain fields, like name and address, why not just
make those specific searches easier? If they need to look for other data,
they could use the Find in the menu.

The beauty of a properly normalized database is that a specific piece of
data can be in an exact place. If you are looking Employees with the last
name of Smith, there should be little doubt of how and where to find them.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jeff,

I want the user to be able to enter a single search term in a single
box without being bothered with worrying about which field of my table
he's searching in. You know, like when you search in google, you
don't specify which types of pages you want to search, you just
search. That's all.

Is that wrong design?

Best regards,
TI

If you 'have not spread the same data across fields', why do you need to
search in multiple fields?

Even if you have employee names, project names, ...in their respective
fields, what will being able to find "something name" allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure what you mean, Jerry. You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in. So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*". Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.

Thanks,
TI

You have discovered a basic inner mystery of good database and table
design.
If you have fields for particular data such as different years, you will
have
problems searching for it. If you add fields, such as for year 2010, you
will
need to change your queries, forms, and reports. And it will never get
easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876

you want a Years column with the data down like:

Years Amount
2009 $2341
2010 $9876

Always keep the same data in one field in one table. Another way to look
at
it, if you have data, such as the Year, in the field name, you probably
are
going the wrong way with the table design especially if you see similar
named
fields going across.

If you have all the same data in one field in one table, finding it is
simple.

:
Hi All,
I am searching my table as follows:

SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));

The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:

[Table1]![NEWFIELD] Like "*" & ... etc.

Is there any way I can just put some sort of wild card in here?, like:

[Table1]![*] Like "*" & ... etc.

And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.

Thanks,
TI

 

[Jeff Boyce]

Are you offering your users a way to rummage in the underlying tables?

Access is not a spreadsheet, and you risk a lot if you let users poke around
in the tables themselves rather than in queries.

But Access queries can help folks find information ... again, why would what
they are looking for be (potentially) located in any column (rather than in
one specific column).

So, even if you give your users a way to find "Smith" (see Jerry's quite
legitimate concerns), what are you expecting them to do with the knowledge
that "Smith" exists somewhere in the db? What is the(ir) next step? After
all, a simple yes/no answer doesn't seem like the REAL reason they're
looking...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

I want the user to be able to enter a single search term in a single
box without being bothered with worrying about which field of my table
he's searching in. You know, like when you search in google, you
don't specify which types of pages you want to search, you just
search. That's all.

Is that wrong design?

Best regards,
TI

If you 'have not spread the same data across fields', why do you need to
search in multiple fields?

Even if you have employee names, project names, ...in their respective
fields, what will being able to find "something name" allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure what you mean, Jerry. You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in. So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*". Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.

Thanks,
TI

You have discovered a basic inner mystery of good database and table
design.
If you have fields for particular data such as different years, you will
have
problems searching for it. If you add fields, such as for year 2010, you
will
need to change your queries, forms, and reports. And it will never get
easier
or be right.

Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876

you want a Years column with the data down like:

Years Amount
2009 $2341
2010 $9876

Always keep the same data in one field in one table. Another way to look
at
it, if you have data, such as the Year, in the field name, you probably
are
going the wrong way with the table design especially if you see similar
named
fields going across.

If you have all the same data in one field in one table, finding it is
simple.

Hi All,
I am searching my table as follows:
SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*" Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));
The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:
[Table1]![NEWFIELD] Like "*" & ... etc.
Is there any way I can just put some sort of wild card in here?, like:
[Table1]![*] Like "*" & ... etc.
And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.
Thanks,
TI

 

[tryit]

The search term wouldn't be potentially located in any column. It
would be in one specific column...almost always. Before you respond
to that with the repeated suggestion that I make my searches field
specific, please read the next sentence with care: I want to provied
the users with a single search box (a la Google) and not ask them to
specify which field they wish to look in. No of course I'm not asking
them to edit the underlying tables or am under the impression that
Access is Excel. The scenarios Jerry outlines wouldn't happen with
the data I'm using or the users who are accessing it in 99% of the
cases--yes I'm certain of that. Again, I don't know why this is
supposedly not a properly normalized database.

To answer your question, if they found "Smith", I would give them
clickable links to records with Smith in the db. They would use the
clickable links to go to those records.

Look, on second thought: thanks for your help guys. At this point,
never mind. I had a question, which was whether SQL allows for a wild
card for fields. I now know it doesn't. Thank you for the
information.

Let's let my response be the last post in this thread.


TI

Are you offering your users a way to rummage in the underlying tables?

Access is not a spreadsheet, and you risk a lot if you let users poke around
in the tables themselves rather than in queries.

But Access queries can help folks find information ... again, why would what
they are looking for be (potentially) located in any column (rather than in
one specific column).

So, even if you give your users a way to find "Smith" (see Jerry's quite
legitimate concerns), what are you expecting them to do with the knowledge
that "Smith" exists somewhere in the db?  What is the(ir) next step?  After
all, a simple yes/no answer doesn't seem like the REAL reason they're
looking...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

I want the user to be able to enter a single search term in a single
box without being bothered with worrying about which field of my table
he's searching in.  You know, like when you search in google, you
don't specify which types of pages you want to search, you just
search.  That's all.

Is that wrong design?

Best regards,
TI

If you 'have not spread the same data across fields', why do you need to
search in multiple fields?

Even if you have employee names, project names, ...in their respective
fields, what will being able to find "something name" allow you to do?

Jeff Boyce
Microsoft Office/Access MVP

"tryit" <[email protected]> wrote in message

I'm not sure what you mean, Jerry. You may have misunderstood me. I
have not spread the same data across fields.

For example, in my actual database, I have employee names in one
field, project names in another, product names in a third, and so on.
I'm open to suggestions on design, but I think the db is designed
pretty rationally.

What I'm trying to do is provide a single, Google-like search box on a
space-limited form such that the user doesn't need to specify what
field he's searching in. So, for example, if he wants to query the db
for an employee called Jerry Whittle, he could put Whitt into my
search box and get back a report with records some field of which
matched "*Whitt*". Just as when you do a Google search, you get
results that aren't quite what you're looking for, it is possible he
might get back some records that have, say, product names with the
string Whitt in them, but that's okay.

Is it wrong of me to want such a search box?

If not, then I would still like some way to specify a wild card
field...or the equivalent, so I don't need to provide a lengthy
expression specifying every last field in my table.

Thanks,
TI

You have discovered a basic inner mystery of good database and table
design.
If you have fields for particular data such as different years, you will
have
problems searching for it. If you add fields, such as for year 2010, you
will
need to change your queries, forms, and reports. And it will never get
easier
or be right.
Instead of something like Years across the fields like
Year2009 Year2010 etc.,
$2341 $9876
you want a Years column with the data down like:
Years Amount
2009 $2341
2010 $9876
Always keep the same data in one field in one table. Another way to look
at
it, if you have data, such as the Year, in the field name, you probably
are
going the wrong way with the table design especially if you see similar
named
fields going across.
If you have all the same data in one field in one table, finding it is
simple.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Hi All,
I am searching my table as follows:
SELECT Table1.ID, Table1.field1, Table1.field2
FROM Table1
WHERE (((
[Table1]![field1] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"
Or [Forms]![SearchForm]![txtSearch] Is Null
Or
[Table1]![field2] Like "*" & [Forms]![SearchForm]![txtSearch] & "*"Or
[Forms]![SearchForm]![txtSearch] Is Null)
=True));
The problem is that I want this search to search all fields in my
table. So, every time I add a field, I have to add a new field and a
new line like:
[Table1]![NEWFIELD] Like "*" & ... etc.
Is there any way I can just put some sort of wild card in here?, like:
[Table1]![*] Like "*" & ... etc.
And be done with it??? --Or the equivalent? Putting a star there
doesn't seem to work.
Thanks,
TI