473,883 Members | 2,137 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Create a multiple parameter search form for a query

I need to figure out how to create a user interface to search a query,
but here's the bad part...I need to account for criteria on at least 7
of the fields. Here's what I'm thinking I need to do:

Create an unbound form with unbound fields for all 7 of the fields in
the query. Then in the query create parameters that refer to those
fields.

But it's not that simple because the user needs the flexibility of
filling in as many or as few of the fields as they want. For example,
they may have "account number", a "customer agent number" and a "date
opened" and an "account type" to search by, or maybe they want to see
all accounts of a certain type only.

Is there any easier way to do this? All I can picture is writing lines
and lines of criteria for all the combinations of "ANDs" and/or "ORs"
that this would require. If there's a different road to go down, in VB
or something, I'm all for it, anything that could be cleaner than
this...

Jun 30 '06 #1
2 9454
"angie" <ub*******@hotm ail.com> wrote in
news:11******** **************@ m73g2000cwd.goo glegroups.com:
I need to figure out how to create a user interface to search
a query, but here's the bad part...I need to account for
criteria on at least 7 of the fields. Here's what I'm
thinking I need to do:

Create an unbound form with unbound fields for all 7 of the
fields in the query. Then in the query create parameters that
refer to those fields.

But it's not that simple because the user needs the
flexibility of filling in as many or as few of the fields as
they want. For example, they may have "account number", a
"customer agent number" and a "date opened" and an "account
type" to search by, or maybe they want to see all accounts of
a certain type only.

Is there any easier way to do this? All I can picture is
writing lines and lines of criteria for all the combinations
of "ANDs" and/or "ORs" that this would require. If there's a
different road to go down, in VB or something, I'm all for it,
anything that could be cleaner than this...

Do you need to filter the query, or can you filter the
form/report which has the query as a recordsource?

If you can, it's easier.

Either way, build an unbound form with the controls, And/Or
selectors and a "Do It" button.
In the OnClick Event for the button, build a SQL where clause
based on the contents of the fields and the setting of the AndOr
selectors.

Dim strWhere as string
If len(me.txtLastn ame) & "" > 0 then
strWhere = strWhere & iif(me.Andor1 = "And", " and ", " OR
")
strWhere = strWhere & "[table].[lastname] = """ & txtLastname
& """"
end if
If len(me.txtFirst name) & "" > 0 then
strWhere = strWhere & iif(me.Andor1 = "And", " and ", " OR
")
strWhere = strWhere & "[table].[Firsttname] = """ &
txtFirstname & """"
end if

.....
' when done strip off the first and /or
strWhere = mid(strWhere,5)

'If you are opening a form or report, just pass the whereclause
as a parameter in the relevant
DoCmd.Openform "formname",,,st rWhere

If it's a query you have to create a recordset and muck around
with the full SQL string. How this is done depends on how you
want to use the query, ask if you need to.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 30 '06 #2
On your Prompts form, you should have a [Submit] button that will have
an On-click event to hold some VBA code.

Dim qd As DAO.QueryDef
Dim Whereclause As String
Dim ssql As String
Dim Assignment As Integer

ssql = "SELECT * From table"

IF me!AccountNumbe r Not NULL Then
Whereclause = " WHERE AccountNumber = " & Me!AccountNumbe r
End If

IF CustomerAgent Not NULL then
IF Whereclause is NULL Then
whereclause = " WHERE CustomerAgent = " & me!CustomerAgen t
Else
Whereclause = " AND CustomerAgent = " & me!CustomerAgen t
End IF
END IF

' Make IF statements for each field on the Prompts form.
' At the end you will have a Whereclause that has entries for those
fields that were filled in; or possibly Whereclause will be NULL.

IF Whereclause Not NULL then
ssql = ssql & Whereclause
End IF

'Then execute the ssql or save it as a query and then do whatever other
VBA code you want.

Jun 30 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2776
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with each having 3 fields each, their own PK; the FK back to the parent table; and the unique data for that table. There is a one to many relation between the parent and each of the 9 child rows. Each child table has between 100,000 and 300,000
2
4780
by: dskillingstad | last post by:
I'm trying to set up a parameter query based on an unbound form. This search form has about 5 text boxes where the user can type in values within each box and search a specific table based on the values of those 5 text boxes. One of two of these boxes are based on date. The obvious part (being the novice that I am) I'm stuck on is having the query search based on 1 to 5 search values? In other words, I need the query to search based on...
4
3761
by: Dave Edwards | last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot figure out how to fill a dataset with the same query but run against multiple SQL servers, the query , table structure and username, password etc will be exactly the same for each server, the only thing that will change is the server name. Idealy I would like to get the server names from a seperate dataset so there could be any number of servers, allthough in...
3
2154
by: RoadRunner | last post by:
I have a form that has 6 command buttons on it. Each command button runs a different query with a wildcard parameter value that looks in 6 different tables in 6 different linked databases. None of the databases are joined or have anything in common. Right now the users have to key in the same lookup string for every button. Is there a way to have a text box on the form, where the user can key the wildcard text string and then click on...
9
16111
by: lightning | last post by:
Hi all, I'm not very conversant with the vocabulary of Access, so please ask for clarification if necessary... I am trying to build a search form that allows combinations of search terms. For example, let's say I have three controls on my form; year, keyword, location. Entering a valid value for all three gives expected results from the dataset. However, I'd like to interpret a blank field in the search form as ignoring that constraint....
1
2635
by: almu | last post by:
Hi all, I need help to build a set of queries in PHP/MySQL for a multiple-parameter search form, whereby the user will be able to search any number of ways (upto seven) Thanks
6
9436
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate report" button located on my form to print all of the list box values (that have been updated via selection from combo boxes) from the form to the report. I've tried using a macro with the code: Macro Name: cmdGenerateReport : On Click Action:...
3
1812
by: vljones | last post by:
I am either having an extended blond moment or have been looking at this too long. This is a basic question but I simply cannot get my head around the solution. I have a Search form with grouped radio buttons. Depending on the selection a different query will run. The problem I am incurring is when I am querying more than one table. As an example: SELECT tbl_Claim.Txt_InsuredName, tbl_Claim.Date_DOL, tbl_Claim.Mem_PolNo,...
18
3120
by: sweeneye | last post by:
Hi, I'm basing a query on the variables used in a form. The database contains lots of problems, say with a computer and a tick box for the apropriate component like monitor, keyboard, mouse etc. and each has several eg problem 1 has a monitor and mouse problem My form then has what to look for tick boxes (tri-state) for each of these. I want my query to include all monitor problems when that is ticked and all monitor and mouse problems...
0
9948
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10767
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7980
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6008
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4623
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4230
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3242
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.