Same Criteria, Multiple Fields

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

Guest

I'm created a QBF (query by form) that will search multiple fields for same
criteria. For example: I want search the Company Address, Primary Contact,
and CC fields for a the same name, say "Margaret"

Company | Contact | CC
ABC LA | Johnny | Sarah
ABC NY c/o Tom Slater | Tom Slater or Bob Fried | Margaret Hiffs
LMN | Margaret Tuttle | Roger Roberts
XYZ c/o Becky Thornbird | Bob Tucker | Liz Arwe

Now I know I would two records for Margaret, one for ABC NY & one for LMN
this is what I want.

Form: NameSearch, unbound box named [q_NameSearch]
Query: All fields with Company | Contact | CC fields having the following
criteria on different lines (an “orâ€): Like "*" & [Forms]![
NameSearch]![q_NameSearch] & "*"

When I run the query with form closed and get prompted for [
NameSearch]![q_NameSearch] it works fine. However when I do the search from
the form I get almost all the records (it excludes null fields) not the few I
need.
 
Lori said:
I'm created a QBF (query by form) that will search multiple fields
for same criteria. For example: I want search the Company Address,
Primary Contact, and CC fields for a the same name, say "Margaret"

Company | Contact | CC
ABC LA | Johnny | Sarah
ABC NY c/o Tom Slater | Tom Slater or Bob Fried | Margaret Hiffs
LMN | Margaret Tuttle | Roger Roberts
XYZ c/o Becky Thornbird | Bob Tucker | Liz Arwe

Now I know I would two records for Margaret, one for ABC NY & one for
LMN this is what I want.

Form: NameSearch, unbound box named [q_NameSearch]
Query: All fields with Company | Contact | CC fields having the
following criteria on different lines (an "or"): Like "*" & [Forms]![
NameSearch]![q_NameSearch] & "*"

When I run the query with form closed and get prompted for [
NameSearch]![q_NameSearch] it works fine. However when I do the
search from the form I get almost all the records (it excludes null
fields) not the few I need.
Click to expand...

In what event are you running the search from the form? The results you
report suggest that the value of the text box [q_NameSearch] is Null or
a zero-length string at the time the query is run.
 
Try

Like "*" & Nz([Forms]![NameSearch]![q_NameSearch],"*") & "*"

or

