Query to search entire record, not one field?

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

Guest

I have a form based on a table with 20 fields. My clients want the ability
to search all fields to find the record they are looking for. Is there a way
for me to create a form (with all 20 fields) where they can chose which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I need. I go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
 
To search based on a filed in a form that may or may not contain a value,
you'd use...

Like Forms![MySearchFormName]![MySearchFieldName] & "*"

Putting this in all the various fields in your query would pull values that
match if the user had made an entry. If no entry was made for a particular
field, then all the values in that field would be valid.
 
Thanks for your help Rick.
I understand the second part but am not sure about the first half.
My Table name is "Master"
My Search form is "Search" and is based on a different table with the same
fields as "Master" But I think it should be based of a query of "Master" ??
So I need a blank form that you can type in the information you want to
search by and then click search and all records with the matching information
will show up.
I tried to put in Like Forms! [Search]![State] & "&" but it put Forms! in []
and when I ran the query it gave me the pop up asking for Foms!Search!State!,
I undertand why I got the pop up but I don't want the pop up, I want to enter
the information into a blank form and then search.
Thanks again.

Rick B said:
To search based on a filed in a form that may or may not contain a value,
you'd use...

Like Forms![MySearchFormName]![MySearchFieldName] & "*"

Putting this in all the various fields in your query would pull values that
match if the user had made an entry. If no entry was made for a particular
field, then all the values in that field would be valid.

--
Rick B



consjoe said:
I have a form based on a table with 20 fields. My clients want the ability
to search all fields to find the record they are looking for. Is there a way
for me to create a form (with all 20 fields) where they can chose which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I need. I go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
Click to expand...
Click to expand...
 
Not sure what you are asking.

You need to build an unbound form.

This form will contain a blank UNBOUND field that the users can enter.

You can then reference each of these in your query.

--
Rick B



consjoe said:
Thanks for your help Rick.
I understand the second part but am not sure about the first half.
My Table name is "Master"
My Search form is "Search" and is based on a different table with the same
fields as "Master" But I think it should be based of a query of "Master" ??
So I need a blank form that you can type in the information you want to
search by and then click search and all records with the matching information
will show up.
I tried to put in Like Forms! [Search]![State] & "&" but it put Forms! in []
and when I ran the query it gave me the pop up asking for Foms!Search!State!,
I undertand why I got the pop up but I don't want the pop up, I want to enter
the information into a blank form and then search.
Thanks again.

Rick B said:
To search based on a filed in a form that may or may not contain a value,
you'd use...

Like Forms![MySearchFormName]![MySearchFieldName] & "*"

Putting this in all the various fields in your query would pull values that
match if the user had made an entry. If no entry was made for a particular
field, then all the values in that field would be valid.

--
Rick B



consjoe said:
I have a form based on a table with 20 fields. My clients want the ability
to search all fields to find the record they are looking for. Is
Click to expand...
there a
way
for me to create a form (with all 20 fields) where they can chose which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I need. I go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Okay, nevermind my last post. I have a form with unbound fields now, (that
was my problem).
The problem I am having now is this:
I put Like Forms![MySearchFormName]![MySearchFieldName] & "*"
in criteria for all fields in my Query.

For this example I used State Like [Forms]![SearchUnbound]![State] & "*"

There is not information for the "State" field in my master table for this
particular record.

I enter Company in my search form and leave State blank. Run my query and
do not get this result.

If I enter a state in my Master Record and then leave State blank (or enter
the State) in my search I will retrieve the record.

This problem is only when I have the
Like Forms![MySearchFormName]![MySearchFieldName] & "*"
criteria in the query for a field that has a null value in my master record.

How can I keep this criteria in every field if the master recrods contain
Null values?

Sorry if this is confusing, I am trying to explain the best I can.
Thanks so much. (Again)

Rick B said:
Not sure what you are asking.

You need to build an unbound form.

This form will contain a blank UNBOUND field that the users can enter.

You can then reference each of these in your query.

--
Rick B



consjoe said:
Thanks for your help Rick.
I understand the second part but am not sure about the first half.
My Table name is "Master"
My Search form is "Search" and is based on a different table with the same
fields as "Master" But I think it should be based of a query of "Master" ??
So I need a blank form that you can type in the information you want to
search by and then click search and all records with the matching information
will show up.
I tried to put in Like Forms! [Search]![State] & "&" but it put Forms! in []
and when I ran the query it gave me the pop up asking for Foms!Search!State!,
I undertand why I got the pop up but I don't want the pop up, I want to enter
the information into a blank form and then search.
Thanks again.

