Query Criteria With Option To Select All Records

Aug 22, 2007

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.

Hope someone can help.

Thanks

Peter

View Replies

Getting Query Criteria To Select All Records Or Specific Records In Query Design Section?

Jun 16, 2014

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.

View 2 Replies View Related

Select Option Group Option Based On Checkbox Value

Aug 22, 2005

Hi

I've got an Option Group with 3 options; Yes, No and Future.

I also have a range of Check Boxes; 1 for every day of the week.

When one of the check boxes is selected I want the Option Group to change to Yes.

Currently, the Option Group defaults to Future and No will only be available by manually selecting it. This doesn't need to be changed.

Any help will be greatly appreciated!

Cheers

View 2 Replies View Related

Forms :: Criteria To Query From Form - Option Group?

Feb 2, 2014

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.

Can this be achieved with option group?

View 2 Replies View Related

Queries :: Linking Option Buttons To Query Criteria?

Apr 15, 2014

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.

View 1 Replies View Related

Forms :: Passing Criteria To Query From Option Group Control

Feb 2, 2014

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]")

View 3 Replies View Related

Criteria For Select Query

Aug 27, 2007

Hello,

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!

View 3 Replies View Related

Setting Criteria Within A Select Query

Aug 23, 2005

I 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

View 1 Replies View Related

Multiple Criteria In Select Query With 1 Like

Apr 22, 2008

Hi,

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] & "*"));

View 3 Replies View Related

Queries :: Or Criteria In Select Query

Nov 15, 2013

How 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 Related

Select Multiple Criteria For Query On A Form

Oct 14, 2005

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.

View 2 Replies View Related

Select Query Using A Lookup Table As Criteria

Aug 29, 2007

Using 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])

View 3 Replies View Related

Query For Populating A Form Based On Different Select Criteria

Feb 20, 2006

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...

thanks!
vrpres

View 1 Replies View Related

Queries :: Query To Select The Date From Two Tables On The Criteria Basis?

May 21, 2013

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.....

View 1 Replies View Related

Insert Records Into Table Based On The Select Criteria From The First Table.

Mar 22, 2005

Hi

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�

View 4 Replies View Related

Option Select

Aug 21, 2004

Hi there!

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.

Can someone please help with some code!

Kind regards
Phindile

View 1 Replies View Related

Queries :: Combo Box On Form That Passes Criteria To Simple Select Query

Sep 22, 2014

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.

View 5 Replies View Related

Modules & VBA :: Criteria On MS Access Query Not Working With Multi-Select Listbox

Aug 22, 2014

I 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] ....

View 1 Replies View Related

Queries :: Allowing Users To Select Multiple Table Fields As Query Criteria

Apr 22, 2013

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?

View 3 Replies View Related

Is There A Way To Use Keyboard To Select From An Option Group?

Apr 17, 2005

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.

View 7 Replies View Related

Having An Option Button Select A Different Subform To Come Up In The Same Location

Aug 17, 2004

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

Thanks for your time everyone

View 3 Replies View Related

Returning Select Records In Query

Mar 30, 2006

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.

Thanks, Tim

View 3 Replies View Related

Query To Select The Duplicated Records.

Jun 23, 2006

Hello,

I want to make a query that retrieves me all the duplicated records from a table. It�s possible?

Thankx

View 1 Replies View Related

Select Query Showing Too Many Records

May 27, 2007

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.

View 6 Replies View Related

Select Only Records For Specific ID Query

Jul 31, 2007

This is killing me,

I have a form with a unique ID, I have a query that pulls all data regardless of the ID on the form.

How can I get the Query to pull only the data for the ID that is currently in view on the form?

Please help..

Thanks,

Fen How

View 2 Replies View Related

Query To Select 20 Random Records

Aug 9, 2007

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?

Thanks,
Jim

View 1 Replies View Related