Like IIF(IsNull(([Forms]![NameSearch]![q_NameSearch]),"*", "*" &
[Forms]![NameSearch]![q_NameSearch] & "*")
 
Paul~

Thank. I am not trying to get nulls.

~Lori

Paul Overway said:
Try

Like "*" & Nz([Forms]![NameSearch]![q_NameSearch],"*") & "*"

or

Like IIF(IsNull(([Forms]![NameSearch]![q_NameSearch]),"*", "*" &
[Forms]![NameSearch]![q_NameSearch] & "*")

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Lori said:
I'm created a QBF (query by form) that will search multiple fields for
same
criteria. For example: I want search the Company Address, Primary
Contact,
and CC fields for a the same name, say "Margaret"

Company | Contact | CC
ABC LA | Johnny | Sarah
ABC NY c/o Tom Slater | Tom Slater or Bob Fried | Margaret Hiffs
LMN | Margaret Tuttle | Roger Roberts
XYZ c/o Becky Thornbird | Bob Tucker | Liz Arwe

Now I know I would two records for Margaret, one for ABC NY & one for LMN
this is what I want.

Form: NameSearch, unbound box named [q_NameSearch]
Query: All fields with Company | Contact | CC fields having the following
criteria on different lines (an "or"): Like "*" & [Forms]![
NameSearch]![q_NameSearch] & "*"

When I run the query with form closed and get prompted for [
NameSearch]![q_NameSearch] it works fine. However when I do the search
from
the form I get almost all the records (it excludes null fields) not the
few I
need.
Click to expand...
Click to expand...
 
Sorry Dirk~

The form NameSearch has unbound box [q_NameSearch] with a Hyperlink
Subaddress: Macro q_PeopleSearch_List

The macro has two actions:
OpenQuery: PeopleSearch
OpenForm: ListPeople

The query PeopleSearch has all fields display in the result with the
Company, Contact and CC fields having the following criteria on different
lines (an "or"):
Like "*" & [Forms]![NameSearch]![q_NameSearch] & "*"

The form ListPeople has a data source of the PeopleSearch query.

I enter Margaret into the [q_NameSearch] box click on the display quick list
button which runs the macro which opens the query, the query (now open the
back ground) display all records except null (again I do not want null
records in the result, but I shouldn't be getting 103 results, I should be
getting only 4.

When I run the query straight with all forms closed the query works fine.

I just ran the macro with all forms closed. I get a prompt for the first
field, Company entering Margaret I get my four expected names, then I get
another prompt for Contact, leave blank, I get 103 records, another prompt
for CC but I already have all the superfluous records.

Any suggestions?

~Lori


Dirk Goldgar said:
Lori said:
I'm created a QBF (query by form) that will search multiple fields
for same criteria. For example: I want search the Company Address,
Primary Contact, and CC fields for a the same name, say "Margaret"

Company | Contact | CC
ABC LA | Johnny | Sarah
ABC NY c/o Tom Slater | Tom Slater or Bob Fried | Margaret Hiffs
LMN | Margaret Tuttle | Roger Roberts
XYZ c/o Becky Thornbird | Bob Tucker | Liz Arwe

Now I know I would two records for Margaret, one for ABC NY & one for
LMN this is what I want.

Form: NameSearch, unbound box named [q_NameSearch]
Query: All fields with Company | Contact | CC fields having the
following criteria on different lines (an "or"): Like "*" & [Forms]![
NameSearch]![q_NameSearch] & "*"

When I run the query with form closed and get prompted for [
NameSearch]![q_NameSearch] it works fine. However when I do the
search from the form I get almost all the records (it excludes null
fields) not the few I need.
Click to expand...

In what event are you running the search from the form? The results you
report suggest that the value of the text box [q_NameSearch] is Null or
a zero-length string at the time the query is run.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
The examples I provided only return null if you don't enter any criteria.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Lori said:
Paul~

Thank. I am not trying to get nulls.

~Lori

Paul Overway said:
Try

Like "*" & Nz([Forms]![NameSearch]![q_NameSearch],"*") & "*"

or

Like IIF(IsNull(([Forms]![NameSearch]![q_NameSearch]),"*", "*" &
[Forms]![NameSearch]![q_NameSearch] & "*")

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Lori said:
I'm created a QBF (query by form) that will search multiple fields for
same
criteria. For example: I want search the Company Address, Primary
Contact,
and CC fields for a the same name, say "Margaret"

Company | Contact | CC
ABC LA | Johnny | Sarah
ABC NY c/o Tom Slater | Tom Slater or Bob Fried | Margaret Hiffs
LMN | Margaret Tuttle | Roger Roberts
XYZ c/o Becky Thornbird | Bob Tucker | Liz Arwe

Now I know I would two records for Margaret, one for ABC NY & one for
LMN
this is what I want.

Form: NameSearch, unbound box named [q_NameSearch]
Query: All fields with Company | Contact | CC fields having the
following
criteria on different lines (an "or"): Like "*" & [Forms]![
NameSearch]![q_NameSearch] & "*"

When I run the query with form closed and get prompted for [
NameSearch]![q_NameSearch] it works fine. However when I do the search
from
the form I get almost all the records (it excludes null fields) not the
few I
need.
Click to expand...
Click to expand...
Click to expand...
 
(comments inline)

Lori said:
The form NameSearch has unbound box [q_NameSearch] with a Hyperlink
Subaddress: Macro q_PeopleSearch_List
Click to expand...

I don't understand the purpose of the hyperlink subaddress, and I wonder
if that's what's messing you up. The values actually stored in
hyperlink fields are not identical to what is displayed and entered.
This text box should not be a hyperlink field.
The macro has two actions:
OpenQuery: PeopleSearch
OpenForm: ListPeople

The query PeopleSearch has all fields display in the result with the
Company, Contact and CC fields having the following criteria on
different lines (an "or"):
Like "*" & [Forms]![NameSearch]![q_NameSearch] & "*"

The form ListPeople has a data source of the PeopleSearch query.
Click to expand...

You need not open the query PeopleSearch at all. The form will query
its recordsource completely independently of the query datasheet you are
opening that way, so effectively you are running the query twice; once
with the OpenQuery and once with the OpenForm. Delete the OpenQuery
action from the macro. It's not actively hurting you, but it serves no
purpose.
I enter Margaret into the [q_NameSearch] box click on the display
quick list button which runs the macro which opens the query, the
query (now open the back ground) display all records except null
(again I do not want null records in the result, but I shouldn't be
getting 103 results, I should be getting only 4.
Click to expand...

I suspect -- but don't know for sure -- that this is because you've
designated q_NameSearch as a hyperlink field.
When I run the query straight with all forms closed the query works
fine.
Click to expand...

That would make sense, because now you are entering exactly the text
value to search for, not a hyperlink-formatted version of it.
I just ran the macro with all forms closed. I get a prompt for the
first field, Company entering Margaret I get my four expected names,
then I get another prompt for Contact, leave blank, I get 103
records, another prompt for CC but I already have all the superfluous
records.
Click to expand...

I'm not sure why you get multiple prompts for the same parameter, but
seeing that you are, your report makes sense. By leaving the second and
third prompts blank you get a query that is set up to pull records ....

WHERE Company Like "*Margaret*"
OR Contact Like "**"
OR CC Like "**"

Naturally, these criteria are going to pull all records except those
with Null in the Contact or CC fields.
 
Dirk~

THANKS! I created hyperlinks because I didn't like the ugly grey buttons.
It never occured to me that I "on click" a text box!

I moved the macro name over there and it works real nice.

Thanks also for the tip on not opening the query.

~Lori

Dirk Goldgar said:
(comments inline)

Lori said:
The form NameSearch has unbound box [q_NameSearch] with a Hyperlink
Subaddress: Macro q_PeopleSearch_List
Click to expand...

I don't understand the purpose of the hyperlink subaddress, and I wonder
if that's what's messing you up. The values actually stored in
hyperlink fields are not identical to what is displayed and entered.
This text box should not be a hyperlink field.
The macro has two actions:
OpenQuery: PeopleSearch
OpenForm: ListPeople

The query PeopleSearch has all fields display in the result with the
Company, Contact and CC fields having the following criteria on
different lines (an "or"):
Like "*" & [Forms]![NameSearch]![q_NameSearch] & "*"

The form ListPeople has a data source of the PeopleSearch query.
Click to expand...

You need not open the query PeopleSearch at all. The form will query
its recordsource completely independently of the query datasheet you are
opening that way, so effectively you are running the query twice; once
with the OpenQuery and once with the OpenForm. Delete the OpenQuery
action from the macro. It's not actively hurting you, but it serves no
purpose.
I enter Margaret into the [q_NameSearch] box click on the display
quick list button which runs the macro which opens the query, the
query (now open the back ground) display all records except null
(again I do not want null records in the result, but I shouldn't be
getting 103 results, I should be getting only 4.
Click to expand...

I suspect -- but don't know for sure -- that this is because you've
designated q_NameSearch as a hyperlink field.
When I run the query straight with all forms closed the query works
fine.
Click to expand...

That would make sense, because now you are entering exactly the text
value to search for, not a hyperlink-formatted version of it.
I just ran the macro with all forms closed. I get a prompt for the
first field, Company entering Margaret I get my four expected names,
then I get another prompt for Contact, leave blank, I get 103
records, another prompt for CC but I already have all the superfluous
records.
Click to expand...

I'm not sure why you get multiple prompts for the same parameter, but
seeing that you are, your report makes sense. By leaving the second and
third prompts blank you get a query that is set up to pull records ....

WHERE Company Like "*Margaret*"
OR Contact Like "**"
OR CC Like "**"

Naturally, these criteria are going to pull all records except those
with Null in the Contact or CC fields.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Paul~

My apologizes. I looked at the code real quick and didn't pay attention.
Since not entering criteria is already setup to return all records I'll just
file this away.

~Lori

Paul Overway said:
The examples I provided only return null if you don't enter any criteria.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Lori said:
Paul~

Thank. I am not trying to get nulls.

~Lori

Paul Overway said:
Try

Like "*" & Nz([Forms]![NameSearch]![q_NameSearch],"*") & "*"

or

Like IIF(IsNull(([Forms]![NameSearch]![q_NameSearch]),"*", "*" &
[Forms]![NameSearch]![q_NameSearch] & "*")

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I'm created a QBF (query by form) that will search multiple fields for
same
criteria. For example: I want search the Company Address, Primary
Contact,
and CC fields for a the same name, say "Margaret"

Company | Contact | CC
ABC LA | Johnny | Sarah
ABC NY c/o Tom Slater | Tom Slater or Bob Fried | Margaret Hiffs
LMN | Margaret Tuttle | Roger Roberts
XYZ c/o Becky Thornbird | Bob Tucker | Liz Arwe

Now I know I would two records for Margaret, one for ABC NY & one for
LMN
this is what I want.

Form: NameSearch, unbound box named [q_NameSearch]
Query: All fields with Company | Contact | CC fields having the
following
criteria on different lines (an "or"): Like "*" & [Forms]![
NameSearch]![q_NameSearch] & "*"

When I run the query with form closed and get prompted for [
NameSearch]![q_NameSearch] it works fine. However when I do the search
from
the form I get almost all the records (it excludes null fields) not the
few I
need.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top