Parameter Queries Using Multiple Between....And

May 24, 2007

I have created a query so that two fields have a range in them i.e.

One Field called "Price"
There are two text boxes on the seach form called "price1" and "price2"
The query reads

Between [price1] And [price2]

A second field called "Date Ordered"
There are two text boxes on the search form called "date1" and "date2"
Field called "Date Ordered"
Between [date1] And date2]

I have used the AND operator to join the first and second range. The user will fill in the criteria from a form with text boxes. This form is unbound but as soon as they click on the Search button, it will open up the query with the parameters in as above.

However, if the user were to type in a price but not a date the query shows no results. Results are only shown if a price and a date are inputted.

The OR operator will work but it is not what we need. We need to show results if a price is entered but not a date and vice versa. The fields are coming from one table.

Please can anyone advise? The database is a library management system so the price and date are the price of any books and the date that the book was ordered.

Thank you in advance for any help or advise.

Janet

View Replies


ADVERTISEMENT

Queries :: Access Multiple Queries Run With Date Parameter

Jun 19, 2015

I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.

1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.

2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.

View 11 Replies View Related

Queries :: One Parameter For Multiple Fields

May 20, 2013

Is there a way to have one parameter in a query effect multiple fields?I have a table that details service calls on our equipment. There are columns in that table for each part of the equipment that get marked, depending on what needed serviced (i.e. software, printer, keypad, monitor, etc). I would like to be able to run a query that will show all the service calls where one part of the equipment was serviced multiple times without have to enter multiple parameters or write a union query (there would need to be 11 unions or 11 parameters for each part to be captured). For example, I would want to know how many pieces of equipment had 3 or more service calls on their software. I want to run the query without having to say that I don't care how many service calls were performed on the printer, monitor, or keypad. I would like to be able to say "Software >3" and have the query run on that information.

View 1 Replies View Related

Enter Parameter Dates Once For Multiple Queries?

Jan 16, 2008

I have created a db with 10 linked tables from 10 db's.
I then build 10 queries associated to these linked tables.
The 10 queries filter data by start date and end date.
The data generated from each query is then exported to excel (each query has its own sheet in the woorkbook).

Dim Output_Path_And_File As String

Output_Path_And_File = "C:" & "Production Labor hrs Querie.xls"


DoCmd.TransferSpreadsheet acExport, 8, "labor hrs 3-WAY", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-ACV", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-EAP", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-EVMV", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-PFE", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-propor", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-SEGR", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-TBO", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-VCA", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-VFS", Output_Path_And_File, False, ""


How can I set these queries up by typing the start date and end date only once rather than 10 times?

Thank you

View 5 Replies View Related

Queries :: Control Source - Parameter Prompt Multiple Times

Jul 15, 2014

I have a form using a query as a control source so that is prompts the user for a parameter when they open the form.

I also have a listbox that is calling the same query.

The issue I am having is when I open the form it will prompt me twice to enter the same parameter.

Tables:
tblship
tblconsignee
tblbill
tblinvoicedetails

Query
qinv

Form
frminvoice

When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.

Currently I just have them enter the invoice number twice.

Is there a way I can take the user input and apply it to my other query?

View 3 Replies View Related

Queries :: Multiple Genre Parameter Query In Movie Database

Feb 3, 2015

I have over 800 films which I've converted from DVD to iTunes format. I have added them to iTunes and extracted the data to Excel. From Excel, I have imported the data into MS Access.

So, now I have a movie database of my films. See attached fields for all tables and the Relationships.

As you can see, I have created a separate Genre table as this will be my main search parameters.

I can run a simple query which returns the search results for example 'Comedy'. My problem is, I am wanting to do a search for films with for example 'Comedy' and 'Drama'. Whichever way I try this, I don't get the right answer. I either seem to get duplicate records or no results returned.

I attach the query I am running which works for 1 Genre only!

I am not sure whether I have some fundamental problems with it and it's better to start from scratch?! I have the raw data from Excel which I could use to start again.

View 4 Replies View Related

How To Handle Multiple Null Values Using Form-based Parameter Queries

Nov 19, 2012

i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.

My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)

View 2 Replies View Related

Queries :: Run Query Multiple Times (different Data Parameter) To Generate Single Dataset

