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.
I 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?
I 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.
I have a table in my database with customer records including account number and account name. On a form I would like to set up a combo where the account number is selected and for the corresponding account name to appear in an adjacent text box.
The customer account number control is currently a textbox for manual entry but I want to change that to a combo with the AccountNumber field set as the control source.
I would like to know how to get the customer name to appear in the CustomerName textbox when the account number is selected from the new combo.
Any help would be appreciated and I enclose a copy of the form on a word doc.
I'm looking to select name of a hospital from "HospName" from a list box. If no, then at least to write the name and the rest I'll get it associated with the name.
Then I need to know if I can put this in a form so the form asks same criteria?
I have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
I manage an Access application that in many instances uses data selected from a combo on a form for variable criteria. In this instance it is in the form of:
I recently wrote a routing that exports to Excel based on a record set derived from a query. In testing I hard-coded the criteria (School Year) in the query. Once everything worked I sustituted the variable above. Now, in the VBA, no records are put into the recordset, when I run the queries directly from Access they work correctly, drawing the results for the school year selected on the form referenced.
'Create The Recordset If Me.Frame11 = 1 Then strQueryName = "ExcelHS" GroupTitle = "High School" Else strQueryName = "ExcelMS" GroupTitle = "Middle School" End If Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why? WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
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.
I'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.
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 } }
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.
I'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?
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.
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] & "*"));
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?
I 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.
I 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.
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:
I 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?
