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 Replies


ADVERTISEMENT

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

Queries :: Access Database - Query Based On Multiple Day / Date Criteria

Jul 26, 2015

I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:

1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)

2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)

I have managed to successfully deal with the second of these with the following expression in a query:

CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")

However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.

I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.

View 8 Replies View Related

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 9 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

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

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 :: Date Range Parameter Query

Jan 8, 2015

I have a query that is hard coded with a date range.

SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI,
FROM tblCase
WHERE (((tblCase.Letter_AMPI) Between #4/1/2014# And #3/31/2015#)) OR (((tblCase.ReqReceived) Between #4/1/2014# And #3/31/2015#))
ORDER BY tblCase.CaseId;

I would like to create a parameter query that allows me to only enter the year and the query would append the rest of the date range. So for example, if I prompt the user to enter the date and they enter 2014, the query would know that it means Between #4/1/2014# And #3/31/2015# or if I enter 2015, it means Between #4/1/2015# And #3/31/2016#.As well the date would need to go into both fields ReqReceived and Letter_AMPI.

View 3 Replies View Related

Queries :: Using Parameter In Query With Calculation From Date

Apr 13, 2015

I have a database with a Date of Birth field. I have a query with a field that calculates the age from the Date of Birth (DateDiff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")

As a criteria in this field I want to be able to select a minimum age, so >=[please input minimum age]

However the results are bizarre - sometimes it gives the right answer, and sometimes not. It seems to have a particular problem with ages above 10, which show up all the time.

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

Queries :: Reference Another Query Based On Date Specified As Parameter

May 16, 2013

Within a query, I'd like to reference another query field based on a date specified as a parameter.

In my query, there are fields for each month: [January],[February], etc.

I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?

View 3 Replies View Related

Queries :: Expression Asking For Parameter - Date Selection Criteria

Jul 31, 2013

I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,

Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600

and the second is,

Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]

When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?

View 3 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 :: Format Date Parameter In Dsum - Undefined Function

Sep 25, 2014

I m using Access 2010.I m Facing a problem in the undermentioned expression,

OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)

when i run the query it gives "Undefined Function 'Format' in Expression" ?

View 3 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

Customizing Access Parameter Queries

Apr 7, 2006

Hi
I wonder if anyone could re-orientate me and put me in the correct direction regarding the above subject. Without wittling on too much what I,ve been basically trying to do is to get a query that I've made, derive its criteria from a form that has 3 combo boxes on it allowing a user to select varying options. I have been using a forum thread which described the process in more detail and whilst I,ve followed it to the letter the end result that I get
an annoying message "The expression On Click event that you entered as the event property setting produced the following error: A problem occured while MS Access was communicating with the OLE server or Active X control. It goes onto say that the expression may not result in the name of a macro, the name of a user defined function, or [Event Procedure].There may have been an error evaluating the function, event or macro". The thread took me to www.fontstuff.com/access/acctut08.htm and it is on this site that I have been trying in vain to come up with a matching result. Am I going mad or is there someting on their tutorial that is mis-leading me! I am desperately trying to adminster some 'self help' and to use the forum threads but I am still at a loss as to what to do next. Could some kind person please help...

Carol:confused:

View 1 Replies View Related

Is There A Way To Include Wildcards In MS Access Parameter Queries?

May 4, 2008

I have a table with a string field for a street. It might have data such as:

Bleeker Street
or Bleeker St.

I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?

Thanks.

View 2 Replies View Related

Queries :: Customizing Access Parameter Query - No Data

Apr 22, 2015

I don't want my user to type in the parameter value for a query in case of miss spelling. Therefore, I'm using a dialog box form with a combo box field. The row source of the combo field is a table with one field for the list. I've added VB code (Event Procedure) to a buttons on the dialog box which says to run a query after click. I've created the query for the info I need displayed and am using the forms combo field as the criteria.

Private Sub cmdOK_Click()
DoCmd.OpenQuery "qryRequestsbyBranch", acViewNormal, acEdit
DoCmd.Close acForm, "frmDepartmentList"
End Sub

The query runs except I'm not getting any data.

View 14 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

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

1 Date Range With Multiple Reports / Queries

May 19, 2006

Hello All,

I am trying automate some report printing.

I have around 20 reports most of which require a from date and a two date.

If I use the code below, it asks for the from a two date on each report print, but the dates are always the same.

Is there a way of take a two date and a from date at the start of the sequence and insert it into each reports date range prompts?


Private Sub btnME_Click()

MsgBox "Please note, this process can take upto 5 minutes to complete."

DoCmd.SetWarnings False

DoCmd.OpenQuery "qry1"
DoCmd.OpenReport "rpt1", acNormal
DoCmd.OpenReport "rpt2", acNormal

DoCmd.SetWarnings True

MsgBox "Reports have been printed, aggregated results have been inputed into 'workstats_automated.xls'"

End Sub

View 14 Replies View Related

Using Same Date Range On Report For Multiple Queries

Jul 25, 2007

Hi all,

Can anyone tell me if it is possible to input just one date range into a report that is accessing data from multiple table and multiple queries. I can create the report which gives me the info needed but I have to put the same date range in 3 or 4 time before the report is generated.

Thanks

View 4 Replies View Related

Queries :: Return Max Date Of Multiple Columns

Jul 28, 2014

How do you return the most recent date of multiple columns.

I have a table (tbl_courses) that has a list of training courses. We want to know when a client completed the course most recently.

The problem is, for one course there has been up to 4/5 different variations of the course with different names over the years. E.g. "Drug awareness" has also been known as "Drug Aware" "Illegal Substances" and "Stoppers". I want to pull through the most recent date for all of the above.

We have a field in the Courses table that links the courses into groups (e.g. All drug aware courses come under "23"). Not sure if that works?

Is there a way to do this? The Tbl_Courses is linked to Tbl_Clients via a ClientID.

I've managed to do it in SQL using GREATEST() but that isn't an option in Access.

View 6 Replies View Related

Queries :: Concatenate Multiple Records Grouped By Date?

May 2, 2014

I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]

I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]

Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)

The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.

Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)

I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :

Code:
SELECT [tblRates].[RateDate]
FROM [tblRates]
INNER JOIN [tblCurrencies]

[Code]....

But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.

View 3 Replies View Related

Queries :: Date - Multiple Columns Into Single Column

Jun 6, 2013

I want to create the date from multiple columns to single colums. Just for example

table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013

Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013

Now i want to make one query, which can you the date as follows when we give criteria = apple

Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import

Means two dates from different table into one query column... One way in my mind to make one table for both tables.

View 9 Replies View Related







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