Queries :: Using Controls From Separate Forms As Parameters In Same Query?
Mar 28, 2014
I have 3 forms that all use the same sub form. The 3 main forms show 3 different project types, where the sub form shows what other projects that a company is engaged with. Rather than create 3 different queries for my sub form, i would like to just filter it using criteria that looks at the CompanyID field on my 3 main forms. Currently my criteria is like this:
[Forms]![frmProjects_Detail_IND]![txtcompanyid] Or Like [Forms]![frmProjects_Detail_CSS]![txtcompanyid] Or Like [Forms]![frmProjects_Detail_TAP]![txtcompanyid]
Is there a way to ignore the parameters that are null?
View Replies
ADVERTISEMENT
May 20, 2013
I did a query which parameter is written in a text box - tprj. This text box is in a form, which is in a navigation control, which is within another navigation control. How can I refer, in the query, to this text box?
The navigation forms are nmain which contains nprojects.
nmainsub and nconsultprojects are the navigation subforms
fprjconsult is a normal form, which is inside nconsultproject
I tried the following criteria:
[Forms]![Nmain].[form]![nmainsub].[form]![nprojects].[form]![nconsultprojects].[form]![fprjconsult].[form].[text4]
And
[Forms]![Nmain].[form]![nmainsub]![nprojects].[form]![nconsultprojects]![fprjconsult].[form].[text4]
And
[Forms]![nmainsub]![nconsultprojects]![fprjconsult].[form].[text4]
And
[Forms]![Nmain]![nmainsub]![nprojects]![nconsultprojects]![fprjconsult].[form].[text4]
And other forms too but I can't get it right.how to refer to forms inside navigation forms, inside navigation forms, in SQL? What are the rules for writing it clearly?
View 5 Replies
View Related
Aug 11, 2015
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.
View 1 Replies
View Related
Jul 22, 2014
I have three tables A, B and C.
I want to build a query that displays three separate totals: Total of A, Total of B and Total of C.
When I try to do this, the query has a weird behaviour summing the totals of the totals of the totals, and I don't know why .
I'm attaching a really basic sample of what's happening, where totals SHOULD be $4, $8 and $16 instead of $16, $32 and $64.
View 14 Replies
View Related
Dec 1, 2013
I have a query that pulls information from two tables. Some of the fields that are being queried share the same name in the tables, [Reimbursed_Amount] and [Cancel_Fee] specifically. In Design View I have specified that I only want the query to pull these fields from the Event Information table. An error occurs when I try to run it, saying that I need to define which table the field is from in the SQL code.
But then after I added clarification in the SQL, when I run the query it now prompts for a parameter for each of these fields. Why is this happening? I leave it blank, so a parameter has no impact on the query. How can I stop this?
Here's the SQL, after I added the table clarification:
Code:
SELECT (Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee].[Event Information])-nz([Reimbursed_Amount].[Event Information]))) AS Total_Cost, [Shared Billing Information].Paid, [Shared Billing Information].Shared_Billing_ID, [Event Information].Shared_Billing_ID
FROM [Shared Billing Information] RIGHT JOIN [Event Information] ON [Shared Billing Information].Shared_Billing_ID = [Event Information].Shared_Billing_ID
[Code] ....
View 7 Replies
View Related
Oct 31, 2013
I want to set parameter in my select query with or function as in detail.i have two form with same field
1-with field A
2-With Field A
now i want to select data for a table .with a parameter select with Field A in form 1 or Field A in Form 2.How can i do it
View 5 Replies
View Related
Jul 10, 2014
I have about 12 tables in a 2010 database and I want to create a single summary form showing key details of the database. Most of these will be count fields using various subset of the tables e,g. total members, count of new members this year, count of those not attending a meeting etc. More than one table will be represented on the form.How would I set up a series of controls that I then bind to SQL queries? Or is this not the way to do it?
View 8 Replies
View Related
Nov 23, 2013
I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.
I am having issues figuring out how to get it to pass them to a query or filter the reports.
I have tried many different examples and nothing seems to work.
View 4 Replies
View Related
Aug 15, 2014
Do access VBA implements parameters passed to query strings in all following parameters?I've been working in ASP.NET/Razor C# and this would be an example of how it would be done:
Code:
db.Query("INSERT INTO threads (name, date_of_creation, user_id, area_id, user_group_id)" +
" VALUES(@0, @1, @2, @3, @4)",
Request["txtThreadTitle"],
DateTime.Now,
Session["user_id"],
area_id,
0
);
View 6 Replies
View Related
Jul 25, 2013
im working in MS Access 2007.what im trying to do is have a query run specific parameters if a check box is selected. So if the check box is selected than the query filters the "Tranche" column so that the only records that shown are records that have the "Tranche" coloumn = 1.
I know this can be done either in vba code or in the criteria section of a query but i dont know that appropriete language for either.
In vba code i was able to get this far. But..... i dont know how to call the criteria line from a query?
This is my very simple unfinished code.
My query is called [Tranch Query] and the column i want to filter with is call [Tranche]. The check box is called [Check0].
Private Sub Check0_AfterUpdate()
If Check0 = True Then
'How do i set the query criteria?????
End Sub
View 8 Replies
View Related
May 1, 2013
I have an database that uses a couple of different date ranges, so I created a table that shows the different date ranges that may be required (xReport Dates) so I didn't have to keep manually editing queries or entering dates every time.
I have one query that appends data from one table into another based on a date range that you need to manually enter when prompted; I can't seem to get it to refer to my xReport Dates table for the range.
Its currently set up as below:
INSERT INTO 001_M_Gross_Telesales ( UpdateDate, OMSNumber, MediaRoute, ExecName, SaleType, Name,
[Reporting Campaign], [Reporting Team], [Sales Leader], [Reprting Name], [Media Route2] )
SELECT Max(L_ExecTracker.UpdateDate) AS MaxOfUpdateDate, L_ExecTracker.OMSNumber,
L_ExecTracker.Campaign, L_ExecTracker.ExecName, L_ExecTracker.SaleType, Z_Ref_Agent_Table1.Field23,
[Code] .....
View 1 Replies
View Related
Jun 9, 2013
I have a table field that contains a lot of part numbers in different format. One could be with hyphen while others are without hyphen.
record#1 : 4535-300-34567
Record#2: 453530034567
Record#3: 4535-301-56721
In this case record# 1 & 2 are same part number only difference is hyphen.
I want to set my query parameter [Enter part number] such a way, when some one enter 453530034567 it should pull both the record (record#1 & 2).
View 5 Replies
View Related
Apr 6, 2013
I have a fact table which contains a list of products at many different Retail Prices. I want to band these products into groups based on Retail Price Bands.
I have created a second table with the fields Retail Price Band, Minimum Retail Price, Maximum Retail Price. This defines the banding structure.
I would like to join Retail Price with Retail Price Band based on the parameters in the second table but don't know where to begin.
View 3 Replies
View Related
Sep 16, 2013
iwhen i add a field to allow me to add the parameters for a search between to dates. the query will only count the ethnicity of people who complained on the same day and give the others as a single number. i need to allow the user to search between dates as to generate the data for a report.
View 3 Replies
View Related
Feb 9, 2015
I have a template csv file which has comes with headers. I now need to export multiple datas from my different tables into the csv file. I thought I would open an instance of excel, open the query as recordset (in VBA) and go through each record and finally use appExcel.saveAs as a CSV file. The approach doesnt look like it will look. I have looked into the DoCmd.Transfer text method but I cant seem to create a export specification because my query requires two parameters (startDate and endDate).
Secondly it is a huge template, over 700 fields and I will only be exporting around 40 fields so there will be huge number of empty fields. If this was a excel file, I would write few queries and write result from one query into the file then skip required columns and then write results from another query but I don't know if its possible with Docmd.write txt.
I am using access 2013 (Office 365 Pro) to perform all of this.
View 10 Replies
View Related
Jun 4, 2014
I have two tables, and I'm trying to create a crosstab query...I think. One table is Contracts, one is Contacts. Each contract could have multiple contacts, but they are numbered (1, 2, 3...etc.) based on importance. I want to create a query that keeps each contract on one line, and separate fields for each contact and each field of the contact. So a contact will have Title, First, Last, Address, etc. So I want my query to show as follows:
Code:
Contract-----Title1-----First1-----Title2-----First2
ContractX Mr. James Mrs. Sally
I of course need this to be dynamic, so if a contract has 9 contacts, there are fields up to Title9, First9, etc.
View 7 Replies
View Related
Feb 11, 2014
I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:
Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.
This is not currently working.
View 8 Replies
View Related
Oct 18, 2013
I have a query and a form, and what I want to be able to do is have the user type in within the form the parameters for the query.
The part of the query that will hold the parameters is based on an amount (formatted as Currency), but I want the user to be able to enter >10 , =<100 or >100000 and get the correct results.
I have already set up the query and the form with unbound cells which are then referenced in the query I've tried just one cell where the user would enter >100000 or tried two cells where one cell would be for >,< etc and one cell for the value (which is formatted as currency), but that didn't work either.
The idea is that you enter the parameter and value then click on a button that runs a macro to export the query based or the user parameters, but everytime I try it I get a box appearing saying Property not Found.
View 1 Replies
View Related
Jun 20, 2013
I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.
Table tClients stores the current clients - primary key is named "clientRef"
Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
Once the completed flag is set that client will be ignored so no further entries will appear.
So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.
View 10 Replies
View Related
Dec 2, 2013
What i wish to do: list activities in a continuous form, and in one column display for each row a listbox which rowsource query is based on another column value from the same row.
I have to admit that the best thing for me would be to be able to create through vba a continuous form... is that possible?
View 3 Replies
View Related
Aug 26, 2013
I've got a code that allows me to select one or many names from a listbox on a form and return data relevant to the name(s) selected from a query. The following code is triggered by a button on the form...
Private Sub Toggle4_Click()
'Set it all up for CSM selection
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[code]....
(Toggle 10 goes to the code for the second listbox which has the same code with different tables refered to giving two selections in the query.)
What I want to do is replace the "warning if nothing found" with a code to show data against all the names in the list box if nothing is selected in the listbox.
View 13 Replies
View Related
Jun 6, 2013
There is a command button on my form that calls a filtered query. I'm filtering the query based on a combo box within the form using
[Forms]![frmAssignDwgs]![Combo15]
It works beautifully until I put the form as a subform in the Navigation Form I'm using. When I execute it from within the Navigation form, it errors out looking for [Forms]![frmAssignDwgs]![Combo15].
Is there another syntax I can use so this will still work from within the Navigation Form? Or another way to approach this functionality?
frmNavigation is the main navigation form
frmAssignDwgToPkgs is the subform that holds Combo15
I found another thread (which I can't link to since I'm new) and tried various arrangements of the following to no avail.
[Forms]![frmNavigation]![NavigationSubform] .[Forms]![frmAssignDwgToPkgs]![Combo15]
View 3 Replies
View Related
Jul 31, 2013
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
View 6 Replies
View Related
Feb 23, 2015
How can you export cross tab queries by using date parameters (for example: Jan 1, 2014 to December 31, 2014)...
View 3 Replies
View Related
Aug 9, 2013
So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.
Query 1: Total Distribution
Query 2: Total Distribution
etc....
Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?
View 1 Replies
View Related
Dec 16, 2014
I have a sign up form where people select their name for the time slot they want, there are 2 spots open per time slot, so I have Interviewer A and Interviewer B. Records can then either just be a name in A or a name in A and B.
I would like to run a report that shows how many times all the interviewers have signed up in a date range.
I can do this individually by looking up one name at a time with this query:
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted
FROM Interview_Schedule
WHERE (((Interview_Schedule.Interviewer_B)=[Forms]![Main]![NavigationSubform]![cboSup])) OR (((Interview_Schedule.Interviewer_A)=[Forms]![Main]![NavigationSubform]![cboSup]) AND ((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]));
My question is how do I get a report to show all agents at once with their own interview count?
I can make 2 separate queries to look up each field, for interviewer A and Interviewer B, then run a join query, but this results in 2 counts for each agent and I can't sum the two totals together....
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted, Interview_Schedule.Interviewer_A
FROM Interview_Schedule
[Code] ....
View 7 Replies
View Related
