Using form to enter "query criteria" (between values)

Good morning everyone,

I want to create a form with two boxes, where first box is lower limit of 
the value and the second upper limit. This values should be used in my query 
as filter criteria.

Like this (query filter criteria): > "textbox1" AND < "textbox2"

Does anyone know how I can build this in a form (i.e. connect my textboxes 
and query criteria)?

Kindly,
Mikael
Sweden
0
Utf
12/18/2007 9:55:01 AM
access.forms 6864 articles. 2 followers. Follow

2 Replies
2558 Views

Similar Articles

[PageSpeed] 7

By thinking about for another minute I found the (simple) answer.

In the query criteria I put the following expression:

BETWEEN [forms].[nameofform].[NameofDatefield1]  AND 
[forms].[nameofform].[NameofDatefield2] 

Where 1 and 2 are the names of the textboxes in my form.

//Mikael
0
Utf
12/18/2007 10:39:01 AM
On Tue, 18 Dec 2007 02:39:01 -0800, Mikael Lindqvist
<MikaelLindqvist@discussions.microsoft.com> wrote:

>By thinking about for another minute I found the (simple) answer.
>
>In the query criteria I put the following expression:
>
>BETWEEN [forms].[nameofform].[NameofDatefield1]  AND 
>[forms].[nameofform].[NameofDatefield2] 
>
>Where 1 and 2 are the names of the textboxes in my form.
>
>//Mikael

That will work... if the table field contains only pure dates without a time
component. Otherwise it will miss records on the last day of the range. 

It may also cause problems if users enter variant date formats (day-month-year
in a system defaulting to month-day-year for example).

I'd suggest doing two things: include 

PARAMETERS [forms].[nameofform].[NameofDatefield1] DateTime,
[forms].[nameofform].[NameofDatefield2] DateTime;

prior to the SQL statement, and use a criterion

>= CDate([forms].[nameofform].[NameofDatefield1])  AND < DateAdd("d", 1, CDate([forms].[nameofform].[NameofDatefield2]))


             John W. Vinson [MVP]
0
John
12/18/2007 6:32:16 PM
Reply:

Similar Artilces:

Pass option box selection to combo box query criteria
I have an option box with options 1-4. I have a combo box with data based on a query. I would like to have the option box selection limit the selections on the combo box. Any help is appreciated. -- Teach me to fish! Thanks for the help. Pax, M Give a little more info her or an example of what you are trying to achieve then we might be able to assist you. The way I read it now is that when you select 1 in the optionbox you want to have only one choice in the combobox. What's the source of the combobox? -- Maurice Ausum "m stroup" wrote: > I have a...

Search Folder
I am using OL2003. Anyone knows how to create a search folder that defines all e-mail address from @abc.com but excluding a few users from the same domain name say(judie@abc.com, michael@abc.com) Thanks a lot ...

IN SELECT Query Criteria
I think I read somewhere that using an "IN SELECT..." type of query will speed up your query performance in Access 2003 better than linking the two tables by their common field. Is that correct? If so, what is the proper syntax for creating this type of query criteria? My scenario is that I have a very long list (1000's) of employees and a select group of managers (about 30). I only want the employees whose manager is in my table of selected managers. I should end up with a list of <200. Is this a good way to use the "IN Select.." as my criteria? -- THX cs ...

Not Like
I have several thousand rows of data with various values (CL, A1, A2, Null). When I put the criteria - Not Like "CL" in the query criteria, it doesn't return any rows (and I know there are rows that don't have "CL" in them). If I put Is Null or Not Like "CL", it will return the blank rows. I want to return the other values (A1, A2 and Nulls), but I can't get it to return any records when I just say Not Like "CL" or Not In ("CL"). This should be simple, and I'm sure I'm overlooking something....any help is app...

IIF statement in query criteria, help!
I have a query in which one date need to be filtered based on another field. If the field [last] =1 , the data needs to be filtered showing only data where [ShiftDate]>=[Start1]. If the field [last]=2, it is [shiftdate]>=[Start2]. So in the criteria for field [ShiftDate], I entered "iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])". I don't get an error, but I also don't get any results. If I enter ">=[start1]" or [shiftdate]>=[start1] instead of the iif statement, I get the results expected. I'm pretty sure that I...

How to select fields using either query criteria or a macro
I have created a database which logs operational data for an industrial water plant. I want to output a table of the data, based on inputs from the user. I've created a form where the user can enter a date range for the data they want to view, and a series of check boxes which correspond with the fields in the underlying table. In order to restrict the output data to the date range and fields required by the database user, I have created a query. My problem is that I can't seem to find a way to link the check boxes on my form to the 'show' checkbox in the query. ...

