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.
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 query that i need to run from a form. From form I need to pass criteria
1 for canada 2 for us 1 and 2 for us and Canada
The criteria needs to go into field Pricing Type. If user selects 1 then only Pricing Type 1 will display. If user selects 2 than only Pricing Type 2 should display. I user selects both , Pricing Type 1 and 2 should be listed.
I have a query that has 2 fields of currency labour rates (current rate and baseline rate), but sometimes there are some blanks in either of the fields and is causing my text field which has a Sum formula (Rates x Hours) to #ERROR and throws out my other calcs. The only way to find out which blank line items are throwing my query numbers out is to manually type in "Is Null" in the criteria.
However, I would like to keep this process fully automated so I tried to create 2 lots of option buttons for my form, one for each of the 2 fields of currency labour rates and after I click on either option button and then run my query it will show all the lines that have blanks. I was able to link 2 text boxes to the option buttons (Select Case - Case 1 - Me.txtBox1.Value = "Is Null") and then link the text boxes to the criteria field using [Forms]![frm_labour]![txtBox1]. However, none of this seems to be driving my criteria in my query.
I need to pass a criteria to a query from my option group control to my query.
It contains three options 1,2 and 3.
If option 3 then Pricing Type 1 and 2
How do I make the code below working?
IIf([Forms]![FrmUserSelection]![PricingType] Like 3,([dbo_AGPricingDiscounts].[PricingType])="2" Or ([dbo_AGPricingDiscounts].[PricingType])="1",",[Forms]![FrmUserSelection]![PricingType]")
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 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 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:
Hi 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...
get 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.....
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
i have a list of provinces in an option select right, so when i click one province i want it to chance the header of my page according to the provinces, and i have no idea on how to do that.
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 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?
I have a user who can't operate a mouse and I want to make it possible for him to select from an option group using only the keyboard.This is possible if a default button is stipulated, but can it be done when there is no default?
There is no event associated with the keyboard in an option group, only with the controls within it. I tried using the Got Focus event on an option Button, but entering the control doesn't cause the options to receive the focus, seemingly.
Any ideas, (they don't have to be fully formed) would be gratefully accepted.
Hi, Asked this one yesterday but I think I must be doing somthing wrong. I have a main form named "Techdata" on this form I have an option group with 2 option buttons. I would like when selecting the first option button for the subform "effectivity" to show up on the main form. When the second button is selected I would like the subform "effectivity single" to show up in the same place where "effectivity" was(would like effectivity to be invisable then). Can anyone walk me through this from whoa to go as Im havn a lil bitta trouble
Hello, I am sure this is quite simple, however I cannot figure it out. I have a query that shows tblPartNumber and tblAverage. The average is calculated in the query by taking the total minutes to assemble the parts, divided by the number of parts completed. What I am trying to do is run a query that will show me the most efficient (lowest average), time for each Partnumber. Ideally the query would show me something like this;
Part # Average 1234 .72 1234 .86 1234 .94 etc...
I searched on here and it seemed like the Top 10 is used, but in my application that ommits all of the other partnumbers because it only shows ten of the records for the first partnumber. I only want to see the five most efficient averages. Any help is greatly appreciated.
I have two tables, bad design, not my choice of the way it's set up.
Table a has Fields 1-2, dups allowed in all fields. No pk was originally assigned. Table b also has the same fields 1-6, but also has field 6.
Table a has all of the records in question, table b only has some of the records from table a but does not have any records that are not in a.
Something like this. Table a
Field1 Field 2 Field 3 Field 4 Field 5 John Shirt blue denim button Mark pants green straight John Shirt blue silk button
Table b
Field1 Field 2 Field 3 Field 4 Field 5 Field6 John Shirt blue denim button new Mark pants green straight used
If I query the two tables, a left join to b, then I get the correct number of total records. If I select any records from b, then the total records goes higher than there are, even for the two combined, listing the same record multiple time.
Any suggestions are appreciated. I am a blood bank lab person, not an IT person, or DBA, so simple responses are greatly appreciated.
As part of my job, each month I have to select 20 problem report resolutions and grade them on quality. Typically we have about 100 problem report resolutions per month. Is there a way I can use a query to return a random selection of 20?
