multiple search criteria in one button

I am trying to make a form to search my 1 table of records by 3 different
criteria. I am trying to make it so a user can type either who the record was
sent by, who the record was sent to, and between what dates. The user only
needs to supply one criteria to search. Also, the form would only be for one
of 6 catagories in the table. For example, some of the records are "incoming
correspondences". The user would open the "search incoming correspondences"
form and enter either who it was sent by, sent to, or a date range. Any help
would be much appreciated. btw i am very new to access so I might not get
fancy language yet

On Mon, 29 Jun 2009 15:25:01 -0700, kbob
wrote:

I am trying to make a form to search my 1 table of records by 3 different
criteria. I am trying to make it so a user can type either who the record was
sent by, who the record was sent to, and between what dates. The user only
needs to supply one criteria to search. Also, the form would only be for one
of 6 catagories in the table. For example, some of the records are "incoming
correspondences". The user would open the "search incoming correspondences"
form and enter either who it was sent by, sent to, or a date range. Any help
would be much appreciated. btw i am very new to access so I might not get
fancy language yet

It's very hard to make any specific suggestions given that we know nothing
about the structure of your tables.

With only three criteria, you could use a Form with (say) two combo boxes to
select the sent to-received from people, and two textboxes txtFrom and txtTo,
and another combo box to select all the valid categories. You could use a
criterion like the following (I'm posting the SQL view which you'll need to
edit to match your table and fieldnames):

WHERE (SentBy = Forms!YourFormName!cboSentby OR Forms!YourFormName!cboSentBy
IS NULL)
AND (SentTo = Forms!YourFormName!cboSentTo OR Forms!YourFormName!cboSentTo IS
NULL)
AND Datefield = NZ(Forms!YourFormName!txtFrom, #1/1/100#)
AND DateField = NZ(Forms!YourFormName!txtTo, #12/31/9999#)
AND (Category = Forms!YourFormName!cboCategory OR
Forms!YourFormName!cboCategory IS NULL)
--

John W. Vinson [MVP]

hi john,
my table's structure is very simple. It has 5 columns and as many rows as
there are records. one of the columns is catagory, which has 6 choices. But i
am not trying to make 1 form to search, I am trying to make a search form for
each catagory (6 total search forms). Then I would put 4 text boxes: from,
to, and 2 for a date range. Is it a problem that I only have 1 table?

"John W. Vinson" wrote:

On Mon, 29 Jun 2009 15:25:01 -0700, kbob
wrote:

I am trying to make a form to search my 1 table of records by 3 different
criteria. I am trying to make it so a user can type either who the record was
sent by, who the record was sent to, and between what dates. The user only
needs to supply one criteria to search. Also, the form would only be for one
of 6 catagories in the table. For example, some of the records are "incoming
correspondences". The user would open the "search incoming correspondences"
form and enter either who it was sent by, sent to, or a date range. Any help
would be much appreciated. btw i am very new to access so I might not get
fancy language yet

It's very hard to make any specific suggestions given that we know nothing
about the structure of your tables.

With only three criteria, you could use a Form with (say) two combo boxes to
select the sent to-received from people, and two textboxes txtFrom and txtTo,
and another combo box to select all the valid categories. You could use a
criterion like the following (I'm posting the SQL view which you'll need to
edit to match your table and fieldnames):

WHERE (SentBy = Forms!YourFormName!cboSentby OR Forms!YourFormName!cboSentBy
IS NULL)
AND (SentTo = Forms!YourFormName!cboSentTo OR Forms!YourFormName!cboSentTo IS
NULL)
AND Datefield = NZ(Forms!YourFormName!txtFrom, #1/1/100#)
AND DateField = NZ(Forms!YourFormName!txtTo, #12/31/9999#)
AND (Category = Forms!YourFormName!cboCategory OR
Forms!YourFormName!cboCategory IS NULL)
--

John W. Vinson [MVP]

On Thu, 2 Jul 2009 09:11:01 -0700, kbob
wrote:

hi john,
my table's structure is very simple. It has 5 columns and as many rows as
there are records. one of the columns is catagory, which has 6 choices. But i
am not trying to make 1 form to search, I am trying to make a search form for
each catagory (6 total search forms). Then I would put 4 text boxes: from,
to, and 2 for a date range. Is it a problem that I only have 1 table?

I don't understand the advantage from the user's viewpoint of having six
forms; why not one form with a combo box or listbox to select the category??

But if you want that, use a Query for each form, with criteria for that form's
category as a literal text criterion; and textboxes named txtFrom, txtTo,
txtStartDate and txtEndDate. The criteria in the query would be
=[Forms]![YourFormName]![txtFrom] on the From field, similar on the To, and

= [Forms]![YourFormName]![txtStartDate] AND DateAdd("d", 1, [Forms]![YourFormName]![txtEndDate])

on the date field.
--

John W. Vinson [MVP]