Input one criteria for searching 5 fields containing the same data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear Friends,

I have a table containing 5 fields for marking customers' address, i.e.
Address line 1
Address line 2
Address line 3
Address line 4
Address line 4
The users input the same street name in different address line. For
example, if I want to find all the customers living in Queen's Road, I am
using query to search for the customers. I input once and copy and paste the
first input to the subsequent pop up input box in order to search all
customers living in Queen's Road. Is there any way that I just input once and
automatically all the five fields are seached with the input criteria?

Thanks

Fanny
 
You can try concatenate all the fields together and search the result:

WHERE [Field1] & [Field2] & [Field3] & [Field4] & [Field5] Like "*" &
Forms!frmSearch!txtSearchFor & "*"

I much prefer controls on forms for entering parameter values.
 
Dear Mr Duane Hookom,

I am new user to Access. Please show me how to do it.

Tks.

Fanny

Duane Hookom said:
You can try concatenate all the fields together and search the result:

WHERE [Field1] & [Field2] & [Field3] & [Field4] & [Field5] Like "*" &
Forms!frmSearch!txtSearchFor & "*"

I much prefer controls on forms for entering parameter values.
--
Duane Hookom
MS Access MVP

Fanny said:
Dear Friends,

I have a table containing 5 fields for marking customers' address, i.e.
Address line 1
Address line 2
Address line 3
Address line 4
Address line 4
The users input the same street name in different address line. For
example, if I want to find all the customers living in Queen's Road, I am
using query to search for the customers. I input once and copy and paste
the
first input to the subsequent pop up input box in order to search all
customers living in Queen's Road. Is there any way that I just input once
and
automatically all the five fields are seached with the input criteria?

Thanks

Fanny
Click to expand...
Click to expand...
 
You create a form named frmSearch with a single text box named txtSearchFor.
Then you create a query based on the table/query you want to search. Create
a new column in the query with an expression like:
SearchText: [Field1] & [Field2] & [Field3] & [Field4] & [Field5]
Substitute your field names above.

Then set the criteria under this new column to:
Like "*" & Forms!frmSearch!txtSearchFor & "*"
Open the form and enter a value in the text box that you want to search for.
Then display the datasheet of the query.
--
Duane Hookom
MS Access MVP


Fanny said:
Dear Mr Duane Hookom,

I am new user to Access. Please show me how to do it.

Tks.

Fanny

Duane Hookom said:
You can try concatenate all the fields together and search the result:

WHERE [Field1] & [Field2] & [Field3] & [Field4] & [Field5] Like "*" &
Forms!frmSearch!txtSearchFor & "*"

I much prefer controls on forms for entering parameter values.
--
Duane Hookom
MS Access MVP

Fanny said:
Dear Friends,

I have a table containing 5 fields for marking customers' address, i.e.
Address line 1
Address line 2
Address line 3
Address line 4
Address line 4
The users input the same street name in different address line. For
example, if I want to find all the customers living in Queen's Road, I
am
using query to search for the customers. I input once and copy and
paste
the
first input to the subsequent pop up input box in order to search all
customers living in Queen's Road. Is there any way that I just input
once
and
automatically all the five fields are seached with the input criteria?

Thanks

Fanny
Click to expand...
Click to expand...
Click to expand...
 
It sounds as if you are using a parameter query. If so, you can use the
same parameter against all the fields. Just type the exact same name for
the parameter under each of your fields. You will have to stairstep the
criteria to get the OR. In the query grid this would look something like
the following. The (number) just indicates the different criteria lines.

Field: AddressLine1
Criteria(1): [Find what?]
Criteria(2): <Blank>
Criteria(3): <Blank>

Field: AddressLine2
Criteria(1): <Blank>
Criteria(2): [Find what?]
Criteria(3): <Blank>

Field: AddressLine3
Criteria(1): <Blank>
Criteria(2): <Blank>
Criteria(3): [Find what?]
 
Dear Duane,

Thanks. Your suggestion save a lot of work for me. But I will pay effort to
learn the form.

Thanks so much

Fanny

Duane Hookom said:
You create a form named frmSearch with a single text box named txtSearchFor.
Then you create a query based on the table/query you want to search. Create
a new column in the query with an expression like:
SearchText: [Field1] & [Field2] & [Field3] & [Field4] & [Field5]
Substitute your field names above.

Then set the criteria under this new column to:
Like "*" & Forms!frmSearch!txtSearchFor & "*"
Open the form and enter a value in the text box that you want to search for.
Then display the datasheet of the query.
--
Duane Hookom
MS Access MVP


Fanny said:
Dear Mr Duane Hookom,

I am new user to Access. Please show me how to do it.

Tks.

Fanny

Duane Hookom said:
You can try concatenate all the fields together and search the result:

WHERE [Field1] & [Field2] & [Field3] & [Field4] & [Field5] Like "*" &
Forms!frmSearch!txtSearchFor & "*"

I much prefer controls on forms for entering parameter values.
--
Duane Hookom
MS Access MVP

Dear Friends,

I have a table containing 5 fields for marking customers' address, i.e.
Address line 1
Address line 2
Address line 3
Address line 4
Address line 4
The users input the same street name in different address line. For
example, if I want to find all the customers living in Queen's Road, I
am
using query to search for the customers. I input once and copy and
paste
the
first input to the subsequent pop up input box in order to search all
customers living in Queen's Road. Is there any way that I just input
once
and
automatically all the five fields are seached with the input criteria?

Thanks

Fanny
Click to expand...
Click to expand...
Click to expand...
 
Back
Top