Extra Criteria Fields

C

Charles

Hey I have a list box that checks a box for criteria as you type it in
and requeries as you type. It works great and the user can type in any
part of the record and results update as the user types, but my one
problem is that there are not enough criteria fields in the design
mode(SQL edit mode) of the list box. I would like it to search even
more records but ive run out of slots for criteria! How can i
incresase the amount without haveing to type in additional crieteria in
SQL view because that will be a real hassle in comparison to useing the
nice query-looking format. I included the SQL for the box below so you
can see what I mean. Thanks in advance for any help!

Code:

SELECT Addresses.FamilyID, Addresses.FmlyName, Campers.FirstName,
Campers.MiddleName, Campers.LastName, Campers.Gender, Campers.Status,
Campers.OtherInformation
FROM Addresses INNER JOIN Campers ON Addresses.FamilyID =
Campers.FamilyID
GROUP BY Addresses.FamilyID, Addresses.FmlyName, Campers.FirstName,
Campers.MiddleName, Campers.LastName, Campers.Gender, Campers.Status,
Campers.OtherInformation, Addresses.EPhone, Addresses.Status,
Addresses.WorkPhone, Addresses.CellPhone, Addresses.HomePhone
HAVING (((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.EPhone) Like
"*"+[Forms]![FilterOne]![Text22]+"*") AND
((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.WorkPhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.CellPhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.HomePhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.FirstName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.MiddleName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.LastName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Addresses.FmlyName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Campers.OtherInformation) Like
"*"+[Forms]![FilterOne]![Text22]+"*") AND
((Addresses.Status)="Active"));


-Charles
 
K

KARL DEWEY

I did not follow all you said but if I need a whole lot of criteria then I
use a table. If I will be using the same criteria over and over I add a
field to flag for search (checkbox or "X") so I do not have to type it all
each time.
In the design view add the table to your query without joining. Put the
field name in the criteria row ( in brackets ) and if using the flagging add
an output field of the flag field and the correct criteria ( minus one for
checkbox or "X" ).

Charles said:
Hey I have a list box that checks a box for criteria as you type it in
and requeries as you type. It works great and the user can type in any
part of the record and results update as the user types, but my one
problem is that there are not enough criteria fields in the design
mode(SQL edit mode) of the list box. I would like it to search even
more records but ive run out of slots for criteria! How can i
incresase the amount without haveing to type in additional crieteria in
SQL view because that will be a real hassle in comparison to useing the
nice query-looking format. I included the SQL for the box below so you
can see what I mean. Thanks in advance for any help!

Code:

SELECT Addresses.FamilyID, Addresses.FmlyName, Campers.FirstName,
Campers.MiddleName, Campers.LastName, Campers.Gender, Campers.Status,
Campers.OtherInformation
FROM Addresses INNER JOIN Campers ON Addresses.FamilyID =
Campers.FamilyID
GROUP BY Addresses.FamilyID, Addresses.FmlyName, Campers.FirstName,
Campers.MiddleName, Campers.LastName, Campers.Gender, Campers.Status,
Campers.OtherInformation, Addresses.EPhone, Addresses.Status,
Addresses.WorkPhone, Addresses.CellPhone, Addresses.HomePhone
HAVING (((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.EPhone) Like
"*"+[Forms]![FilterOne]![Text22]+"*") AND
((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.WorkPhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.CellPhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.HomePhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.FirstName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.MiddleName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.LastName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Addresses.FmlyName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Campers.OtherInformation) Like
"*"+[Forms]![FilterOne]![Text22]+"*") AND
((Addresses.Status)="Active"));


-Charles
Click to expand...
 
K

KARL DEWEY

Select all your fields and put the table or other critera for one. Change to
SQL view and copy to Word. Copy the field list in the SELECT part of the
statement, omiting the one that has criteria, and paste in the WHERE part and
add a comma after the last field name. Copy the criteria part of the
statement and using the Replace in Word to replace the comma with OR and
your criteria.

Progress like this --
SELECT Accounting.ap_date, Accounting.Posted, Accounting.xx, Accounting.yy
FROM Accounting, AccountTime
WHERE [Accounting].[ap_date] Like "*" & [SOR] & "*";

SELECT Accounting.ap_date, Accounting.Posted, Accounting.xx, Accounting.yy
FROM Accounting, AccountTime
WHERE [Accounting].[ap_date] Like "*" & [SOR] & "*", Accounting.Posted,
Accounting.xx, Accounting.yy,;

SELECT Accounting.ap_date, Accounting.Posted, Accounting.xx, Accounting.yy
FROM Accounting, AccountTime
WHERE [Accounting].[ap_date] Like "*" & [SOR] & "*" OR Accounting.PostedLike
“*†& [SOR] & “*†OR Accounting.xxLike “*†& [SOR] & “*†OR Accounting.yyLike
“*†& [SOR] & “*â€;
 
J

John Spencer

If all you mean is that you want to add more rows to the criteria in the
grid design view, you can use the Insert: Rows from the menu.

You can insert multiple rows at one time, by
-- place the cursor over the left-most line of a criteria line. the cursor
should turn into a right-pointing arrow
-- drag down to select multiple rows
-- Select Insert: Rows from the menu.


Charles said:
Hey I have a list box that checks a box for criteria as you type it in
and requeries as you type. It works great and the user can type in any
part of the record and results update as the user types, but my one
problem is that there are not enough criteria fields in the design
mode(SQL edit mode) of the list box. I would like it to search even
more records but ive run out of slots for criteria! How can i
incresase the amount without haveing to type in additional crieteria in
SQL view because that will be a real hassle in comparison to useing the
nice query-looking format. I included the SQL for the box below so you
can see what I mean. Thanks in advance for any help!

Code:

SELECT Addresses.FamilyID, Addresses.FmlyName, Campers.FirstName,
Campers.MiddleName, Campers.LastName, Campers.Gender, Campers.Status,
Campers.OtherInformation
FROM Addresses INNER JOIN Campers ON Addresses.FamilyID =
Campers.FamilyID
GROUP BY Addresses.FamilyID, Addresses.FmlyName, Campers.FirstName,
Campers.MiddleName, Campers.LastName, Campers.Gender, Campers.Status,
Campers.OtherInformation, Addresses.EPhone, Addresses.Status,
Addresses.WorkPhone, Addresses.CellPhone, Addresses.HomePhone
HAVING (((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.EPhone) Like
"*"+[Forms]![FilterOne]![Text22]+"*") AND
((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.WorkPhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.CellPhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active") AND
((Addresses.HomePhone) Like "*"+[Forms]![FilterOne]![Text22]+"*")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.FirstName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.MiddleName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Campers.LastName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND ((Addresses.FmlyName)
Like "*"+[Forms]![FilterOne]![Text22]+"*") AND
((Campers.Status)="Active") AND ((Addresses.Status)="Active")) OR
(((Addresses.FamilyID)=[Campers].[FamilyID]) AND
((Campers.Status)="Active") AND ((Campers.OtherInformation) Like
"*"+[Forms]![FilterOne]![Text22]+"*") AND
((Addresses.Status)="Active"));


-Charles
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search memo field using criteria from a Form 7
HELP,how to show search result according to specifed criteria and selected fields 2

Top