Searching multiple fields with textbox control

[GregB]

I have table that has 5 fields that I want to be searched.
I create a form and an unbound control box. I also set the control box after
update property to requery the form.

The form is based of a query where I put Like "*" &
[Forms]![Search]![TextBox] & "*" in the critera. I simiply put the same line
in the or criteria for each field that i want to be searched.... but it is
not working, only searchs the first field.

(this worked when I would prompt the user, but now its not working when
using the textbox as critera)

Thanks for the help
(I didn't know if to start a new post or not, so if I am wrong I am sorry)

Greg

 

[Jeff Boyce]

Greg

Based on your description, you are trying to get Access to do something it
isn't designed for ...

.... or rather, give the data structure you described, you (and Access) will
have to work extra hard to do what you seem to be trying to do.

In a well-normalized relational database, it would be very unusual to need
to search more than ONE field. How is it that there are 5 fields that might
have the search term you are seeking?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GregB]

I know you are abusolutely right!
It is set up such a way in an excel sheet, and I expressed what you just
said to the person who asked for it to be done..... and they are determined
to keep it that way... I don't want to argue with the person because it is my
job to do what they ask.

I know that when i would just run a query with [] input field.... that I
could just copy and paste that into the other fields in the OR rows...

Why won't that work in this situation? Is there a way to make it work?

Thanks for the input and help.

Greg

Greg

Based on your description, you are trying to get Access to do something it
isn't designed for ...

.... or rather, give the data structure you described, you (and Access) will
have to work extra hard to do what you seem to be trying to do.

In a well-normalized relational database, it would be very unusual to need
to search more than ONE field. How is it that there are 5 fields that might
have the search term you are seeking?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have table that has 5 fields that I want to be searched.
I create a form and an unbound control box. I also set the control box
after
update property to requery the form.

The form is based of a query where I put Like "*" &
[Forms]![Search]![TextBox] & "*" in the critera. I simiply put the same
line
in the or criteria for each field that i want to be searched.... but it is
not working, only searchs the first field.

(this worked when I would prompt the user, but now its not working when
using the textbox as critera)

Thanks for the help
(I didn't know if to start a new post or not, so if I am wrong I am sorry)

Greg

 

[Jeff Boyce]

Greg

Not that I'd ever suggest subtrefuge, but...

Unless the person you are answering to will actually dig into the underlying
table structure, you could do what needs to be done in Access and generate
what this person needs to see in the format requested. These do NOT have to
be identical.

If the requester can and or does rummage in the tables, and would "catch you
out" if you design for a relational database model instead of a spreadsheet
model, then I suppose you could:

1 - create a UNION query that gets RowID + Field1, and RowID + Field2, and
....
2 - create a query against the UNION query that you could then search for
terms using the approach you had before (i.e., use a form for the search
term, set the Selection Criterion in this/second query to look to the form).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I know you are abusolutely right!
It is set up such a way in an excel sheet, and I expressed what you just
said to the person who asked for it to be done..... and they are
determined
to keep it that way... I don't want to argue with the person because it is
my
job to do what they ask.

I know that when i would just run a query with [] input field.... that I
could just copy and paste that into the other fields in the OR rows...

Why won't that work in this situation? Is there a way to make it work?

Thanks for the input and help.

Greg

Greg

Based on your description, you are trying to get Access to do something
it
isn't designed for ...

.... or rather, give the data structure you described, you (and Access)
will
have to work extra hard to do what you seem to be trying to do.

In a well-normalized relational database, it would be very unusual to
need
to search more than ONE field. How is it that there are 5 fields that
might
have the search term you are seeking?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have table that has 5 fields that I want to be searched.
I create a form and an unbound control box. I also set the control box
after
update property to requery the form.

The form is based of a query where I put Like "*" &
[Forms]![Search]![TextBox] & "*" in the critera. I simiply put the same
line
in the or criteria for each field that i want to be searched.... but it
is
not working, only searchs the first field.

(this worked when I would prompt the user, but now its not working when
using the textbox as critera)

Thanks for the help
(I didn't know if to start a new post or not, so if I am wrong I am
sorry)

Greg