Mar 30, 2015

I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.

I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format

The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)

So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.

How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)

The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.

View 3 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'm having multiple problems with my database like things such as -

i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too

I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?

i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:

Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"

The Query is the one im most concerned about , i can live without a form.

View 14 Replies View Related

Multiple Parameter Query

Jun 15, 2005

Hi

Apreciate your help on this.

I have a stock control db and when goods are recieved they could have a different references on them e.g. our internal ref, the purchase order ref etc.

I would like to run a query that firsts ask what criteria i want to use to run the query e.g use the int ref or the PO nr. I can set up a simple parameter query in one field but can not find a solution for the db asking what field i want to use before you put in the reference?

many thanks

scott

View 7 Replies View Related

Multiple Parameter Query

Sep 6, 2007

Hey all,

I think this is a simple fix but I am very rusty on access so I can't figure it out, even after a long haul on search engines!

Problem:

I have a single form in my db. This form has a series of drop down menus, check boxes etc etc allowing the user to select as much information they want to query against!

My end goal is to be able to submit this form, a report is opened and relevant records are shown depending on what options the user has selected.

I have attempted to use a query to acheive this, by simply using:

[Forms]![frm_stats]![cbo_location] Like [Forms]![frm_stats]![cbo_location] Is Null....which I was told would work, i.e. expecting the query to miss the form fields that were not selected....But no, the query only returns recrds based on my first combo box on my form and ignores the rest.

Wondering if anyone has any thoughts? - I don't really want to go down the route of using vba and checking each field in turn!!!!

Thanks a bunch.

View 8 Replies View Related

Queries :: Crosstab Queries - Export Command Ask To Enter Date Parameter Twice

Feb 24, 2015

I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?

Code:
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"

[Code] .....

View 9 Replies View Related

Parameter Prompting Multiple Times?

Aug 15, 2011

I have a report with a subreport. I have added a parameter to the query to prompt for [manager] or is null to get all managers. However, on the report when I enter a manager my count on the subreport still returns the count for all. So, I added the same prompt to the query for the subreport. It returns the right count, however, it prompts for the manager multiple times. How do I get it to prompt only once yet get the right total?

View 1 Replies View Related

Multiple Table Parameter Append Query

Oct 6, 2005

With referential integrity enforced, is it possible to use append query to append to multiple tables based on only one parameter that applies to only one table. If so, please enlighten me with a detailed explanation.

Thank you

View 1 Replies View Related

Querying Same Date Parameter From Multiple Tables

Apr 21, 2008

During a client's stay with our agency, they are served among multiple contracts and are placed within multiple programs. Because clients move frequently between contracts and between programs, we have separate tables which have start and end date fields. It is common for any client to have multiple listings in these two tables, but with different event dates.

I am struggling to create a query that will capture each client's most CURRENT contract AND program. In the future, I'll want to create a parameter query that will allow the end user to enter a range of dates and capture all of the contract and program movement during a period of time.

Any suggestions?

View 4 Replies View Related

Reports :: Requesting Parameter Multiple Times

May 29, 2014

I have created a query with parameters and a report from the query. The report is requesting the paramater multiple times but the query is running fine. How to resolve? Again, the query requests the parameter once but the report multiple times (6x).

View 3 Replies View Related

Queries :: Sub-queries With Text Box Based Parameter

Sep 18, 2014

I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.

Here is the main DELETE query :

Code:
DELETE DISTINCTROW [tblComments].*
FROM [tblComments]
INNER JOIN [qryCommentsToPurge]
ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]

I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.

If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?

Code:
SELECT [tblComments].*
FROM [tblComments]
INNER JOIN [qryIssuesToPurge]
ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID]
ORDER BY [tblComments].[CommentID];

[code]...

View 1 Replies View Related

Form Based Parameter Query With Multiple Controls Used For One Criteria

Nov 14, 2012

My form-based search mechanism uses controls to set the parameters for the query data source.

I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.

HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.

The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]

When i try and run this, i get the message "THe expression is too complex to be evaluated".

View 5 Replies View Related

Parameter Queries Vs Parametrized Queries

Nov 5, 2004

