Same Criteria, Multiple Fields

[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.

 

[Dirk Goldgar]

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.

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.

 

[Paul Overway]

Try

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

or

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

 

[Guest]

Paul~

Thank. I am not trying to get nulls.

~Lori

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.

 

[Guest]

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

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.

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)

 

[Paul Overway]

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

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

Paul~

Thank. I am not trying to get nulls.

~Lori

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.

 

[Dirk Goldgar]

(comments inline)

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

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.

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.

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.

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.

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.

 

[Guest]

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

(comments inline)

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

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.

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.

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.

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.

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)

 

[Guest]

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

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

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

Paul~

Thank. I am not trying to get nulls.

~Lori

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.