Query Criteria If On Form Field Limit, Otherwise Do Nothing?
Apr 26, 2006
I tried to word the title as accurately as possible.
What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.
I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.
There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?
Hello I have an access 2003 db. I have a csv file that regularly downloads from a propietary system. This file contains about 18,000 records. Some customers have more than one record so there are about 7000 customers. I have linked this table to access to benefit from the regular download changes. About 150 of these customers are accounts I want to filter out of my db. I tried a query criteria Cust# Not "(Cust#) or (Cust#) and so on. Access will not let me have this many in my query. Is there another way?
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
I am creating a training database and first I have tables in relationship to the courses:
tbl_Courses Course ID (Primary Key) Course Title Objectives
Instructors (lookup field allowing multiple instructors to be selected, meaning they are skilled to teach the course)
tbl_Instructors Instructor ID (Primary Key Last Name First Name Full Name (Caluclated to put Last Name, First Name)
[code]....
What I am trying to do on a form to create a new event is once the user selects the Course from the Course ID combo box, then I need the Instructor combo box to only display the instructors who are skilled to teach the course which are selected in the tbl_Courses.
I can get all Instructor ID's, but not the names and the class that has multiple instructors show on one line.Should I have not put the Instructors field in the tbl_Courses? Do I need another table for instructor skills or something?
Ok I have a simple problem. I want to entry value of a field as criteria of a query. Ie. I have a field on a form which is EmplyeeCategores. It's simple to use this as a a filter in a query by simply putting Forms![frmMailer2]![EmployeeCategories] in the criteria of the EmployeeCategories column in the query if there was only 1 criteria.
What I mean is, if the value of the field in form was A1 for example, it'll work and the query will bring up all emplyees who are categorised as A1. But I want users to be able to put multiple codes in the form, such as A1 or A2 or C4, and the query to bring up all the employees that fit into all 3 of these categories,
I hope this makes sense. Any help will be much appreciated.
We have a database with one main data entry which stores records in a seperate table. There are several thousand records keyed in using this form. Therefore, alongside the entry we have a search form which allow the user to search for a specific record and display it in the main if he/she needs to edit one.
We have received a request from users for a functionality which allows users to browse all records, using the main form layout, that they have keyed in. I guess what I am asking is: Is there a way to limit the records visible in a form using field on that same form as a limitation (ie. the users name - which is a required key-in)?
I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.
Any pointers in the right direction would be greatly appreciated.
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code: SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU FROM Calls GROUP BY Calls.SKU HAVING ((Not (Calls.SKU) Is Null)) ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
I have built a query (Loading_query) that brings in the prefix, number and date_loaded (=null) for each rail car.
I now want to display the rail cars numbers in a drop-down but be limited by the prefix selected in another drop-down on the form.
For example:
If the prefix that was selected = GATX then the next drop-down should use "Loading_query" to only show the rail cars that are empty (according to date_loaded) and have a prefix that = GATX..... Still new at this and do not know how to use a value from the form to limit the query.
hi i have created a form bringing together 3 queries. one of the queries contains images the other 2 queries are text/data information. i am now creating a report to print out the queries. each query is made into a report and then combined with a main report [2 of the queries are subreports]. the code i have used to link the images so that i get a unique image with every record is
me.[cempic2].picture = me![combined_mage_path]
however when i print the report if the record contains more then 1 image i get the following [screenshot 2]. what i need to do is to limit the image print out for each record to the first image only. the only common naming criteria for all my images is 001.jpg, 002. jpg and so on what i need to do is to show only the image for each record that ends with the criteria *001.jpg. how would i do this in VB? thanks
I've attached a zip version of my database (which is still in the early stages of development) as I think it will be tricky to explain what I need. I've studied lots of other threads but can't find the answer I'm looking for.
Basically I have a form "Goods-in" which has a subform in continuous view that lets you pick from a product list. I'm trying to limit this combo to products that relate to this supplier only, but (it seems) because the subform is based on a query I can't update it.
I have tried several approaches but got nowehere. At this stage I don't know whether the problem is with - how I'm linking the parent and child forms - the SQL of the combo recordsource - my relationships - my overall design - the weather! - etc.
I would be eternally grateful if someone could take a look and advise me. The items shaded grey on the forms are just my shorthand for what will be invisible in the final version.
I'm going home now and even though I may read replies tonight, I wont be back in the office until 9am.
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10 1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
In one table I want to limit the options of one field depending on what is chosen in another field.
Field Type has two options: Receivable and Payable.
I want field Sybtype with this limits:
If Receivable is chosen in Type field you will only have CreditCard & Lockbox available in the Subtype field. If Payable is chosen you will only have Cheque & Transfer to choose. Shall I have two separate tables? or only one table with Type and SubType as below? but then how can I make the above work?
Type SubType Receivable CreditCard Receivable Lockbox Payable Cheque Payable Transfer
I have a fairly simple database that looks like the attached. It is the beginnings of a school database. Unfortunately, the MS template doesn't quite do it.
We have around 700 pupils in 5 year groups. Each year's intake is assigned the year of that intake (e.g. 2002, 2003 etc).
The youngest pupils are in Year 7 whilst the oldest are in year 11.
Each Class is assigned based on the intake year but given the name of the schooling year - (e.g. year 7, year 8 etc). And there are 6 classes per year - hence names 7.1, 7.2, 7.3 etc.
My "Students and Classes" table should combine both the classes and pupils. However, I want to be able to firstly select the class to put the pupil into (and hence infer the induction year), then just show in the next filed only the pupils that belong to that induction year and hence go into the selected class.
However I design my lookup or query, I can't seem to figure out how to do this, or if this can even be done.
I am not sure how to search for this so if there is another thread with this example please direct me in that direction. What I am trying to accomplish in my query is the following: I am bringing data in from another source in the following table
ID Date1 Date 2
I want a query to do the following based on dates entered on the switchboard in a beginningDate field and EndingDate field
ID Date1 Date2 Final1 FInal2
If Date1 is between beginning and ending date I want it to put a yes in Final1 if Date2 is between beginning and ending date I want it to put a yes in Final2. Both can have yes'.
I tried doing an if statement in my expression as follows: IIF([Date1] between [Forms]![FrmSwitch]![BeginningDate] and [Forms]![FrmSwitch]![EndingDate], "Yes", "") but nothing comes up. My switchboard is always open with the dates in an unbound field.
Hello, ...I'm not sure if I'm making this question more complex or not or if it's even possible? (I would think so) Anyway...What I am trying to do is create a form in which I can input more than one criteria into a field - We will cal it txtSetValues. Then via a button open a query that will use txtCode as the criteria for searching records in a table. The table is called: tblMASTERTABLE-EmpCount query is called: qryEmpCount
The table currently only has two field; ORG and Name. The criteria I have in ORG is: Like [Forms]![frmEmpCount]![txtCode] I currently can find any specific ORG just fine. I can also use the wildcard and that works fine. But how can I set it up so that I can enter in multiples... For example, I would like to find all employees that there org is either; AL001 OR AL010 OR AL100 - The wildcard will 'Almost' do it in some circumstances, but it may gather some orgs that I don't want in this list. I hope I am explaining this in an easily understood fashion?
Thanks, ---roystreet P.S. Now I know I could just make multiple fields in the form (ie; txtCode2, txtCode3,etc) and then just add those as criteria in the query, but I was hoping that I wouldn't have to do that.
I'm designing an access system in which I have many reports (Primary Key is reportNumber) I need a form with a field in which I can speicify a range of reportNumber to print them.
example I need to print reports from 1000 to 2000
please note than I'm a beginner in this so I need detaild steps.
PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields
Hi all,I posted something similar to this beforehttp://www.access-programmers.co.uk/forums/showthread.php?t=124289But i didnt get it figured out.Is it possible to use the same field for multiple criteria in a query?the one i would like to base it on is taskID.i just want the total time to be called admin time if taskid=2 and investigative time if taskid<>2.Ive tried it with single and multiple queries in one and am recieving errors with both. help is always appreciated!Woohoo for 100 posts!