Multiple Listbox selectin and query criteria
I've read many of the posts here about using multiple list box selections to filter queries yet I still cant get it to work. I'll post my understanding then my code (which I am totally clueless about) and see which is at fault. My table is called "sales", my query is "sales summary Query", my filter form is " Filter Report", my listbox is "customerselect" and set to simple multi select I then have a hidden control named "customerselect2" with on click set to event procedure, then in the procedure I've pasted the code ...

Numerical query criteria and quotation marks
I have a query that uses a criteria of >"269.9". I realized it was not working because the format of the field being queried was text. I successfully altered the format of the source field to double. Access will not allow me to remove the quotation marks from around the "269.9" criteria. It puts them back as soon as I tab off the field. If I create a new query it works, but why won't it let me modify the existing one? Thanks, -- PRD In query design view try deleting the field, save, and then put it back. -- Build a little, test a little. ...

date query criteria prompt
hi I would like to add a prompt to a query allowing user to enter month and year desired and show all records where matches criteria the date is in format dd/mm/yy so if users enters 04/10 at prompt they would get all data for april 2010 or do I need to have start date and stop date thanks tina "tina" <tina@discussions.microsoft.com>, iletisinde şunu yazdı, news:9C66AE9A-B1C9-4B7A-9185-C37780AAD5FA@microsoft.com... > hi > I would like to add a prompt to a query allowing user to enter month and > year desired and show all records where matches criteria ...

multiple query criteria
Let's start with the fact that I am an Access novice, so if this looks like I am way off base, and cannot get any guidance here, just let me know. What I have is a table listing vehicle inspection standards. A vehicle tag number, and then 50 fields such as, wipers inop, headlight inop, etc. My fields are numeric, because there is a point system used to determine a pass/fail. I input the tag number, and if the specific area is deficient, I enter either a 10 or a 25 for point values. What I end up with, is a table with tag numbers and then fields showing p...

Query criteria 05-23-07
Hello, Does anyone know a good way of including special characters inside of an Access query criteria? IE, the following data is in a field that needs to be searched against: Field 1: dr100 dr101 dr102(a) As so forth...how do you set your query criteria so you can search for the dr102(a) entry? ...

Query Criteria and Data Formats
I have a query against an ODBC VFP table linked to our sales order software. I have run the total sum to display a daily total of all sales orders entered, but need to have the data displayed not only by day but also by week and month. I cannot find the correct expression for the query or report to group by week and month as well as by day. Does anyone know how to do this? PS I posted this about 2-1/2 hours ago and it seemed to be dropped from this thread, anyone know why? "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message news:115E7F08-BC3C-42EA-BF6D-...

query criteria from form
Hi, I have a table that contains many yes/no fields. "Attribute 1", "Attribute 2", "Attribute 3", etc. I would like to set up a query whose criteria for these fields comes from a form with a check box for each field. I tried using a reference to a check box control on a form as the criteria in the query. For example, the criteria for "Attribute 1" in the query would be [forms]![formname].[Attribute 1] I named the control after the field. The problem with this is, since every field has this criteria, if Attribute 1 is checked on the ...

minimize repeating query criteria & generating monthly YTD totals
I have 5 queries. One for each month. There are # things I want to do here. Consolidate to a single query. currently the "Query is too complex" message appears. Simplify my code. I'm only changing the date range in my criteria, but repeating the Aggregate a YTD column for Count, Sum, Avg Completion Time fields within the same record. Please keep in mind that my query must start with December 2009 to current. "Location" Mandatory field for all tables SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, "" AS [Avg Completion Ti...

Help with query criteria
I am creating an inventory database. Inventory is taken 3 times per day based on shift start. I want to be able to create a parameter query and then a report that will show the inventory taken at the start of each shift. There are nine areas that are inventoried each shift. I need to be able to query by date and time. I want the times to be broken down by shift name (i.e. Days, Afternoons, Nights). The form that feeds the table where the query is run has a txtDate field and txtTime field with default values of =Date() and =Time() so that when the data is saved to the Inventory table the ...

