Searching Multiple Fields in a query

[BrianPaul]

I already know how to search through a query for values using the like
statement in a single field and it returning the records that contain that
field.

My question would be, If I have 3 Fields, telephone Number, Fax Number, Cell
Number, How Would I set up in the query to enter a number I was looking for
and it displaying any records that matched the search criteria. Thanks,

 

[GM Leonric]

I already know how to search through a query for values using the like
statement in a single field and it returning the records that contain that
field.

My question would be, If I have 3 Fields, telephone Number, Fax Number, Cell
Number, How Would I set up in the query to enter a number I was looking for
and it displaying any records that matched the search criteria.  Thanks,

You can do this in the query grid view. Under each of the columns for
the three phone fields you need to put your LIKE statement in the
Criteria rows. The key to getting it to search and find it in any of
the fields is making sure that you place each like statement on a
different row. You'll see that there is a "Criteria" row then an "Or"
row then a bunch more rows with no heading. If you put each of the
LIKE statements on separate rows you are saying match field1 or field2
or field3. If your criteria all go on the same row, you are saying
that all three fields have to have that same value for a record to
match.

You can also accomplish this in SQL by using a statement like this:
SELECT Contacts.[Name], Contacts.[HomePhone], Contacts.[MobilePhone],
Contacts.[FaxNumber]
FROM Contacts
WHERE Contacts.[HomePhone] Like "623*" OR Contacts.[MobilePhone] Like
"623*" OR Contacts.[FaxNumber] Like "623*";

This will find any contacts with Home, Mobile or Fax Numbers starting
with 623.

Keven Denen

 

[BrianPaul]

I dont think you understood my question, Here is one of my like statements.

Like "*" & [Type anything you wish to search] & "*"

So its promted by the User when the query runs for the data that is inputed,
I could try my statement in the code you provided and see if that works,
Thanks.

I already know how to search through a query for values using the like
statement in a single field and it returning the records that contain that
field.

My question would be, If I have 3 Fields, telephone Number, Fax Number, Cell
Number, How Would I set up in the query to enter a number I was looking for
and it displaying any records that matched the search criteria. Thanks,

You can do this in the query grid view. Under each of the columns for
the three phone fields you need to put your LIKE statement in the
Criteria rows. The key to getting it to search and find it in any of
the fields is making sure that you place each like statement on a
different row. You'll see that there is a "Criteria" row then an "Or"
row then a bunch more rows with no heading. If you put each of the
LIKE statements on separate rows you are saying match field1 or field2
or field3. If your criteria all go on the same row, you are saying
that all three fields have to have that same value for a record to
match.

You can also accomplish this in SQL by using a statement like this:
SELECT Contacts.[Name], Contacts.[HomePhone], Contacts.[MobilePhone],
Contacts.[FaxNumber]
FROM Contacts
WHERE Contacts.[HomePhone] Like "623*" OR Contacts.[MobilePhone] Like
"623*" OR Contacts.[FaxNumber] Like "623*";

This will find any contacts with Home, Mobile or Fax Numbers starting
with 623.

Keven Denen

 

[BrianPaul]

SELECT test.Scripture, test.Scripture1, test.Scripture2, test.Scripture3,
test.Scripture4, test.Scripture5, test.Scripture6, test.Scripture7,
test.Scripture8
FROM test
WHERE (((test.Scripture) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture1) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture2) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture3) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture4) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture5) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture6) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture7) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture8) Like "*" & [Type anything you wish to search] &
"*"));


I did this in the app I was working with, It worked Find and I didnt have to
go to SQL view to create it. I just didnt think the OR would work that way,
Thanks

I dont think you understood my question, Here is one of my like statements.

Like "*" & [Type anything you wish to search] & "*"

So its promted by the User when the query runs for the data that is inputed,
I could try my statement in the code you provided and see if that works,
Thanks.

I already know how to search through a query for values using the like
statement in a single field and it returning the records that contain that
field.

My question would be, If I have 3 Fields, telephone Number, Fax Number, Cell
Number, How Would I set up in the query to enter a number I was looking for
and it displaying any records that matched the search criteria. Thanks,

