Query Criteria A List Box Record
Mar 22, 2008
Hello Daily Post, I suppose,
Here is what I've got:
On one form resides a list box that get's it's results based on a customers sales history.
The form contains all the customers prudent information, otherwise.
What I'm attempting is such:
Onclick for the listbox is DoCmd.OpenReport "RptIndividualSale" the source for the report is a query whose criteria is the unique SaleID [Forms]![Main]![LstSales].[SaleID] for the records in the list box.
It also has a few other fields such as the item name, sale amount, etc.
My hitch is that it is using every SaleID as a result in the report. What I wish to attain is a report that only generates the information for that single sale, instead of the entire history.
So if the list had SaleIDs of
1
2
3
4
5
and a user clicked 5 a report would open showing ONLY the details of the Sale with ID 5, as stated it currently shows details for all the SaleIDs, so I have a report with 1, 2, 3, 4, 5.
Any way to fix this?
View Replies
ADVERTISEMENT
Oct 24, 2006
I'm very new to access - perhaps this is an easy one...
I have a master table 2 columns wide. I want to extract data from the master table for values that appear in a second table.
That is...
Master Table:
1 a
1 b
3 c
4 d
4 e
6 f
6 g
5 a
My second table
1
6
5
I want to return:
1 a
1 b
6 f
6 g
5 a
I basically want to enter a list of values in the criteria section of the query design view.
Any suggestions?
View 1 Replies
View Related
Dec 12, 2007
I have a query which is based off all fields of the main table. I want to run the query based on one or more choices made from a list box on one field: category.
tbl_ClientMain
qry_ClientCategory - all fields of tbl_ClientMain with criteria under Category as =[Forms]![frm_CategoryList]![lstCategory]));
frm_CategoryList is a listbox form based on qry_Category
qry_Category is a group by query from tbl_ClientMain of the Category(s) collected
When I run the query, I get Enter Parameter Value: Forms![frm_CategoryList]![lstCategory]));
Based on what I've read in various postings and other sources - I must be close, but I'm missing something. Any ideas?
View 3 Replies
View Related
Jan 15, 2006
Hello everyone, i am trying to get 2 listboxs (the first is for name, the second is for state). I need to be able to select a name from one listbox and a state from the other and have a query display after a button is clicked. At the moment i have found a section of code which runs under the OnClick Event Procedure of the button to display the query (one listbox only though). It is as follows:
Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tblCompanies"
'Build the IN string by looping through the listbox
For i = 0 To name_listbox.ListCount - 1
If name_listbox.Selected(i) Then
If name_listbox.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & name_listbox.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strCompanyCounty] in (" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
'Clear listbox selection after running query
For Each varItem In Me.name_listbox.ItemsSelected
Me.name_listbox.Selected(varItem) = False
Next varItem
Exit_cmdOpenQuery_Click:
Exit Sub
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If
End Sub
Is anyone familiar with the code to tell me how i go about adding code to this so to have a second listbox working?
The working example with one list box can be found here: http://www.databasedev.co.uk/downloads/multi_select_listbox_2000.zip
Cheers
Pete
View 5 Replies
View Related
Nov 12, 2014
I have a parameter query where user enter the department number to get their info. I want instead of entering the department number, a list box pops up and ask to select department as parameter and run the same query.
View 2 Replies
View Related
Jun 3, 2012
Is there someway within Access itself or the documentation to see a complete list of:
Functions such as sum, median, average
Operators such as "and" "or"
View 2 Replies
View Related
Jun 11, 2013
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies
View Related
May 10, 2014
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
View 9 Replies
View Related
May 10, 2014
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
View 3 Replies
View Related
Jul 3, 2013
I have a query thats outputs my costs on a project divided into 50 categories. Additionally, each project can be divided into 3 stages, each with the same 50 cost categories. I have been using a query that prompts the user which stage they would like to look at, and it works great.
However, it would be useful to be able to see all stages at once, next to one another.
So, essentially, i want to be able to input the stage parameter in the criteria box for three fields in the query, so instead of showing the costs for one stage at a time, it will show all three(and a summation across all stages), without a prompt.
It works when i set the one field manually to the first stage, but when i add additional fields, it returns a blank query.
View 1 Replies
View Related
Mar 5, 2013
I am trying to create a report which basically includes the following:
Company, Wages, Contribution.
Each company reports wages for each employee every month. Then they also contribute to a general fund based on a percentage of the wages. For instance:
Company---Employee---Wages---Contribution
CompanyA---EmployeeA---$4000---$40
CompanyA---EmployeeB---$3800---$38
CompanyA---EmployeeC---$3800---$38
CompanyB---EmployeeA---$4200---$42
CompanyB---EmployeeB---$4200---$42
...and so on.
Each employee is required to contribute, in this example, 1% of gross wages to the general fund.
On occasion, the company does not pay in the required 1% of gross, say, for CompanyA EmployeeA, they only paid in $35.
Here is what I need to do. If any contribution amount for any employee is incorrect, I want to display all the records for that company, not just the incorrect ones. The report is grouped by Company, and may contain dozens of companies.
I am already passing a number of criteria to the report using a filter, including the date range and other fields which are informational.
View 6 Replies
View Related
Nov 14, 2013
I have table which store set of number
table: parameter
field: Branch
550
660
770
880
I want to use enter query criteria so that it can filter all record from parameter table, How can I do? or any VBA code can serve same purpose?
View 6 Replies
View Related
May 2, 2014
I have a table where the columns are team1, team2 and winner. Team1 and team2 are comboboxes that show all the teams in Team table. After I pick the team1 and team2 in a row I want to make a listbox that shows only those two teams as options for the winner.
View 3 Replies
View Related
May 5, 2006
Good afternoon,
I need to use a selection from a drop down list to change the criteria in a query as opposed to typing text in a parameter box. This is to allow the user to choose from a list and then click on a command button to move to the next query. There are 20,000 records in the table. The series of queries will take the user to 1 or 2 records in 3 or 4 mouse clicks.
Thanks
View 2 Replies
View Related
Jun 28, 2013
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
View 11 Replies
View Related
Sep 13, 2005
Im not sure if this is the right place to put this but I was wondering if you could help me:
Say I have database with forms that display records or people ie name, address, postcode, etc. What I want to do is display a list of Names then from that list I can double, single click and open the record details.
Id call my self and amatuer at access and am probably guessing this is done via vscript or something else, but any help will do.
Thanks
Alex
View 14 Replies
View Related
Sep 16, 2013
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
View 8 Replies
View Related
Nov 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
View 9 Replies
View Related
Feb 4, 2015
I am trying to create a form that allows the user to select a detective and multiple case statuses (such active, inactive, dna, filing, etc.). The form is intended to allow the user to determine the number of assigned cases. The form is tied to a query and the criteria that I've set up in the query is:
[Forms]![DET_CaseManagementFRM]![Combo30] which is under the investigator field.
The case status field has similar criteria [Forms]![DET_CaseManagementFRM]![List43].
My problem is that I cant figure out how to allow the user to select multiple criteria in case status field in my form. The research I've done online says you need to specify simple or extended in the Multi Select field under the other tab in the property sheet. However, when I do specify simple it doesnt return any records which I know it not true. Is there VB code that I need for this?
View 3 Replies
View Related
Sep 16, 2011
I created a search form that searches for Customer Number, Part Number, and Revision Number. The results are displayed in a List Box.
I want the user to be able to make a selection on the List Box and click an "Open Selected" button that launches that record in another form. How is this possible?
View 9 Replies
View Related
Sep 14, 2013
I have an AfterUpdate event where I want a list box to be populated based on three different criteria based on a table in my database
1. Complete = False AND
2. Supplier on form = supplier in table AND
3. Status in table = "SUPPLIER_RFQ FOLLOW-UP" OR "SUPPLIER_RE-RFQ TO OTHER SUPPLIER"
I am having trouble with the last OR statement criteria, i cannot get this to return values correctly. Here is my code:
Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool].[RFQ Contact] " & _
"FROM Consolidated_Master_Req_Pool " & _
"WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool].[RFQ Supplier] = '" & Nz(Me.cboStatusRFQ) & "' And [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RFQ FOLLOW-UP" & "' OR [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RE-RFQ OTHER SUPPLIER" & "'" & _
"ORDER BY [Consolidated_Master_Req_Pool].[RFQ Contact];"
View 2 Replies
View Related
Jun 13, 2013
I am trying to add up a list of dates that match a criteria... a search box result.
I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)
But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.
Code:
Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant
[Code] .....
I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?
View 5 Replies
View Related
Mar 28, 2014
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
View 5 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Sep 5, 2006
Hi all,
Another problem bothering me. Wish someone can help me to solve it. I am doing the 'Edit' Option right now ... As a user friendly propuse, I had try to use a combo box to let user to choose my employee record with all the detail on but the problem is the program 'JUST' list the record they can not go to the current record!! When i try to edit my record, the program is not allow (for sure because they can't have a duplicate record on it). The reason is because the record just stay in the FIRST record but can not go to the current record. I do believe is the problem of i using the way of listing the record as below:
Me.EmployeeName = Me.EmployeeName.Column(0)
Me.IC = Me.EmployeeName.Column(1)
Me.Nationality = Me.EmployeeName.Column(2)
Me.Race = Me.EmployeeName.Column(3)
Me.Sex = Me.EmployeeName.Column(4)
Me.FMU = Me.EmployeeName.Column(5)
Me.Position = Me.EmployeeName.Column(6)
Me.SectionField = Me.EmployeeName.Column(7)
Me.DOEmploment = Me.EmployeeName.Column(8)
Me.DOResign = Me.EmployeeName.Column(9)
Me.Remarks = Me.EmployeeName.Column(10)
So, Could someone help me to solve it like when i can use the combo box to select the Employee record with the details are on as the same time can go to current record... Thanks alot!!!
i do have the confident if i can go to the current record then my edit option can be working properly.
P/S: I had attach the picture... showing my program problem (with the red circle)
View 8 Replies
View Related
Aug 20, 2007
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
View 2 Replies
View Related