Multiple Criteria Search

[Harry J.]

We are trying to develop a form where users can ask for information
about employee training. What the managers are asking for is a little
over my head, so hopefully someone can help me.

They want to..
(1) select the topic (or not)
(2) select the year and/or the quarter (or not)
(3) select group/department that received the training.

I can get this to work using a query - as long as all the fields are
filled in. However, the managers would like to be able to decide if
they want to make and entry or not. For example;
(a) they might want to see ALL training taken in the year 2003 for
their department. So the topic would be blank.
(b) they might want to see all the first aid training that a
department has had, from the beginning

Can anyone help me out here? What is the easiest way to go? (VBA?)
An example would help too.

THANKS in advance.

 

[John Vinson]

We are trying to develop a form where users can ask for information
about employee training. What the managers are asking for is a little
over my head, so hopefully someone can help me.

They want to..
(1) select the topic (or not)
(2) select the year and/or the quarter (or not)
(3) select group/department that received the training.

I can get this to work using a query - as long as all the fields are
filled in. However, the managers would like to be able to decide if
they want to make and entry or not. For example;
(a) they might want to see ALL training taken in the year 2003 for
their department. So the topic would be blank.
(b) they might want to see all the first aid training that a
department has had, from the beginning

Can anyone help me out here? What is the easiest way to go? (VBA?)
An example would help too.

THANKS in advance.

This question really should be in QUeries, not tablesdbdesign... but:

Try creating a small unbound form frmCrit with textboxes or combo
boxes for the different criteria. With only three of them, you can
create a Query with criteria on the fields like

=[Forms]![frmCrit]![cboTopic] OR [Forms]![frmCrit]![cboTopic] IS NULL

and similarly for the other criteria.

 

[Tim Ferguson]

(e-mail address removed) (Harry J.) wrote in

(1) select the topic (or not)
(2) select the year and/or the quarter (or not)
(3) select group/department that received the training.

You can do something like this. It is pretty boring to write, but is easy
to maintain and works as long as you have good validation of the actual
controls.

' start by building the SQL statement
strSQL = "SELECT something FROM somewhere "

' initialise the criterion string
strWhere = ""

' start by looking at the topic. If it's a combo,
' you need to make sure it's not empty
if len(cboTopic.Value)>0 then
if len(strWhere) = 0 then
strWhere = strWhere & " AND "
end if
strWhere = strWhere & _
" (Topic = """ & cboTopic.Value) & """) "
end if

' numbers don't need quotes around them
if len(txtYear)>0 Then
if len(strWhere) = 0 then
strWhere = strWhere & " AND "
end if
strWhere = strWhere & _
" (Year(ActionDate) = " & txtYear & ") "
end if

' Quarters are best shelled out using the Format
' function (as long as you are not using SQL Server)
' although you could do it the long way round with a
' BETWEEN clause if you were really stuck.
if Len(txtQuarter)>0 Then
if len(strWhere) = 0 then
strWhere = strWhere & " AND "
end if
strWhere = strWhere & _
" (Format(ActionDate,""q"")=""" & txtQuarter & """) "
end if

' For a list box, it's only empty if there is no selection
' in which case it's easier to check the ListIndex property
if lstGroup.ListIndex > -1 then
if len(strWhere) = 0 then
strWhere = strWhere & " AND "
end if
strWhere = strWhere & _
" (TrainingGroup = """ & lstGroup.Value & """) "
end if

' okay, if there were any criteria, get them into the main
' SQL statement
If len(strWhere) > 0 Then
strSQL = strSQL & " WHERE " & strWhere
End If


.... and so on. There are neater ways of getting the " AND " bits right, but
this is probably the most obvious. There is an example of this in the
Northwind database somewhere.

Hope that helps


Tim F

 

[prabha]

Hi,

I think you want to use: ACC2000: How to Use the Query by Form (QBF)
Technique
http://support.microsoft.com/default.aspx?scid=kb;en-us;209645

In the above article they use 2 parameters. You can expand it to use more
than 2.

So in your case the Criterias would be:

Field: [Group/Department]
Sort: Ascending
Criteria: Forms![your Form]![Dept]

Field: [year and quarter]
Sort: Ascending
Criteria: Forms![your Form]![year and quarter] OR Forms![your Form]![topic]
Is Null

Field: [topic]
Sort: Ascending
Criteria: Forms![your Form]![topic] OR Forms![your Form]![year and quarter]
Is Null

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| From: (e-mail address removed) (Harry J.)
| Newsgroups: microsoft.public.access.tablesdbdesign
| Subject: Multiple Criteria Search
| Date: 24 Feb 2004 12:06:59 -0800
| Organization: http://groups.google.com
| Lines: 21
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 65.244.107.4
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1077653219 31990 127.0.0.1 (24 Feb 2004
20:06:59 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Tue, 24 Feb 2004 20:06:59 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!newsfee
d01.sul.t-online.de!t-online.de!fu-berlin.de!postnews1.google.com!not-for-ma
il
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:76264
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| We are trying to develop a form where users can ask for information
| about employee training. What the managers are asking for is a little
| over my head, so hopefully someone can help me.
|
| They want to..
| (1) select the topic (or not)
| (2) select the year and/or the quarter (or not)
| (3) select group/department that received the training.
|
| I can get this to work using a query - as long as all the fields are
| filled in. However, the managers would like to be able to decide if
| they want to make and entry or not. For example;
| (a) they might want to see ALL training taken in the year 2003 for
| their department. So the topic would be blank.
| (b) they might want to see all the first aid training that a
| department has had, from the beginning
|
| Can anyone help me out here? What is the easiest way to go? (VBA?)
| An example would help too.
|
| THANKS in advance.
|

 

[Larry Daugherty]

Hi,

It's pretty risky trying to meet needs you don't understand. I'd really
push for clarification before investing too much time and effort in a
project unless it's under the guise of "prototyping".

My usual solution to similar requirements is to create a "launcher" form to
gather the criteria arguments. Usually the users are willing to accept the
constraint that they must present the beginning of any string to match
(usually case insensitive). If they enter nothing then anything that
matches the fields that have arguments will be returned. The text boxes on
the "launcher" will hold the initial part of the search element. The
criteria line of the query will refer back to the textbox on the "launcher"
and follow it with an asterisk. Once you get it working one way your users
may get enthusiastic and want several additional functions.

Note that I would not turn it over to the users while allowing them to run
queries that might be updateable. I would get the query running and then
pull it into a report so that it isn't visible as a named query and only
present the data in report format thereafter.

Note that your post really had nothing to do with tables design.

HTH