You can do this in the query grid view. Under each of the columns for
the three phone fields you need to put your LIKE statement in the
Criteria rows. The key to getting it to search and find it in any of
the fields is making sure that you place each like statement on a
different row. You'll see that there is a "Criteria" row then an "Or"
row then a bunch more rows with no heading. If you put each of the
LIKE statements on separate rows you are saying match field1 or field2
or field3. If your criteria all go on the same row, you are saying
that all three fields have to have that same value for a record to
match.

You can also accomplish this in SQL by using a statement like this:
SELECT Contacts.[Name], Contacts.[HomePhone], Contacts.[MobilePhone],
Contacts.[FaxNumber]
FROM Contacts
WHERE Contacts.[HomePhone] Like "623*" OR Contacts.[MobilePhone] Like
"623*" OR Contacts.[FaxNumber] Like "623*";

This will find any contacts with Home, Mobile or Fax Numbers starting
with 623.

Keven Denen

 

[BrianPaul]

I did better than that, I redone the database and did just that, Added a
scriptureID then created a subform, Life got easier and it synched up fine.
I just cut and pasted the scriptures in so I didnt loose much time when I
created the new table. Thanks for the guidance, sorry didnt get back with
you sooner.

I saw in your next post that your existing table is TblSubject. So your
tables should look something like:

TblSubject
SubjectID
<whatever other fields you have>

TblScripture
ScriptureID
SubjectID
ScriptureVerse
etc

Steve
(e-mail address removed)

Brian,

You would do well to add a Scripture table to your database rather tha
have Scripture, Scripture1, Scripture2, etc in your existing table. Your
tables would look something like:
TblExisting
EXistingID
<whatever other fields you have>

TblScripture
ScriptureID
ExistingID
ScriptureVerse
etc

Steve
(e-mail address removed)

SELECT test.Scripture, test.Scripture1, test.Scripture2, test.Scripture3,
test.Scripture4, test.Scripture5, test.Scripture6, test.Scripture7,
test.Scripture8
FROM test
WHERE (((test.Scripture) Like "*" & [Type anything you wish to search] &
"*")) OR (((test.Scripture1) Like "*" & [Type anything you wish to
search] &
"*")) OR (((test.Scripture2) Like "*" & [Type anything you wish to
search] &
"*")) OR (((test.Scripture3) Like "*" & [Type anything you wish to
search] &
"*")) OR (((test.Scripture4) Like "*" & [Type anything you wish to
search] &
"*")) OR (((test.Scripture5) Like "*" & [Type anything you wish to
search] &
"*")) OR (((test.Scripture6) Like "*" & [Type anything you wish to
search] &
"*")) OR (((test.Scripture7) Like "*" & [Type anything you wish to
search] &
"*")) OR (((test.Scripture8) Like "*" & [Type anything you wish to
search] &
"*"));


I did this in the app I was working with, It worked Find and I didnt have
to
go to SQL view to create it. I just didnt think the OR would work that
way,
Thanks

:

I dont think you understood my question, Here is one of my like
statements.

Like "*" & [Type anything you wish to search] & "*"

So its promted by the User when the query runs for the data that is
inputed,
I could try my statement in the code you provided and see if that works,
Thanks.



:

On Jul 12, 2:40 pm, BrianPaul <[email protected]>
wrote:
I already know how to search through a query for values using the
like
statement in a single field and it returning the records that
contain that
field.

My question would be, If I have 3 Fields, telephone Number, Fax
Number, Cell
Number, How Would I set up in the query to enter a number I was
looking for
and it displaying any records that matched the search criteria.
Thanks,

You can do this in the query grid view. Under each of the columns for
the three phone fields you need to put your LIKE statement in the
Criteria rows. The key to getting it to search and find it in any of
the fields is making sure that you place each like statement on a
different row. You'll see that there is a "Criteria" row then an "Or"
row then a bunch more rows with no heading. If you put each of the
LIKE statements on separate rows you are saying match field1 or field2
or field3. If your criteria all go on the same row, you are saying
that all three fields have to have that same value for a record to
match.

You can also accomplish this in SQL by using a statement like this:
SELECT Contacts.[Name], Contacts.[HomePhone], Contacts.[MobilePhone],
Contacts.[FaxNumber]
FROM Contacts
WHERE Contacts.[HomePhone] Like "623*" OR Contacts.[MobilePhone] Like
"623*" OR Contacts.[FaxNumber] Like "623*";

This will find any contacts with Home, Mobile or Fax Numbers starting
with 623.

Keven Denen