I created a form that has 3 multiple drop-down selections that is supposed to select records from [TableName_1] based on those selections. I initially created the query using the query designer with actual "hard coded" selections to make sure it worked. When it gave me the expected records, I changed the criteria from the "hard coded" option to the input form's control reference [Forms]![FormName]![ComboName]. I did this one at a time and tested after each change.
After the first change, I got exactly what I expected.
After the second change, it seemed okay. (note: I put the criteria on the first criteria line in the query designer, not on the second line)
After the third change, I get the dreaded: The expression is typed incorrectly or is too complex to be evaluated....
Here is the SQL:
SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA
FROM (SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA FROM M_Lending_Institution) AS LenderSearchQuery
WHERE (((LenderSearchQuery.GeoRegionID.Value)=[Forms]![LoanSearch]![CmbPrefGeo]) AND ((LenderSearchQuery.SpecialtyID)=[Forms]![LoanSearch]![CmbSpecialtyArea]) AND ((LenderSearchQuery.SBA)=[Forms]![LoanSearch]![CmbSBA]));
And once this is sorted out, yes I want to be able to allow for all records to be returned if any of those fields is NULL. I've read a LOT of posts around this subject, but I can't seem to find anything that answers my question without jumping into advanced VBA code.
I am creating an absenteeism database which has the following tables:
tblEmp - PK - auto number, EmpId, First Name, Last name tblFunction - PK - FID (autonumber) has departments tblTL - PK TLID (autonumber) has list of team leaders tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism
I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis
Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.
In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.
Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far.
Private Sub SingleItemRptB_Click() If DCount("[FieldName]", "[Table]", "[FieldName]=[TextBoxValue]") = 0 Then MsgBox "Item not in database. Please check value and re-enter." Else DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly End If End Sub
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.
Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").
Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want
As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.
For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.
Right now the queries are all configured as the "Select" type.
have a query that works fine when I have to select one parameter, however I don't know how to select multi parameters...In this query I would like to able to select as well as the specific 'POSTITION' value Also 'BASE' and all those parameters that have a check box empty or full.
Code: SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.Name, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMember.StartingDate, tblCrewMember.Resined, tblCrewMember.ResinedDate, tblCrewMember.Birthday, tblCrewMember.IDCrewMember, [GroupBy] AS Expr1 FROM tblCrewMember WHERE ((([GroupBy])=[Position])) ORDER BY tblCrewMember.StaffNumber;
I have a simple question that I know will have a very complex answer
You have two sets of tables with identical structures. The first set contains data that is a model for the second set. The users run queries that append data to the second set of tables
Here is an example of the structure TblSourceOne SourceOneID SourceOneData
The user adds a record to the main table (TblTargetOne) then uses an append query to add all of the applicable records from TblSourceTwo to TbleTargetTwo
So now the user has a data model in TblSourceTwo which has been “copied” to TblTargetTwo
Next for each record in TblSourceTwo that has been copied to TblTargetTwo that has daughter records in TblSourceDetails another query is executed to copy all of the pertinate records to the TblTargetDetails
Now the user has completely copied all of the necessary record for both table.
Finely the question
How can I use a multiselect list box to achieve this in one operation? I don’t care if it includes running multiple queries but it needs to appear as one single operation to the user.
I have a query set up which needs to have different criteria at run time depending on values selected by the user. If no option is specified, I need to pick up all records with 'Nulls' in them else, if date is specified by user, I need to pick up all records with date > than specified date. The query is able to pick up the date value from an unbound text box in the form. I used the same field in the form and populated "Is Null" in the field and it does not work. How do I pass this as a paramater to the query from the form? Is there a way around without setting up 2 sets of queries and reports?
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
I am just starting out in Access Development. I have a database that runs a logistics department. This was working fine until the users wanted a little more tweaking.
The report prints out the vehicles with all corresponding drops. This comes out all on sheet.
Is there a way that I can create a form to filter the report via a multi select listbox and print each drop selected on a seperate sheet of paper.
Morning - I have been searching a bit but to no avail. I have a query (qryTopTenList) set up in a database that selects the Top Ten "Classes" from a table with a key field of "insclass".
I have a multi select list box (LstInsClass) that allows end users to select multiple insurance classes. What I would like to do is set up a standard button that when clicked, will loop through all of the insclass in the query "qryTopTenList" and then select those same insurance classes in my multi select list box. So the end result is that 10 records in the List Box would be selected.
This is helpful b/c it prevents the need to go through the entire list box to select the top ten classes.
I have created a search form which I would like to use to run a query (so the data is in a spreadsheet form and I can export it).
In this search form I have a multi select list box (simple) that list the states in the US. I need to be able to pass 1 or more states as a search criteria at the same time. Also I need it to pull ALL states if there is nothing selected. The search form has a bunch of fields on it that won't all be used.
I know once you select more than one thing from a list you can't reference it directly, is there a way to accomplish what I want to do?
For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.
I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.
I've been able to get a string created using the example posted here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909) and it's in the format of "54,67,89,100" etc.
Public Function Public Function fMultiSelect(ctlRef As ListBox) As Variant Dim Criteria As String Dim i As Variant
' Build criteria string from selected items in list box. Criteria = "" For Each i In ctlRef.ItemsSelected If Criteria <> "" Then Criteria = Criteria & "," End If Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000") Next i
fMultiSelect = Criteria gMultiSelect = Criteria End Function
I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.
Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.
Am I missing something here?
The long explanation:
I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)
Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.
Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.
I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.
Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?
Hi, I need help in creating a parameterized query.
I need to show only certain fields depending on the value of the parameters. For example, I have a table with fields: ID, Name, Phone Number, Address, DOB. The parameter can only be value of A, B or C. If the user input A as the parameter in the query, only field ID, and Name will be shown If the user input B as the parameter in the query, field ID, Name and Phone Number will be shown. etc.
Is there any way to do this, rather than to separate the query into 3 queries for each of the parameters? Thank you.
i want to open a recordset, in its 'where' clause i want to pass the value which i entered in Form textbox? is this possible ?
COde: rs.open "select * from table1 where id=form.textbox",con
i have written general code above. when i run my application ..it gives error "Control which is focus can only be reference" how can i pass my value to select query ?
Hi, is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help. Thanks!
i have a multiselect listbox in my form. The multiselectlistbox contains the names of different persons from tblUsers. it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform. I can display the values using a valuelistbox, but i need to see the non-selected items too..
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
i need some major direction! here's what I need to do.... i need to generate a report based on a table(no big deal). However, I need to generate a query that lists all users from the table(still no big deal). Here's where the fun comes in... I need to use that query to populate a listbox and allow multiselect on the listbox. The table data that is related to the values from the listbox that are selected are NOT to be showed on the report. So I imagine I need to create a query where the criteria is <>the value(s) in the listbox. So I basically have two problems... 1. getting the value(s) from the listbox (the only way i seem to be able to get a value from the listbox is if I do not allow multiple selections--i'm obviously overlooking something) 2. using the value(s) as criteria to limit my query results(not sure how i would do this, since it's more than one value--possibly an array??)
I guess i could also do it the opposite way, too(select users that you want)...but either way, i have no idea how to go about it. Please help~I'm going crazy!!
I am looking to see if anyone can help i have been able to get the multi select function part of my form working but i need another filter in the query for a date but it keeps deleting this part of the query everytime i run the report what i need to be able to do is get all jobs against a specific leadcraft that are in the past and also 31 days into the future.
Paul
here is the sql so far, thanks to those who helped already.
Private Sub preview_Click() Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String Set db = CurrentDb() Set qdf = db.QueryDefs("FAelecplan") If Me!Leadcraftlist.ItemsSelected.Count > 0 Then For Each varItem In Me!Leadcraftlist.ItemsSelected strCriteria = strCriteria & "MAXIMO_V_WORKORDERS_FA.LEADCRAFT = " & Chr(34) _ & Me!Leadcraftlist.ItemData(varItem) & Chr(34) & "OR " Next varItem strCriteria = Left(strCriteria, Len(strCriteria) - 3) Else strCriteria = "MAXIMO_V_WORKORDERS_FA.LEADCRAFT Like '*'" End If strSQL = "SELECT * FROM MAXIMO_V_WORKORDERS_FA " & _ "WHERE " & strCriteria & ";" qdf.SQL = strSQL Dim stDocName As String stDocName = "elecplan" DoCmd.OpenReport stDocName, acPreview On Error GoTo Err_preview_Click Exit_preview_Click: Exit Sub
Err_preview_Click: MsgBox Err.Description Resume Exit_preview_Click End Sub
I currently have a database of customer information and form set up with a MultiList box based on the regions those companies are based. You select the region(s) you require from the list box, click a button and it runs a query.
However I now want to be able to filter the results further using a combo box with business sectors in also.
The code used for the multilist box is as follows (I don't totally understand all the code if I'm honest, but it works )
Private Sub Command2_Click() Const cstrQuery As String = "CAMPQRY" Dim strNames As String Dim strSelect As String Dim StrWhere As String Dim varItm As Variant
I have been working with Access for some time managing some databases for my company. But now I have some problems making the database easier to use by automatically creating a Query based on different criterias.The code looks like this:
Sub Multi_value() 'Definitions Dim strSQL As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = Application.CurrentDb
[code]....
The problem is that the "Business Field.Value" is inserted into the Query-criteria with brackets which for some reason are not supposed to be there when I use the .Value-parameter instead of the "Business Field"-parameter.