Rick B said:
To search based on a filed in a form that may or may not contain a value,
you'd use...

Like Forms![MySearchFormName]![MySearchFieldName] & "*"

Putting this in all the various fields in your query would pull values that
match if the user had made an entry. If no entry was made for a particular
field, then all the values in that field would be valid.

--
Rick B



I have a form based on a table with 20 fields. My clients want the
ability
to search all fields to find the record they are looking for. Is there a
way
for me to create a form (with all 20 fields) where they can chose which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I need. I go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I thought doing it with the "*" would also look for blank values. Maybe
not.

I have not tested this, but try something like this...

Is Null or Like [Forms]![SearchUnbound]![State] & "*"


--
Rick B



consjoe said:
Okay, nevermind my last post. I have a form with unbound fields now, (that
was my problem).
The problem I am having now is this:
I put Like Forms![MySearchFormName]![MySearchFieldName] & "*"
in criteria for all fields in my Query.

For this example I used State Like [Forms]![SearchUnbound]![State] & "*"

There is not information for the "State" field in my master table for this
particular record.

I enter Company in my search form and leave State blank. Run my query and
do not get this result.

If I enter a state in my Master Record and then leave State blank (or enter
the State) in my search I will retrieve the record.

This problem is only when I have the
Like Forms![MySearchFormName]![MySearchFieldName] & "*"
criteria in the query for a field that has a null value in my master record.

How can I keep this criteria in every field if the master recrods contain
Null values?

Sorry if this is confusing, I am trying to explain the best I can.
Thanks so much. (Again)

Rick B said:
Not sure what you are asking.

You need to build an unbound form.

This form will contain a blank UNBOUND field that the users can enter.

You can then reference each of these in your query.

--
Rick B



consjoe said:
Thanks for your help Rick.
I understand the second part but am not sure about the first half.
My Table name is "Master"
My Search form is "Search" and is based on a different table with the same
fields as "Master" But I think it should be based of a query of
Click to expand...
"Master"
??
So I need a blank form that you can type in the information you want to
search by and then click search and all records with the matching information
will show up.
I tried to put in Like Forms! [Search]![State] & "&" but it put Forms!
Click to expand...
in
[]
and when I ran the query it gave me the pop up asking for Foms!Search!State!,
I undertand why I got the pop up but I don't want the pop up, I want
Click to expand...
to
enter
the information into a blank form and then search.
Thanks again.

:

To search based on a filed in a form that may or may not contain a value,
you'd use...

Like Forms![MySearchFormName]![MySearchFieldName] & "*"

Putting this in all the various fields in your query would pull
Click to expand...
values
that
match if the user had made an entry. If no entry was made for a particular
field, then all the values in that field would be valid.

--
Rick B



I have a form based on a table with 20 fields. My clients want the
ability
to search all fields to find the record they are looking for. Is there a
way
for me to create a form (with all 20 fields) where they can chose which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I
Click to expand...
need. I
go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I thought so too.

I was trying to figure something out with an If statement:
If not Null then Like [Forms]![SearchUnbound]![State] & "*"
but that was not working. Is there somewhere else to key in an if statement
besides the criteria of the query?
Your below suggestion works for the state (text fields) example but not for
the date fields for some reason. I have a date of 08/08/2005 and as long as
I enter something to search by (ex. 07/05/2004) I get every record (either
with a date or blank because of the null) but it seams that it should only
give me the 07/05/2004 dates. Any ideas on the date not 'using' the like
function?
You are helping me very much, thank you again.


Rick B said:
I thought doing it with the "*" would also look for blank values. Maybe
not.

I have not tested this, but try something like this...

Is Null or Like [Forms]![SearchUnbound]![State] & "*"


--
Rick B



consjoe said:
Okay, nevermind my last post. I have a form with unbound fields now, (that
was my problem).
The problem I am having now is this:
I put Like Forms![MySearchFormName]![MySearchFieldName] & "*"
in criteria for all fields in my Query.

For this example I used State Like [Forms]![SearchUnbound]![State] & "*"

There is not information for the "State" field in my master table for this
particular record.

