Query Based On String Value In List
Apr 8, 2008
Hello again.
I have a drop-down list that contains string values. Is it possible to construct a query that will sum the total number of times that the string value is selected? For example,
String Value Sum
================
Chicken 3
Beef 9
Pork 2
I have been trying to work on this for a while and cannot figure it out. Can someone shed some light on this issue? Thanks.
View Replies
ADVERTISEMENT
Jan 3, 2008
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
View 7 Replies
View Related
Sep 10, 2013
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
View 1 Replies
View Related
Jun 18, 2013
I'm using Access 2007.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
View 11 Replies
View Related
Feb 10, 2006
Dear all, is it possible to run a query (or ideally a report) based on a selection from a drop down list in a form? (in accsess 97). The reson being is i have a big list of of codes for different absence reasons & at present the user has to type in the correct code to display the data in a report based on the code they enter, however its not easy for users to remember which code to type in to run a report.
Idealy what i would like is a drop down list with the absence codes & the absence desciption next to each code, then when the user selects a code from the list it displays the corresponding data in the report?
Is this possible?, any help would be much appreciated, thanks.
View 4 Replies
View Related
Oct 3, 2014
I have a list box (List25) that is generated from a query (qryThisBox). One of the fields in that query uses the criteria [Forms]![MyForm]![SelectedField] to filter.I also have combo boxes in the form that List25 displays in. Unfortunately, I want to requery the list box in the form, using a combo box that calls the same underlying table field, SelectedField.
How can I do this? Can I somehow dynamically change the criteria in qryThisBox to change when it gets requeried?How do I stack combo box requeries, eg: Combo Box 1 gets selected - does a requery
and/or
Combo Box 2 gets selected - and filters the results of the requery for Combo Box 1.
View 1 Replies
View Related
Jul 31, 2013
I have a query that allows the user to put in a date range - this works.He also wants to limit what he prints.
For example at the moment he gets
OrderA rest of line 1 for orderA
OrderA rest of line 2 for orderA
OrderA rest of line 3 for orderA
OrderB rest of line 1 for orderB
OrderC rest of line 1 for orderC
OrderC rest of line 2 for orderC
OrderD rest of line 1 for orderD
.
.
.OrderZ etc
He only wants to say print lines for OrderA and OrderD. It's he's choice and not based on anything else already in the table.
I see that in the criteria on the design for the query it allows me to bbuild an expression and I can select a list box on the form such as
[Forms]![name of form where list box is]![name of list box].[EXPRESSION VALUES]
I don't know which expression value to choose. I have tried afterupdate and beforeupdate but none of the rest look like they are the ones I should be using.
View 1 Replies
View Related
Feb 8, 2008
I have a textbox that is set equal to countitem field of a query. The query is based on a subform that exists on the same form. I am trying to update the textbox every time the number goes up or down on the query. Whenever I open my form the number for this textbox is updated. Can I update this field without having to open the form everytime?
View 1 Replies
View Related
Jul 13, 2015
I'm an Access novice. I have a query based report that is based on several tables. All tables are joined by the same field "customer ID," but 1 table is not available under "Add Existing Fields." I cannot figure out why that table isn't available, but I need to add a field.
View 8 Replies
View Related
Feb 23, 2014
When creating a query I have set up a parameter query as instructed (in this case searching for a name) but when I run the query it returns the error 'this expression is typed incorrectly etc.'
I have worked out that this must be due to the fact that the names are not typed in by a front-end user but need to be chosen from a dropdown list (which i have stored in a separate table) - So, the query seems to fail based on that.
View 10 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
Aug 14, 2014
I have a table x where the field value is selected via a combo box in a form that is populated from another table z.
When I look in x, it appears to have correctly stored the text from z, not the ID number.
I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.
Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.
How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.
View 5 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
Sep 15, 2014
I've setup a selection form that returns a specific list of email addresses in a query.
What I can't work out is how to press a button and have the email addresses populate the To: field in a blank Outlook email - I don't need any bells or whistles.
View 8 Replies
View Related
Sep 12, 2014
I have a select query that holds anywhere between 1 to a couple hundred records, I need to take the Name field and populate a textbox which after verification I write to a text file that I later use in a powershell script. So far have tried using VBA to create a variable and populate the textbox based on the string but it only provides me the first item, I've tried dmax and this gives me a single result last Item, Dlookup only gives me the first item, changed it to a listbox which populates the box correctly but only writes the selected item in to my text file.
View 7 Replies
View Related
Oct 30, 2014
I am currently developing a calendar and am trying to open a recordset based off of a SQL string. When I deleted the Where part of the SQL statement, the code ran fine. So I am pretty sure that the problem lies within the Where part of the code. I use this code to filter my query based on txtTaskTypeID but if the value is null then the query is suppose to return all values. I keep getting Run-time error"3061: Too few parameters. Expected 1".
Code:
strSQL = "SELECT tbl1CalendarEntries.ID, tbl1CalendarEntries.Title, tbl1CalendarEntries.StartDate, tbl1CalendarEntries.StartTime, " _
& "tbl1CalendarEntries.EndDate, tbl1CalendarEntries.EndTime, tbl1CalendarEntries.TaskTypeID " _
& "FROM tbl1CalendarEntries " _
& "WHERE (((tbl1CalendarEntries.TaskTypeID)" _
& " Like IIf(IsNull([forms]![frmProductionPlanning]![txtTaskTypeID])=True,""*"",[forms]![frmProductionPlanning]![txtTaskTypeID]))) "
& "ORDER BY tbl1CalendarEntries.Title;"
View 5 Replies
View Related
Jun 11, 2014
I have a continuous form that displays data from a table. What I am trying to accomplish is to change the format of the field if the string length is equal to 11, to "@@@-@@@-@@@@-@". Otherwise if it is greater or less than 11 then no format.
I have tried it using code:
If Len(Me.FormFieldName)=11 Then
Me.FormFieldName.Format = "@@@-@@@-@@@@-@"
End If
Is it even possible and if so is my syntax correct or am I way off base?
View 8 Replies
View Related
Jan 16, 2014
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code:
Dim db As DAO.Database
Code:
Dim db As DAO.Recordset
Dim srtSQL As String
[code]...
View 14 Replies
View Related
Jun 20, 2005
I have a form that contains a list box with 11 separate options. Within this list is the option named "None". My desire is to have a message box displayed when any option is selected other than "None". I have searched various threads, however I have been unsucessful nailing down my issue. Thanks in advance for the assistance.
View 1 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
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 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
Aug 24, 2007
Hi,
I've been using this forum for a couple of weeks but this is my first post, so apologies if I've put it in the wrong place!! I have a table with some drop-down lists to fill certain fields, and I would the available list options to change based on information in other fields. To provide an example:
Field One: The drop down list lets the user choose "Alphabet" or "Number"
Field Two: The drop down list lets the user choose A-Z if "Alphabet" is selected in Field One or 1-100 if "Number" is selected in Field One
Hope I've been clear enough. Any help is greatly appreciated!!
Cheers,
Matt
View 3 Replies
View Related
Nov 20, 2006
Hi,
I have a form with 1 List Box. This list box contains the names(SSN, Department, Status) of the columns in TableA.
The question is, can I create just ONE query statement, and base the GROUP BY on whichever field the user selects?
For example, if a user choose Department, then the query will group by Department. I know how do this by the long way; meaning I create 3 separate for each,(qrySSN, qryDepartment, qryStatus) and if say the user select SSN, it will open the qrySSN query. This is way too much maintenance. I have a feeling there is a much easier way. Please advice.
Thank you.
Joe
View 3 Replies
View Related
Mar 24, 2005
how do i filter a form based off what you select in a list box?
thanks
chad
View 7 Replies
View Related
Mar 17, 2015
I want to create a combo box on a form where I can choose between 5 company names, and upon my choice, I want a list box or another combo box to display the departments under the company I chose (each company has different departments), and when I choose the department, I want to another combo box to display the limited job titles under that department.
For example: I choose company A > combo box displays the 5 departments under company A (dept.1,2,3,4) I choose dept. 4 > display job titles under dept. 4
How can I do that? Should I do a table? A query?
View 3 Replies
View Related
