How To Query Data For Specific Criteria - Criteria Help
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?
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.
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.
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
I have a table & form which has a field called address. I need to create a query that lets me select all addresses which may contain say George Street, this includes lock ups and individual addresses for that address, i hope you will understand and be able to help
I have a query drawing information from two tables. The first table has information about a product, and the second table holds all the faults found per product. So any one product can have anything from 1-14 faults. I have created a report from the query, but the Fault item that I put on it only ever shows the first fault for that product. Is there some way of fixing this at report level, or on the query?
The tables are connected through the ID field, but the primary key for the faults table is another autonumber. Ex:
That's two faults for the same product. Maybe I could create new fields in the query like Fault1: [Fault] with criteria set to primary key = 1? And one for each fault? But when I do it, it sets that criteria to the entire query, so the results end up blank because the "primary key" field doesn't exist on the product table.
I guess what I'm asking is can you set a criteria for just one field of your query to follow? Or can I resolve this issue in the report design?
Is there a way to search for queries that use specific criteria? Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.
Suddenly we need to also include Province MB to all of these 35 queries. Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.
I hope my explanation is clear. Thanks upfront for any suggestions!
We have a PHP website that utilizes an Access 2003 SP3 database. This website is used as an authorization process for a business process in our company. On the website, 8 different people must "sign off" on the process. This sign off process simply consists of typing your name and date into two different text boxes, as well as a third drop-down menu for "APPROVED" or "DENIED". These, of course, correlate to their respective fields in the Access database.
Within the past year or so, the 8th approval section was added. Since then, there have been sporadic issues where all of the approval fields would simply be erased and everyone has to go back to the form and "re-approve" their fields. I believe we have narrowed the issue to when the 8th person signs off before the 7th person. However, the approvals are not cleared every single time this happens, but this condition exists every time data is cleared. So my question is, would this be an issue related to access, or with the coding of the website?
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
I am trying to set up a number of queries to return completed jobs with a specified time frame, usually monthly. So rather than have to type the same dates in arround 20 times I tried to use the data from a table in the Criteria. This did not work any suggestion would be much appreciated.
Attached is a screen shot showing a fragment of the query and the dilog box I get when it runs.
I was wondering when I am filtering out data from a table, is there a way for me to filter out specific lines (as in, if I have a 100 line datasheet, can i choose to filter out lines 5, 10 and 20 out of the query)?
For instance:
Line Name Address Phone Number 1 A1 fjlafs 453453454 3 A2 fsdfsd 343534534 4 A3 gsdgsdg 354543534 5 A4 gsdgsdgsd 345345345
I want to make it so that lines 2 and 4 are NOT included in the query when I click "RUN".
I don't know what it would be called or even how to start doing it in access other than it requires a criteria here is what I'm trying to make happen with access
name date yes/no criteria would be set to date > 180 days then it would equal value of No < 180 days then equal value Yes
If i set a today's date value on the database and then criteria is based of the value in the date box with the above information how would this be done.
ID (Type Text) Title (Type Text) Remarks(Type Text) Formatted: FormatTitle([title],[Remarks]) Expr1: InStrRev([Formatted], "~")
public functionFormatTitle(ByVal sTitle as String, ByVal sRemarks as String) as String 'do process code here very complicated an long, but works find in the end 'creates a Multi-String delimited by | (pipe) end function
The above works, and Expr1 does give an accurate value for the position of a "~" (tilde) in the string Created by the FormatTitle() function.
However, If I put a Criteria >0 on Expr1 it asks for the value of the [Formatted] field as if it was a parameter. If I put a criteria for Formatted: Like "*~*" I get a Data Type Mismatch in Query Criteria
This Query Also produces the Data Type Mismatch in Query Criteria pardon me, but WTF? If it isn't a STring, than InStrRev() should produce an error, not an accurate response, and if InStrRev() produces a number why can't i compare it to 0 (zero)? This is indubitably messed up that I'm getting this error. There is no data type mismatch, on either of these tests, one is a string and I criteria-limit it by a string operation, the other is a number and I criteria limit it by a number, WHAT IS GOING ON!!!
Thanks Jaeden "Sifo Dyas" al'Raec Ruiner - The Frustratedly Confused
There is data in the [Year] field and the [Season] field.
I placed a button on the subform to create a report based on the data of the current "Sub Record" .
Basically, I want to use the data in the subform to create a small "Lookup" report.
OK:
I have the Management Form I have the Management History SubForm I Placed a Button Called PRICE LIST The button kicks off a Macro that previews my PRICE LIST REPORT A Query is the RECORD SOURCE for my PRICE LIST REPORT.
In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.
I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.
What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??
I tried the following:
Like [Forms]![Management]![Management_History].[Year]
Like [Forms]![Management]![Management_History].[Season]
i have created a query to do the calculation of how much stock came in went out and is still on hand .the problem im having is that it shows me the 0 total for product with no stock in it when i put my criteria as >0 in my goods on hand field it show me no data in the query.
my calculation fields is as follows GoodsOnHand:[GoodsIn]-[GoodsOut] Goods in gets calculated as follows GoodsIn:Nz([SumOfGoodsIn],0) Goods out get calculated same way as goods in.
The Reason i have a sum of goods in is because i have to calculate the quantity in another query because it is spread over lots of locations.
The code below allows me to open a publisher document that is already merged to the current record. What I need is to allow the user to open the document according to the group they are reviewing. How to accomplish this?
For example:
IF Group = A1 OR B1 THEN OPEN AB_1.pub IF Group = A2 OR B2 THEN OPEN AB_2.pub IF Group = A3 OR B3 THEN OPEN AB_3.pub
Dim pubApp As Publisher.Application Dim pubDoc As Publisher.Document Set pubApp = New Publisher.Application Set pubDoc = pubApp.Documents.Add.Application.Open("c:Publishe rAB_1.pub) pubDoc.ActiveWindow.Visible = True
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code: Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code: [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code: Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2] Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5] Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
I am attempting my first access database which tracks blood components in our medical facility. When a component is issued to a patient there is a button to push which links to a report that runs a query. The user must enter the unique key for the component at a promt, which ensures the report generated will be for that component only. My question is if there is a way to cpture and use that key automatically when pressing the button. If I need to clarify something, please let me know--I may not have the Access developer lingo down yet...
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