I have been asked to set up a parameterized query using 4 fields (ID Number, Surname, Christian Name and Treating Dr/s). When I search Microsoft On-line under parameterized queries it gives a tutorial which basically set out a simple query with "prompt" parameters in the Criteria field. When I search under the Access Help it gives me a totally different explanation - ie. setting up a cross-tab query first and then defining parameters. Is this a loose use of terminology or is there indeed a difference. Help please!

View 1 Replies View Related

Parameter Queries

Jun 29, 2007

Is there a way to label a parameter in a query?

I need to enter a start date for a query - the parameter - and the end date is always six months later.

Currently, I use;

Between [Enter Start date:] And [Enter End date:], but this is not idiot-proofed.

How can I make the second parameter be a calculation based upon the first; i.e., [First Param] + 180 days?

Thanx for any help!

View 2 Replies View Related

Parameter Queries

Nov 15, 2007

Hi there.

I am looking to rationalise some of the queries I have in my access application.

Let's say I have two forms that query the same table based on a value they have in a combo box.

Currently I would have a separate query for each form and set the criteria to the name of the control on the respective form.

My question is whether I can use just 1 parameter query and use this for both forms, passing the necessary criteria - similar to how you would with an SQL Server stored procedure.

I have done this in the past programatically but wondered whether it is possible to do this within the GUI?

One solution is to not have any criteria in my query and then define an SQL statement in the rowsource of my form/control that queries my original query i.e.:
"SELECT FirstName, LastName from Qry_Customers WHERE QryCustomers.CustomerID = ????"

This works but it not as easy to maintain as having separate queries (same reason why I prefer to use stored procedures rather than definining my SQL statements within my code)

Also are there any negative performance impacts in defining the rowsource in this way (opposed to just setting to an existing Table/Query)?

Thanks,

View 1 Replies View Related

Parameter Queries

Nov 22, 2004

I have a query field with the criteria: [enter state]. I would like to be able to enter multiple states (ie: IL or WA or AZ) but the query only functions when one individual state code is entered. Any ideas how to fix this? Thank you.

View 1 Replies View Related

Need Help On Parameter Queries

Dec 18, 2006

I'm new to this forum and am looking for help in the following:
I'm trying to create a parameter query (using Access 2003) and would like to display just the data that are not null. I tried to insert "and Is Not Null" after the [parameter name] under "Criteria", but it still list null data. Any help will be greatly appreciated (please note that I am a basic Access user and do not do visual basics or anything other than the quick command buttons or the wizard.

Many thanks in advance

View 3 Replies View Related

Date Parameter Queries

Jan 9, 2006

Hi everybody,

I have a report that is based on an underlying query.At the query level,I
want to define parameters of date data type so that records to be displayed
should fall between DateA and DateB. ie Between[Enter Date A:] And [Enter Date B:].
I want the DAteA and DateB to be only days and Months of a year.

Say my DateA = 01 Jan 06 and my DateB = 31 Dec. 06

But I want only the Parameter to be Between[Enter day and Month A:] And [Enter day and Month B:].


How can I write a query with the two parameter values?

Thank you

dfuas

View 1 Replies View Related

Multiply Parameter Queries!

Apr 13, 2007

i have a Query where i have Criteria for two fields as the following:

Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]

Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]

Occasionally, staff may just want all incidents (records) to be shown for the selected StudentSurname and so, just select the surname and leave the date fields blank.

i tried the following:
Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]
Or: [Forms]![frmSearchStudent&Date]![cboStartDate] Is Null And [Forms]![frmSearchStudent&Date]![cboEndDate] Is Null

Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]

then when i tested this by leaving the date fields blank and selecting a valid student surname it simply returned all incidents (records) for ALL students not the selected one.

is it possible to achieve this? if so, could someone please help!

View 6 Replies View Related

Queries :: Don't Want Parameter In Query

Apr 25, 2013

I have a table that has a date column. I am assigning one group of students a test date of 4/29 and another group 4/30. I don't want access to change the column or ask for a parameter during the query, but no matter what I do, it keeps asking me for a parameter. I just want it to use the data that is already in the column.

I tried changing the column name in Excel to DOT from Date. That didn't work.I also tried changing the column to text, so that it wouldn't be a "date." That didn't work.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved