473,692 Members | 2,557 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select Query Criteria from user form

I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and icluding
the selected date or all records on or after the selected date

The user selects either "=", >=" or "<=" from a combo box and then a
date from another combobox. The combination of thse two choices is
then set in an unbound textbox so for example the result in the unbound
textbox could be =01/01/2006 or >=01/02/2006 or <=01/03/2005.

Upon pressing a Command button the tableview of a form is opened whose
record source is a select query.

If I set the Criteria in the select query to the date in the form's
date combox the results are displayed
e.g. Criteria =[Forms]![StatementGBP]![INST_DATE]

However I'm trying to set the criteria to either the value in the
unbound textbox or the combination of the two combo boxes so that the
results will be for whichever criteria the user specified i.e.
"=01/01/2006" or ">=01/02/2006" or "<=01/03/2005".

The Criteria ought to be " =[Forms]![StatementGBP]![StatusBox]" where
Statusbox is the name of the unbound control

I have also tried "Like (([Forms]![StatementGBP]![ChoiceBox]) &
([Forms]![StatementGBP]![INST_DATE]))" where both of the comboox values
are concatenated.

Whatever I try doesn't seem to work, I have tried various formatting
functions within the select query, all to no avail. Does anyone know
the solution?

Dec 7 '06 #1
2 5732
The value of a text box on a form can be used as a parameter in a query.
However, you cannot use text boxes to supply operators for the query.

There are various workarounds. If you drop the combo, you could do some
stuff such as:
Between Nz([Forms]![StatementGBP]![INST_DATE],
[Forms]![StatementGBP]![End_DATE])
And
Nz([Forms]![StatementGBP]![End_DATE],[Forms]![StatementGBP]![INST_DATE])
The way that works is that if the user supplies both dates, it treats it as
between, but if they supply only one, it treats as just that date.

Another alternative is to get the to enter both dates the same if they just
want one date, and then use:
WHERE (([Forms]![StatementGBP]![INST_DATE] Is Null)
OR ([MyDateField] >= [Forms]![StatementGBP]![INST_DATE]))
AND (([Forms]![StatementGBP]![End_DATE] Is Null)
OR ([MyDateField] <= ([Forms]![StatementGBP]![End_DATE]))
This one works like this:
- start date only: all records from this date onwards;
- end date only: all records up to this date;
- both dates: only records between the 2 dates;
- no dates: all records.
To get one date only, use enters the same start and end date.

HOWEVER, this whole approach gets very tedious, inefficient, and difficult
to maintain when there are lots of filtering options. A better option is to
build a Filter string form only the boxes where the user entered something.
There's an example of how to do that in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Roughton" <mr*******@qb e-europe.comwrote in message
news:11******** **************@ n67g2000cwd.goo glegroups.com.. .
>I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and icluding
the selected date or all records on or after the selected date

The user selects either "=", >=" or "<=" from a combo box and then a
date from another combobox. The combination of thse two choices is
then set in an unbound textbox so for example the result in the unbound
textbox could be =01/01/2006 or >=01/02/2006 or <=01/03/2005.

Upon pressing a Command button the tableview of a form is opened whose
record source is a select query.

If I set the Criteria in the select query to the date in the form's
date combox the results are displayed
e.g. Criteria =[Forms]![StatementGBP]![INST_DATE]

However I'm trying to set the criteria to either the value in the
unbound textbox or the combination of the two combo boxes so that the
results will be for whichever criteria the user specified i.e.
"=01/01/2006" or ">=01/02/2006" or "<=01/03/2005".

The Criteria ought to be " =[Forms]![StatementGBP]![StatusBox]" where
Statusbox is the name of the unbound control

I have also tried "Like (([Forms]![StatementGBP]![ChoiceBox]) &
([Forms]![StatementGBP]![INST_DATE]))" where both of the comboox values
are concatenated.

Whatever I try doesn't seem to work, I have tried various formatting
functions within the select query, all to no avail. Does anyone know
the solution?

Dec 7 '06 #2
Allen,

Many thanks for your suggestion of using two date fields. I had hoped
that there would be a way to incorporate the operators as that would
have been ideal from the end usrs point of view. However your
suggestion will work just fine and has provided a resolution to my
problem.

Thanks for your input, which as ever, is accurate and most helpful.

Allen Browne wrote:
The value of a text box on a form can be used as a parameter in a query.
However, you cannot use text boxes to supply operators for the query.

There are various workarounds. If you drop the combo, you could do some
stuff such as:
Between Nz([Forms]![StatementGBP]![INST_DATE],
[Forms]![StatementGBP]![End_DATE])
And
Nz([Forms]![StatementGBP]![End_DATE],[Forms]![StatementGBP]![INST_DATE])
The way that works is that if the user supplies both dates, it treats it as
between, but if they supply only one, it treats as just that date.

Another alternative is to get the to enter both dates the same if they just
want one date, and then use:
WHERE (([Forms]![StatementGBP]![INST_DATE] Is Null)
OR ([MyDateField] >= [Forms]![StatementGBP]![INST_DATE]))
AND (([Forms]![StatementGBP]![End_DATE] Is Null)
OR ([MyDateField] <= ([Forms]![StatementGBP]![End_DATE]))
This one works like this:
- start date only: all records from this date onwards;
- end date only: all records up to this date;
- both dates: only records between the 2 dates;
- no dates: all records.
To get one date only, use enters the same start and end date.

HOWEVER, this whole approach gets very tedious, inefficient, and difficult
to maintain when there are lots of filtering options. A better option is to
build a Filter string form only the boxes where the user entered something.
There's an example of how to do that in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Roughton" <mr*******@qb e-europe.comwrote in message
news:11******** **************@ n67g2000cwd.goo glegroups.com.. .
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and icluding
the selected date or all records on or after the selected date

The user selects either "=", >=" or "<=" from a combo box and then a
date from another combobox. The combination of thse two choices is
then set in an unbound textbox so for example the result in the unbound
textbox could be =01/01/2006 or >=01/02/2006 or <=01/03/2005.

Upon pressing a Command button the tableview of a form is opened whose
record source is a select query.

If I set the Criteria in the select query to the date in the form's
date combox the results are displayed
e.g. Criteria =[Forms]![StatementGBP]![INST_DATE]

However I'm trying to set the criteria to either the value in the
unbound textbox or the combination of the two combo boxes so that the
results will be for whichever criteria the user specified i.e.
"=01/01/2006" or ">=01/02/2006" or "<=01/03/2005".

The Criteria ought to be " =[Forms]![StatementGBP]![StatusBox]" where
Statusbox is the name of the unbound control

I have also tried "Like (([Forms]![StatementGBP]![ChoiceBox]) &
([Forms]![StatementGBP]![INST_DATE]))" where both of the comboox values
are concatenated.

Whatever I try doesn't seem to work, I have tried various formatting
functions within the select query, all to no avail. Does anyone know
the solution?
Dec 8 '06 #3

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

Similar topics

18
4056
by: CJM | last post by:
I'm building a search function for one of my applications. The user has the option to enter a number criteria of criteria, but none are compulsary. I need to be able to build up a query string that includes only the right criteria. The simplest way I have found is something like this: sSQL = "Select field1, field2, etc form table where 1=1" If Request.Form("Criteria1") <> "" then sSQL = sSQL & " and criteria1 = " &...
19
3545
by: William Wisnieski | last post by:
Hello Everyone, I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the cmdShowResults button on the main form, the subform returns the records based on the two criteria. The criteria used on the main form are values selected in two list boxes. When the user clicks on the first list box (lstCollege), it returns values in the second list box...
6
7118
by: Andy | last post by:
Hello, I am having many problems with setting up a parameter query that searches by the criteria entered or returns all records if nothing is entered. I have designed an unbound form with 3 fields on it: Date (DateSpan1 and DateSpan2), Originator, and GroupName. I have added a button that triggers a query and uses those fields as its parameter criteria to populate a form. The user must be allowed to either enter all of the
3
6378
by: Matthew | last post by:
I am trying to build a form that has certain criteria on it (combo boxes) which a user can pick from a range of values to specify the criteria he wants when he runs a query on the form. I am trying out different ways to do this, but nothing is working. Does anyone know the easiest way to do this?
5
11441
by: SuffrinMick | last post by:
Hello - I'm a newbie to coding! I'm working on an access 2000 database which has three tables: tblContacts - A list of customer contacts. tblOrgTypes - A list of organisational types. tblPositions - A list of job descriptions (Contacts can hold more than one position) I want to use a multi-select list box (Containing alphabetical list of positions) to run a query. HELP!
5
3697
by: jjyconsulting | last post by:
Newbie needing some help. I have a tblParticipants. The fields include gender, education_level, income, occupation etc., I'm trying to create a form where a user can run a query from the form and just choose the appropriate criterias from the combo boxes to get the results. I also want the query to run even if there is not a value in all the combo boxes ie., i want just all males with income level of over $100,000...Any insights or help...
1
8061
by: The.Daryl.Lu | last post by:
Hi, two parts to my problem if someone can help address either one or both: 1. I want to SELECT everything in the table if it matches the criteria when the query button is pressed (this is just a commandbutton on my form). The biggest problem I'm having is that I can't (don't know how) populate a combobox on static items such as "True, False, Any." The Any field, would be selected by the user to find all records where that fields is...
11
5597
by: woodey2002 | last post by:
This problem is driving me crazy. Hello there, i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user can enter their search criteria eg. surname, reg num, etc. in the text boxes. The multi select list box allows the user to select multiple counties which they have the option of including in the search. The user should be able to select or omit the...
45
27709
by: dizzydangler | last post by:
Hi, I'm new to access (2007, running on XP), but I've gotten some great help from this site on my project so far. However, the more I know, the more complex the problems seem to become.... I'm working with all of my data in a single table and, right now, I've got a form linked to a query that allows the user to input whatever search criteria they want through a variety of controls. I've also created a series of check boxes on the form...
0
8970
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
8810
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...
0
7639
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6462
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
4329
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
4564
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2984
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
2242
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1962
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.