Select Top X Amount Criteria.
Jul 14, 2007I'm ok with Parameter Queries now, but I'm stuggling for the certeria to select say the top 20 records for a list. This would be based on a number, say amount spent.
Any help?
ADVERTISEMENT
Select First X Amount Of Records
Feb 27, 2008Hello All
if I have thirty records and have the end user select the first 8 or what ever amount they want, could be 3, 14, 9 etc, is this possible via a form ?
thanks
General :: Calculate Deposit Amount For Current Month - DSUM With Multiple Criteria
Mar 23, 2013I am trying to get deposit amount for the current month but results are in accurate i am using this
DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))
Modules & VBA :: Adding Amount And Checking Daily In Hand Amount?
Jul 21, 2015I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
General :: Multiply Different Amount Of Hours By Different Amount Of Rates
Jul 17, 2014I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
Criteria For Select Query
Aug 27, 2007Hello,
I am struggling writing criteria that is based off of multiple combo boxes in a search form. I basically have a form setup in several pages the first page being a search page. This page consists of three combo boxes which are populated from the tables. These three boxes are all related. The way the search is setup at the moment is if the combo boxes are Null then they report all the data in the fields but if something is selected then the query gets requeried and displays the data based on that criteria.
What I need to have it do is if it is Null report everything but if something is selected in the first box that selection will also be apart of the second criteria and third criteria.
What I have written at this time is;
First Box:
IIf(IsNull([Forms]![FormName]![Combo1]),[Data1],[Forms]![FormName]![Combo1])
Second Box:
IIf(IsNull([Forms]![FormName]![Combo2]),[Data2],[Forms]![FormName]![Combo2] )
Third Box:
IIf(IsNull([Forms]![FormName]![Combo3]),[Data3],[Forms]![FormName]![Combo3] )
My intentions are too write something that allows the criteria to use the other values in each combo box. The problem I have is it keeps spitting an error out at me which says I have the wrong number of arguments.
Here is what I am trying to accompolish.
PsuedoCode:
If (Combo1 =Null) {
Select all values of Data1
Else {
Select all values of Data1 with match value of Combo1
}
If ( Combo2 = Null) {
If( Combo1= Null){
Select all values of Data2
}
}
Else{
Select all values of Data2 with matching value of Combo1 and Combo2
}
}
Any help will be greatly appreciated.
Thanks!
Setting Criteria Within A Select Query
Aug 23, 2005I have set up a query to pull data from within a date range.
I have written an SQL Statement to amend the format of the date field:
Effective Date: IIf([Date_Effective_From]="00000000",Null,DateValue(Mid([Date_Effective_From],7,2) & "/" & Mid([Date_Effective_From],5,2) & "/" & Mid([Date_Effective_From],1,4)))
Within my criteria I have:
Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2]
(Benefits being the form). If I enter date range 01/01/2005 to 01/05/2005 I receive all data from 01 January to 01 May - but including different years i.e 2002, 2003, 2004 etc.
Can anyone suggest anything I may have missed?
Thanks
Using Combo Box To Select Data Criteria
Jul 11, 2005I'm having trouble creating a query.
I work for a company which have a load of cd's which i've put into a database.
the database consists of a table which lists the contents, date created, department etc.
I've created a form where users can select (using combo box's) criteria to search the database.
It works well if the user selects relevant data from each combo box but i have now added a "select all" function to each combo box incase the user doesn't know the specific criteria.
This is where the trouble starts. I cant work out how to create the query so that if the combo box is on "select all" how it will show all records for that particular field.
I have tried to use an IIF function for each field in the query but i always get errors.
Please help.
ComboBox To Select Filter Criteria
Jan 11, 2006I'm building a filter using comboboxes and I'm having trouble with two of them. One is to filter by records that are completed or by records that are not completed. The other combobox is to filter by records that have comments or by records that have no comments.
Filter7
Row Source Type: Value List
Row Source: Completed;[tblMainData.Status]='Complete';Not Completed;[tblMainData.Status]<>'Complete'
Column Count: 2
Bound Column: 1
Filter8
Row Source Type: Value List
Row Source: Comments;[tblMainData.Comments] Is Not Null;No Comments;[tblMainData.Comments] Is Null
Column Count: 2
Bound Column: 1
The VBA Code is:
==================
If Me(Filter7) <> "" Then
strWhere = strWhere & " " & Me.Filter7.Column(1) & " " & " And "
Debug.Print strWhere
End If
If Me(Filter8) <> "" Then
strWhere = strWhere & Me.Filter8.Column(1) & " And "
Debug.Print strWhere
End If
==================
I'm getting an error on building the strWhere variable. Can I use filter criteria statements in a combobox column?
Thanks,
PC
Filter Criteria - Select One Customer Or All
Jun 10, 2006Hello,
I would like to have a query where I could select just one customer or, by hitting enter, select all.
For example, [Enter Customer ID or Hit Enter to display all customers]
SELECT Certain Rows Based On Criteria
May 8, 2007Hi all,
Very very new to Access, moved department a week ago and inherited a couple of databases that need tweaking. Although I'm learning I am stuck by something very simple and can't find it by searching this forum or Google :)
I have a query that originates from 2 tables, there is one custom record made from 2 of the fields -
Free Stock: [stk_stkqty]-[stk_allstk]
Now I want a record that shows -
Product where the free stock = backorder stock
so theroetically my guess as a beginner is -
SELECT * FROM [Product] WHERE [Free Stock] <> [Back Order]
But it fails miserably on syntax. Can someone point me in the right direction, and I apologise for my lack of understanding in advance.
Boofuls :)
Multiple Criteria In Select Query With 1 Like
Apr 22, 2008Hi,
I'm trying to build a select query where it prompts the user for a few parameters. I've been having issues where people would misspell the vendor name and nothing pops up. I changed the vendor to Like [Vendor Name] & "*" but now I can't use the parameter of PO# without everything popping up. I have attached the SQL view. Thank you for any suggestions.
SELECT [Main Payment].[Batch#], [Main Payment].VendorName, [Main Payment].VoucherPrefix, [FY08 PAYMENT detail].VoucherNumber, [Main Payment].VoucherSuffix, [FY08 PAYMENT detail].Vchline1, [FY08 PAYMENT detail].PONo, [FY08 PAYMENT detail].InvoiceDate, [FY08 PAYMENT detail].InvoiceID, [FY08 PAYMENT detail].Amount
FROM [Main Payment] INNER JOIN [FY08 PAYMENT detail] ON [Main Payment].VoucherNumber = [FY08 PAYMENT detail].VoucherNumber
WHERE ((([FY08 PAYMENT detail].PONo)=[Enter PO#])) OR ((([Main Payment].[Batch#])=[Enter Batch #])) OR ((([FY08 PAYMENT detail].VoucherNumber)=[Enter Voucher #])) OR ((([Main Payment].VendorName) Like [Enter Vendor Name] & "*"));
Queries :: Or Criteria In Select Query
Nov 15, 2013How can set criteria in select query as Textbox1 or textbox 2 ...textbox 1 and textbox 2 are field on different forms.
View 4 Replies View RelatedSelect Statement Searching On Date Criteria
Feb 23, 2006Hello everyone! :)
I've been banging my head of a brick wall with this one. Its probably straight forward... but I can't see the solution! A problem shared is a problem halved... or so they say!
Ok I have this table called CustomerComments. In it are the following columns
CustID
Comment
CommentDate
For each custID I want the most recent commentdate and its matching comment from that row... (there can be multilple comments recorded per custID)
Problem is I can't seem to get this to work.. instead its returning all rows. I have run a query that will sort and group this list. The top line of the grouping gives the most recent comment and commentdate per customer.
Does anyone know how I could just get that top line per custID rather than all rows returned?
Thanks for reading this... hope it makes sense!
:)
Query Criteria With Option To Select All Records
Aug 22, 2007I am developing an Event Management DB for business training courses. I have an attendees table with a link to CompanyNames. I wish to be able to produce an attendees report based on the CompanyName ie all attendees from the selected company. I have done this by using a dropdown of companynames on a Criteria selection form. This works well.
What I would also like to do is print the same list without the companyName criteria. I am aware I could make another query but was hoping that maybe there is a way of setting up the criteria in the companyName field so that it could accept either a company name or ALL company names.
Hope someone can help.
Thanks
Peter
Select Multiple Criteria For Query On A Form
Oct 14, 2005I was searching through here looking for a solution to the following problem:
I am building a Capacity database for work. The requirements require that users be able to sort by numerous criteria (Forecast Date, Portfolio, Market, Bucket, Month), all from a simple form that will spit out the results. Rather than creating unique queries for each combination of criteria(way too many!) or creating the SQL text in VBA, I played around until I came up with the following(which may have been done already, but I can't find it on here, so I'm not claiming to be brilliant...LOL). I have dropdowns on my form for each sort criteria-(Forecast Date, Portfolio, Market, Bucket, Month)
Then I created a query and for each criteria, I put this in:
Like (IIf(IsNull([Forms]![frmWAOFAdjustments]![Bucket]),"*",[Forms]![frmWAOFAdjustments]![Bucket]))
So if the user leaves a dropdown blank, the query simply brings back all the results from that field (Like *).
The text in bold is simply replaced with the dropdown name for each segmentation criteria. Next to each dropdown is a button that clears the dropdown box and requeries the listbox with the results(in case the user doesn't want to remove a criteria. This makes running searches for a specific number of accounts easy for the user and easy on me..lol.
Hopefully this can help someone out with a similar problem. I have learned so much from this forum, I just wanted to give something back. If anyone has any questions on this, just let me know.
Select Query Using A Lookup Table As Criteria
Aug 29, 2007Using MS Access 2002
Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.
Table name is "dbo_VG_PropertyValues"
AppID - Number
DNIS - Text (This is the column i need filtered)
PropertyName - Text
ProperetyValue - Text
I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..
I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.
Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:
IN ([DNISList]![TFN])
Forms :: How To Select Multiple Criteria In A List Box
Feb 4, 2015I am trying to create a form that allows the user to select a detective and multiple case statuses (such active, inactive, dna, filing, etc.). The form is intended to allow the user to determine the number of assigned cases. The form is tied to a query and the criteria that I've set up in the query is:
[Forms]![DET_CaseManagementFRM]![Combo30] which is under the investigator field.
The case status field has similar criteria [Forms]![DET_CaseManagementFRM]![List43].
My problem is that I cant figure out how to allow the user to select multiple criteria in case status field in my form. The research I've done online says you need to specify simple or extended in the Multi Select field under the other tab in the property sheet. However, when I do specify simple it doesnt return any records which I know it not true. Is there VB code that I need for this?
Select Those In A Column That Meets Character Count Criteria
Nov 4, 2005Hey, I know this is probably a simple thing to do, but I couldn't find it anywhere on the web so hopefully someone here can help.
I have a column of UPC codes which are a bunch of #'s. I need to find the ones that have more than or less than 12 characters.
Thanks
Query For Populating A Form Based On Different Select Criteria
Feb 20, 2006Hi all,
I have a query which populates a form called EditPatientFrm, which asks the user to enter the patient UniqueID in order to pull up the record they want to edit from the TblPatient. However, I want the user to now be able to input just one of three criteria in order to pull up the record to be edited:
UniqueID
ChartNumber
PatientLastName and PatientFirstName (two fields)
since they may not have the UniqueID readily available to them.
Can anyone show me how to do this? I have tried to find answer to this one under queries and forms- no avail. Sure it's a simple thing...
thanks!
vrpres
Forms :: How To Select A Record Based On Multiple Criteria
May 15, 2013I have a list of client stored in a table "Clients". I would like a form to present a user with the next client in the list at the click of a button, but there will be some exceptions:
Some clients will be given priority, and should be moved to the top of the list although they may not be the next logical entry in the table. Priority clients are indicated by a field "Priority" with a Yes/No option set before work begins.
Some clients will have requested a call at a certain time of the day - This time will be stored in a field named timeToCall. The next user to click the button after the time has passed should be given that clients details.
When the end of the list is reached any clients who could not be contacted will be tried again. My table currently has a field "Attempts" to track this, but that may not be needed. Clients who have been dealt with will be removed from the "Clients" table to a "Completed" table.
Whichever record is selected will be flagged as being dealt with so that 2 people don't get the same entry. I may add a new field for this, but right now I plan to use the "attempts" field to control this
I am unsure of the best approach to the above, I'm not very well-versed in Access, but if I were doing this in Excel (as originally requested by my boss) I would do it like this:
Use a form where the "Next" button will use VBA code to first check if there are any priority clients, then check if there are any timed client's ready to call and then move to the next entry that has had 0 contact attempts. Whichever record is the next one will be displayed in the form to the user.
Filter Report By Multi-select Combo Box Criteria
Nov 15, 2011I have a form that contains three multi-select combo boxes, called Market, Readiness, and Role. I am trying to use the user-selected criteria from these boxes as parameters for my query, which is linked to a personnel report.
I have been attempting (without success) to modify code to gather all the selections in each box and then open the updated report. The code below uses the combo box called "Market" and a report called "Succession".
Private Sub Run_Report_Click()
Dim StrWhere As String
Dim varItem As Variant
If Me.Market.ItemsSelected.Count > 0 Then
[Code] .....
Questions:
1. This code is missing something, since when I click my event button to run it, a box pops up to ask for the parameter I already gave in the combo box.
2. What is the syntax to add in the other combo box selections?
Queries :: Query To Select The Date From Two Tables On The Criteria Basis?
May 21, 2013get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
Queries :: Combo Box On Form That Passes Criteria To Simple Select Query
Sep 22, 2014I have a combo box on my form that passes criteria to a simple select query. There are four possible selections to make from the combo box. For some reason, when I select the first option on the list the query runs perfectly. However, if I select the second, third or fourth option from the combo box, the query returns no records, even though I know there are records in my table which should be returned.
View 5 Replies View RelatedModules & VBA :: Criteria On MS Access Query Not Working With Multi-Select Listbox
Aug 22, 2014I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
Queries :: Allowing Users To Select Multiple Table Fields As Query Criteria
Apr 22, 2013I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
View 3 Replies View Related