I enter Company in my search form and leave State blank. Run my query and
do not get this result.

If I enter a state in my Master Record and then leave State blank (or enter
the State) in my search I will retrieve the record.

This problem is only when I have the
Like Forms![MySearchFormName]![MySearchFieldName] & "*"
criteria in the query for a field that has a null value in my master record.

How can I keep this criteria in every field if the master recrods contain
Null values?

Sorry if this is confusing, I am trying to explain the best I can.
Thanks so much. (Again)

Rick B said:
Not sure what you are asking.

You need to build an unbound form.

This form will contain a blank UNBOUND field that the users can enter.

You can then reference each of these in your query.

--
Rick B



Thanks for your help Rick.
I understand the second part but am not sure about the first half.
My Table name is "Master"
My Search form is "Search" and is based on a different table with the same
fields as "Master" But I think it should be based of a query of "Master"
??
So I need a blank form that you can type in the information you want to
search by and then click search and all records with the matching
information
will show up.
I tried to put in Like Forms! [Search]![State] & "&" but it put Forms! in
[]
and when I ran the query it gave me the pop up asking for
Foms!Search!State!,
I undertand why I got the pop up but I don't want the pop up, I want to
enter
the information into a blank form and then search.
Thanks again.

:

To search based on a filed in a form that may or may not contain a
value,
you'd use...

Like Forms![MySearchFormName]![MySearchFieldName] & "*"

Putting this in all the various fields in your query would pull values
that
match if the user had made an entry. If no entry was made for a
particular
field, then all the values in that field would be valid.

--
Rick B



I have a form based on a table with 20 fields. My clients want the
ability
to search all fields to find the record they are looking for. Is
there a
way
for me to create a form (with all 20 fields) where they can chose
which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I need. I
go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, had the wrong date field (I have like 10 of them on this form)
It does work for the date also.
I think we (okay YOU) have solved the problem.
I will write back if I run into anything else but I think (hope) we are good.
THANK YOU SO MUCH!!

Rick B said:
I thought doing it with the "*" would also look for blank values. Maybe
not.

I have not tested this, but try something like this...

Is Null or Like [Forms]![SearchUnbound]![State] & "*"


--
Rick B



consjoe said:
Okay, nevermind my last post. I have a form with unbound fields now, (that
was my problem).
The problem I am having now is this:
I put Like Forms![MySearchFormName]![MySearchFieldName] & "*"
in criteria for all fields in my Query.

For this example I used State Like [Forms]![SearchUnbound]![State] & "*"

There is not information for the "State" field in my master table for this
particular record.

I enter Company in my search form and leave State blank. Run my query and
do not get this result.

If I enter a state in my Master Record and then leave State blank (or enter
the State) in my search I will retrieve the record.

This problem is only when I have the
Like Forms![MySearchFormName]![MySearchFieldName] & "*"
criteria in the query for a field that has a null value in my master record.

How can I keep this criteria in every field if the master recrods contain
Null values?

Sorry if this is confusing, I am trying to explain the best I can.
Thanks so much. (Again)

Rick B said:
Not sure what you are asking.

You need to build an unbound form.

This form will contain a blank UNBOUND field that the users can enter.

You can then reference each of these in your query.

--
Rick B



Thanks for your help Rick.
I understand the second part but am not sure about the first half.
My Table name is "Master"
My Search form is "Search" and is based on a different table with the same
fields as "Master" But I think it should be based of a query of "Master"
??
So I need a blank form that you can type in the information you want to
search by and then click search and all records with the matching
information
will show up.
I tried to put in Like Forms! [Search]![State] & "&" but it put Forms! in
[]
and when I ran the query it gave me the pop up asking for
Foms!Search!State!,
I undertand why I got the pop up but I don't want the pop up, I want to
enter
the information into a blank form and then search.
Thanks again.

:

To search based on a filed in a form that may or may not contain a
value,
you'd use...

Like Forms![MySearchFormName]![MySearchFieldName] & "*"

Putting this in all the various fields in your query would pull values
that
match if the user had made an entry. If no entry was made for a
particular
field, then all the values in that field would be valid.

--
Rick B



I have a form based on a table with 20 fields. My clients want the
ability
to search all fields to find the record they are looking for. Is
there a
way
for me to create a form (with all 20 fields) where they can chose
which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I need. I
go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top