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)?
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"
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.
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];
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!
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.
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.
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?
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
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!
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.
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.
This statement returns a "date" each time the event occurs. I want to have a parameter between [Start Date] and [End Date] so I can pull events for a specified date range. I have tried this "Expr [EventDate]" Between [start date] And [end date] but it returns "null" results. I am using access 2010
EventDate: IIf([tblEventException].[EventID] Is Null,IIf(([qryEventCartesian].[PeriodTypeID] Is Null) Or ([qryEventCartesian].[PeriodFreq] Is Null) Or ([qryEventCartesian].[InstanceID] Is Null),[qryEventCartesian].[EventStart],DateAdd([qryEventCartesian].[PeriodTypeID],[qryEventCartesian].[InstanceID]*[qryEventCartesian].[PeriodFreq],[qryEventCartesian].[EventStart])),IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate]))
Is it possible to have a parameter in the field row of a query? That way, the user could select the field they want to query against without going into the design view of the query.
I have a query that retrieves a list of all "Open" issues/projects that I'm working on. I've added a couple of columns in the query to calculate the age of each project (comparing Now() to Start Date), and using a comparison between the last time I updated the project to Now().
The problem I'm having is that the second calculation is based on the first calculation. As a result, the second calculation causes prompt box "Enter Value Parameter" to pop open whenever I open the form based on this query.
I would like to know how can I open this form without the prompt box popping up?
I have upto 10 queries which in turn produces 10 reports. Each query has a 'current_year' parameter input by user. Storing 'current_year' value in table and accessing it in queries was an option which was discarded because of the possibility of inner queries using 'current_year' value from a table could slow down the query.
Is there any way to set parameter values for 10 queries in a single stretch. In all the queries parameter name is the same.
Here is a sample query. CURRENT_YEAR is the parameter ===================================
SELECT "1. Candidates in full time Education in Government Schools" as CAPTION,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR ) AS CURRENT_TOTAL,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR AND C_P='S' AND CENTRE <= 'BW835' ) AS CURRENT_NUM, Round(CURRENT_NUM*100/CURRENT_TOTAL,2) AS CURRENT_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 ) AS CURRENT_MINUS1_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS1_NUM, Round(CURRENT_MINUS1_NUM*100/CURRENT_MINUS1_TOTAL,2) AS CURRENT_MINUS1_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 ) AS CURRENT_MINUS2_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS2_NUM, Round(CURRENT_MINUS2_NUM*100/CURRENT_MINUS2_TOTAL,2) AS CURRENT_MINUS2_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 ) AS CURRENT_MINUS3_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS3_NUM, Round(CURRENT_MINUS3_NUM*100/CURRENT_MINUS3_TOTAL,2) AS CURRENT_MINUS3_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 ) AS CURRENT_MINUS4_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS4_NUM, Round(CURRENT_MINUS4_NUM*100/CURRENT_MINUS4_TOTAL,2) AS CURRENT_MINUS4_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 ) AS CURRENT_MINUS5_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS5_NUM,
Round(CURRENT_MINUS5_NUM*100/CURRENT_MINUS5_TOTAL,2) AS CURRENT_MINUS5_NUM_PERCENT, ROUND(CURRENT_NUM_PERCENT-CURRENT_MINUS1_NUM_PERCENT,2) AS DIFFERENCE1, ROUND(CURRENT_MINUS1_NUM_PERCENT-CURRENT_MINUS2_NUM_PERCENT,2) AS DIFFERENCE2, ROUND(CURRENT_MINUS2_NUM_PERCENT-CURRENT_MINUS3_NUM_PERCENT,2) AS DIFFERENCE3, ROUND(CURRENT_MINUS3_NUM_PERCENT-CURRENT_MINUS4_NUM_PERCENT,2) AS DIFFERENCE4, ROUND(CURRENT_MINUS4_NUM_PERCENT-CURRENT_MINUS5_NUM_PERCENT,2) AS DIFFERENCE5
FROM MERGED_TABLE AS A WHERE YEAR In (CURRENT_YEAR)
I have upto 10 queries which in turn produces 10 reports. Each query has a 'current_year' parameter input by user. Storing 'current_year' value in table and accessing it in queries was an option which was discarded because of the possibility of inner queries using 'current_year' value from a table could slow down the query.
Is there any way to set parameter values for 10 queries in a single stretch. In all the queries parameter name is the same.
Here is a sample query. CURRENT_YEAR is the parameter ===================================
SELECT "1. Candidates in full time Education in Government Schools" as CAPTION,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR ) AS CURRENT_TOTAL,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR AND C_P='S' AND CENTRE <= 'BW835' ) AS CURRENT_NUM, Round(CURRENT_NUM*100/CURRENT_TOTAL,2) AS CURRENT_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 ) AS CURRENT_MINUS1_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 1 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS1_NUM, Round(CURRENT_MINUS1_NUM*100/CURRENT_MINUS1_TOTAL,2) AS CURRENT_MINUS1_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 ) AS CURRENT_MINUS2_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 2 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS2_NUM, Round(CURRENT_MINUS2_NUM*100/CURRENT_MINUS2_TOTAL,2) AS CURRENT_MINUS2_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 ) AS CURRENT_MINUS3_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 3 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS3_NUM, Round(CURRENT_MINUS3_NUM*100/CURRENT_MINUS3_TOTAL,2) AS CURRENT_MINUS3_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 ) AS CURRENT_MINUS4_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 4 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS4_NUM, Round(CURRENT_MINUS4_NUM*100/CURRENT_MINUS4_TOTAL,2) AS CURRENT_MINUS4_NUM_PERCENT,
(SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 ) AS CURRENT_MINUS5_TOTAL, (SELECT COUNT(CAND) FROM MERGED_TABLE C WHERE C.YEAR = CURRENT_YEAR - 5 AND C_P='S' AND CENTRE <= 'BW835') AS CURRENT_MINUS5_NUM,
Round(CURRENT_MINUS5_NUM*100/CURRENT_MINUS5_TOTAL,2) AS CURRENT_MINUS5_NUM_PERCENT, ROUND(CURRENT_NUM_PERCENT-CURRENT_MINUS1_NUM_PERCENT,2) AS DIFFERENCE1, ROUND(CURRENT_MINUS1_NUM_PERCENT-CURRENT_MINUS2_NUM_PERCENT,2) AS DIFFERENCE2, ROUND(CURRENT_MINUS2_NUM_PERCENT-CURRENT_MINUS3_NUM_PERCENT,2) AS DIFFERENCE3, ROUND(CURRENT_MINUS3_NUM_PERCENT-CURRENT_MINUS4_NUM_PERCENT,2) AS DIFFERENCE4, ROUND(CURRENT_MINUS4_NUM_PERCENT-CURRENT_MINUS5_NUM_PERCENT,2) AS DIFFERENCE5
FROM MERGED_TABLE AS A WHERE YEAR In (CURRENT_YEAR)
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...
I am trying to alter this parameter to bring back all records if either beginningsalesrange or ending salesrange is left blank. I can't quite get it right.
Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange]
I have multiple queries that I need to have filered by the same parameter (Client). There is a list of clients that receive this report (the report has a pre-made template with multiple tabs, hence the multiple queries).
Currently the user of the database has to filter the exported excel file for each client re-save and email basied on the client. The above is done when the user pushes the specified button on a form (which just says run report a on it).
I am trying to figure out how the write a code so that a parameter (or table if that would work better) pulls the first record of the Client query to filter the other queries being run. Then to loop that process with the second record and so on through the Client query until all the clients have had the report run for them.
Attached are the current VBA being used and the current SQL for the first query that needs the parameter value added.
I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String Public Function returnName() As String If IsNull(gbl_loginName) Then returnName = "test" ' dummy account created for development only Else returnName = gbl_loginName End If End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name], [Entry of Hours].[Set Up Time], [Entry of Hours].[Run time], [Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date], [Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?
I have a criteria in a parameter query that asks the user to enter a Subsystem number (such as 4596-666-001). The source data for my database has Systems (e.g, 4596-666) and each system has several Subsystems associated with it (e.g, 4596-666-001, 4596-666-002, etc). The System and Subsystem numbers are stored in the same column of the source data spreadsheet, and hence the same field in my database.
The source data is structured in such a way that Systems and Subsystems have different information. I would like to set up a query that shows the System information as well as the Subsystem information.
For example, if the user enters 4596-666-001 into the parameter dialog box the query returns all the information for 4596-666-001 and 4596-666 (but NOT any other Subsystem).
As a work around I have set up an OR situation where the user first enters the Subsystem number and then another parameter query dialog opens and they enter the System number. This works but is not as nice as simply entering the Subsystem number and having all information returned.
Is there a way to set up my query so the when 4596-666-001 is entered an OR is generated using the first 8 characters of the Subsystem number?
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.
I am trying to set up a create table query using parameter called Year. For each year I input , I need to create a new table with the suffix of the year. for example, if i input year 2011, i want to create a table called budget_2011, if i input year 2012, i want to create a table called budget-2012...
In my criteria, I am passing a parameter from a form.
Currently, I am using: DateValue([forms]![myForm]![txtStartDate])
From myFORM, the value of the text box is 3/1/2013
When I run the query, it runs as expected and returns all records with the 3/1/2013 dates.
But now I want to make it where if the parameter is null.
How do I change that criteria if that parameter I am passing is null from my form's textbox? I want it to return all records that have a null entry in the [startDate].
I've used this parameter back in Access 2.0 where I ask for I put [What location?] as the parameter and users can put 'North' or hit enter to get all locations.
