Form Based Queries
Sep 18, 2006:confused: G'day, can anyone tell me how to quickly create form based queries using the design view interface. I want my users to be able to specify the various criterias using drop down menus. :confused:
View Replies
ADVERTISEMENT
Queries :: Query To Run Before Report Based On Criteria Based From Two Combo Boxes On Form
Mar 20, 2013I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies View RelatedQueries :: Change SQL Where Based On Form Controls
Jun 30, 2015I have a form with a combobox, textbox, a button and a listbox.
Currently the button checks if the textbox is empty and if it is, it runs a SQL query to select data using the combobox value in the where clause, else it selects data using the textbox value in the where clause
I need to be able to type or select values in both controls and use those values in the SQL query as a where.
Queries :: Filtering Query Based On Form
Jan 16, 2014I have a form named form1. In the form I have two unbound text boxes formated as general date; startdate and enddate are the text box names. In my query criteria for the ContactDateTime field I put the following code
Code:
[Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate]
When I run it I don't get any results. So it runs but no records come up. I have about five queries that run when I click a button and I want to be able to just select start and end dates once on the form and have all the quieries run.
Queries :: Search Query Based On Form?
Nov 13, 2013I am unable to get multiple search output using the below code in queries, the search text is based on form :
Search form name : Find
Query name : Search
Table name : Sheet1
[forms]![find]![city]
The above works fine if it is only used in one of the fields, but as soon as I have it in multiple fields the output is null.
I am aware that i can used Like "*" code, but the thing I want to find records which are perfectly matching based on the form.
How To Find Record On Form Based On Multiple Queries
Jun 23, 2006I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.
I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.
I have no idea how to accomplish this...can anyone help?
Queries :: Dropdown Box - Find Records Based On A Form
Jul 16, 2014I have a form with a dropdown box that shows the 'Group by' records in a table.
For example the form shows "Art_6". My table has many records (could be 1000's) that start "Art_6".
I want a macro/query to find all records that start "Art_6" and delete them.
How to find only those that start as described.
Queries :: Set Criteria On A Query Based On If A Form Is Open Or Not?
Jun 18, 2015What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.
Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?
Queries :: Dynamic Query Based On A Form (ComboBox)
Jan 22, 2014We have a ComboBox on a form with the months: Jan, Feb, Mar, Apr, etc.
We have fields in a budget table named: Jan, Feb, Mar, Apr, etc.
We want to create a query that pulls the correct field based on the value in the combo box.
This is what I have that doesn't work:
MyField: [Query1].[SumOf] & Forms![Main Menu]![test]
How to do this?
Queries :: Change Query Field Based On A Form
Oct 29, 2013I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
Queries :: Dynamically Add Fields To SQL Or Query Based On Form Selection
Nov 6, 2013Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
View 3 Replies View RelatedQueries :: Query Results Based On 2 Form Control Values
Oct 29, 2014I have 2 form controls one a combobox and the other a text box. The text box select the site (txtLocation) where the user can enter part of the name of the site and all sites with those characters are returned. I've done this by adding:
Code:
Like [Forms]![frmSearchDB2]![txtLocation] & "*"
into the criteria on the Site field in the query design editor.I also want the combobox to have an affect on the query. I want it to query on user status. However if the combobox reads "All Users" I want it to return all status's and all null values. In the criteria field I put:
Code:
iif([Forms]![frmSearchDB2]![cbxUserStatus] = "All Users",like "*",[Forms]![frmSearchDB2]![cbxUserStatus])
It kind of works but no null values are pulled back. Should it be an expression?Do I need to do it in VBA?
Queries :: Open A Query Based On Two Or More Fields Of A Continuous Form
Nov 22, 2013I have a subform which is a continuous form displaying a single combobox of trade names so there may be up to 4 or 5.i want to click on a button on the main form which will open a form based on a query that will display all personnel matching all or 1 of the trades in the subform?
View 2 Replies View RelatedQueries :: Query Based On A Form Which Had Field Names And Some Values
Feb 26, 2014I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.
I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.
E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.
I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).
The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?
Form Based On Queries - Deleting Field From Main Table
Mar 28, 2014In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
View 1 Replies View RelatedQueries :: Drop Down - Query Not Combining And Filtering Values Based On A Form
Jul 22, 2014I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.
The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.
I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").
Using Windows 7,
Access 2010
Is there a way to select multiple values from the drop downs?
Queries :: MS Access 2010 - Put Month On A Form Based On Data In Query?
Mar 26, 2014How do you put the Month on a form based on the data in a query?
View 1 Replies View RelatedHow To Handle Multiple Null Values Using Form-based Parameter Queries
Nov 19, 2012i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
Queries :: Running Query Based On Form Text Box Criteria With Special Features
Dec 18, 2013I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
Queries :: Form List Based On A Query Returns Original ID Value Not Field Text
Aug 14, 2014I 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.
Forms :: Parameter Based Query - Filter A Form Based On Another Form
Mar 20, 2013I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
Queries :: Differentiate A Query Based On All Group Records Or A Query Based On Only One Record
Dec 22, 2014I 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 RelatedQueries :: Adding Calculations To Queries Based On Columns In Query
Feb 18, 2014I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
Chart Based On Form Based Parameter Query
Mar 22, 2006Hi all,
stuck on this, not sure if it can be resolved....
I have a parameter query for which the parameter is based on a form field entry (i wanted to avoid the dialog box popping up).
No problems with this....however, I want to create a chart based on the same query however, i get the following error message because it does not recognise the query parameter.
Error Message: The Microsoft Jet database engine does not recognise '[Forms]![frmChooseDTDate]!DateOccured' as a valid field name or expression.
Can this be resolved?
Help most appreciated!
regards to all
K
Queries :: Creating A List Based On Multiple Queries
Jun 23, 2015I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
Queries :: Sub-queries With Text Box Based Parameter
Sep 18, 2014I'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];
[code]...