Query Criteria question
In a query; the fields are: CustomerID, Customer, NotPaid, Overdue, Outstanding. Where Notpaid is under 10 days, overdue is over 10 days, outstanding is over 30 days. I need to see NotPaid and Overdue only. If I type True in both Criterias, it shows nothing. I can only go with one True. How can I do it so I can see both but no other data which would be irrelevant? I know I can do one form NotPaid, the other Overdue, then show both pages. I am trying to avoid that. You need an Or statement. In the query design view put the True for the two fields on different lines on the Criteria. -- ...

Options Group and Query Criteria, revisited
I recently posted a question about option groups passing info to a query for a Yes/No field, and received some great advice. However, I've tried to apply that advice in a similar way to a Date field, to no avail. In the query, I want the user to be able to display (1) only records with dates present or (2) only records without dates present or (3) all records. I changed the query criterion that worked for the checkbox as follows, and placed it under the Date field which I want to select by (this statement yields no records): IIF([forms].[fmQryByClinicSurveys].[emailOptions]=1...

Partial match for query criteria
Hello, I am trying to create a query that will deliver results based on matching only a part of the text in a table field. For example, if I have a table that contains the names, address and phone numbers of 10,000 businesses but I only want my query to show me businesses that have the word "National" in their name, how would I do that? I'm not sure how to set up the criteria. Thanks for your help! Use the Like operator with wildcards. In the 'criteria' row of the business name column in query design view enter: Like "*National*" Or you can use a ...

Problem with using query criteria
I have a column named "DATE". The way the information is set up is, YYYYMMDD. I need to run a query by YYYY. In other words, i need to filter everything that is in year 2000 but i have information from 1999-2003. How can i run this query with this criteria. First you need to change the name of the Date field. Date is a reserved word and should not be used for a field name. YYYYMMDD is a text format so you can use a text function to get the year. Add the following expression in a blank field in your query: MyYear:Left([MyDate],4) Then put "2000" with the quotes in...

using a date variable in query criteria
Hello I want to use date variables in the criteria of a query. When I put Between #1-1-07# and #1-30-07# it works but if I try to use a date variable it doesn't work. In the query by example view. The field in the database is a string but I convert to a date using Datevalue([work_order_received]). I want to calculate a date like this in the Field: dateback: datefrom: ( year(Date())-1 , 1 ,1) dateto: (year(Date())-1,12,31) When I try to use the variables (datefrom dateto) in the criteria statement this doesn't work ...

Using IIF to pass query criteria
Good morning all! I have searched the archive and didn't find an exact answer to my question, so I am posing this quesiton to the group: I am trying to use IIf([forms].[PreviewReports].[chkpumpstations]=0,<>7,7) but in reading through the posts here I understand that the IIF statemtn may not pass the <> not equal to characters to my query. I have also tried: IIf([forms].[PreviewReports].[chkpumpstations]=0,"Not 7",7) and various other combinations like it to no avail. What I would like to do is if the mentioned check box is active (true) collect only...

Use Table field in query criteria
Just like using the Forms!FormName!FieldName....can I use that same logic with Tables? I have a start and end date in two fields within a single table. I would like the query to reference the StartDate field and the EndDate field within that table. I don't want the user to have to have a form open to run the query. Any ideas? Thanks. If the table has one record and you don't need the query to be editable then simply add the table to the query and add the start and end date fields to the criteria. If your query must allow edits, you could try DLookup() to return th...

using text box as query criteria
I have a form with a subform. My main form has a text box wich is used in the query expression of my subform. In the KeyUp event of the text box I refresh the forms. This works to filter my subform each time a new character is entered into the text box. My problem is every time a key is pressed the refresh selects the entire contents of the text box. each new letter you type replaces the last making it impossible to type an entire word. Is there and easy work around. Is there a way to mimic the end key behavior in code? TIA, Tim On Fri, 23 Feb 2007 16:11:05 -0800, "Tim ...

Query criteria: field is empty
I'm sure this has been asked before but I can't seem to create a successful search for it. I'm trying to generate a simple query which brings up all records where field X is empty. In reality, this is the "Invoice paid" (date) field and the query will bring up all unpaid invoices. Any suggestions? Thanks, Paul In query design, in the Criteria row under this field, enter: Null -- 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. "paulkay...

Edit and save query criteria
I have 2 Select queries where the results criteria changes on a yearly basis for 2 fields. There are 6 tables referenced in the queries. Only 2 tables referenced with 1 field each where the criteria would be changed. How could a user edit and save these changes? These are very Novice users of a database who would not have access to the open database. This was a homegrown database, but now trying to make it useable for others who have little to no experience. Thanks for any and all suggestions. -- DN One way would be to use a parameter prompt where the users are prompte...