Query Multiple Parameters, Help Pls!

Jan 4, 2006

I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...

strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"

Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.

SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;

Your help will be greatly appericated.

View Replies


ADVERTISEMENT

Union Query - Multiple Parameters

Oct 31, 2005

I am trying to run the below union query, but it keeps asking for FRGHT_BL!FB_CREAT_DTM as a parameter. I want it to only ask for the Start Date and End Date once.

SELECT * FROM qLOC_ID WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION SELECT * FROM qLOC_ID2 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION Select * From qLOC_ID3 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date]));

View 1 Replies View Related

Multiple Parameters [for The Same Field] For A Single Query?

Nov 23, 2005

I have set up a parameter query in Access 2003 that asks the user for the "Section", such as "Admin", "Accounting" etc.

I need them to be able to respond to the prompt with more than one section if they want- sometimes just one, sometimes two or three or four.

So they can get "Accounting" and "Admin" both in the records that are returned.

The code:
SELECT T_ElainesMaster.Section, T_ElainesMaster.Login, T_ElainesMaster.Workstation, T_ElainesMaster.NT, T_ElainesMaster.Barcode, T_ElainesMaster.[PC model], T_ElainesMaster.[Emp Name], T_ElainesMaster.[swap or not], T_ElainesMaster.[Exp Date]
FROM T_ElainesMaster
WHERE (((T_ElainesMaster.Section) Like [What section would you like to include?]))
ORDER BY T_ElainesMaster.Section;

Thanks!

View 1 Replies View Related

Multiple User Specified Parameters For Search Query?

Mar 30, 2006

Hello

I have created a database for my department to log all of our jobs to keep track of them and I want to create a simple search for them but I am having difficulties.

I am familiar with creating queries to search tables for matching records, but is it possible to get search criteria from the user (ideally from a search form they fill in) to form the query?

For example, I would like the user to be able to query the jobs by month and/or business area and/or supplier...is this possible?:confused:

Any help gratefully received!!!
:)

View 2 Replies View Related

Using Multiple Criteria / Parameters For Same Record In Query

Jul 3, 2013

I have a query thats outputs my costs on a project divided into 50 categories. Additionally, each project can be divided into 3 stages, each with the same 50 cost categories. I have been using a query that prompts the user which stage they would like to look at, and it works great.

However, it would be useful to be able to see all stages at once, next to one another.

So, essentially, i want to be able to input the stage parameter in the criteria box for three fields in the query, so instead of showing the costs for one stage at a time, it will show all three(and a summation across all stages), without a prompt.

It works when i set the one field manually to the first stage, but when i add additional fields, it returns a blank query.

View 1 Replies View Related

Access Query To Accept Multiple Check Box Parameters

Nov 9, 2007

Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you

View 13 Replies View Related

Allow Multiple Values In Unbound Field For Query Parameters

Apr 30, 2014

I am trying to find a way to allow the user to enter multiple numbers in an unbound box that I will pass to a query as parameters. I already have it set up to pas the parameters from the form but if i try to do more than one number it doesn't work.

I have tried:

1306 or 1307 or 1308
1306, 1307, 1308
"1306 or 1307 or 1308"

I was reading somewhere that when the value is picked up from the form it is not like you are typing it right in the criteria box of the query.

So are there any other options here? Is there a way to enter all the number in a box and then use vba to create an array and then pass that to the query?

View 2 Replies View Related

Modules & VBA :: Multiple Selections In List Box - Query To Accept Parameters?

Mar 27, 2014

How to modify my query to accept parameters from a multiple choice list box. This is the SQL code behind my search query:

SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber,
AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto,
AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType, tblAirports.AirportName, InfoSource.SourceType, tblCountry.CountryName, tblAircraftCategory.AircraftCategory

[Code] ....

I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:

1. Piston
2. Turbo Prop
3. Entry Level Jet
4. Light Jet
5. Super Light Jet
6. Midsize Jet
7. Super Midsize Jet
8. Heavy Jet
9. Ultra Long Range
10. Helicopter
11. Air Ambulance
12. Cargo
13. Vip Airliner
14. Airliner

The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).

I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?

View 4 Replies View Related

Multiple Parameters Doesn't Work On Multiple Fields

Jun 27, 2006

I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?


SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount
FROM [JE 06 Log]
WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));

View 6 Replies View Related

Multiple Parameters (I'm SOL With SQL)

Jul 26, 2005

I am trying to strengthen an already developed database at my work. They had a form with five different text boxes, each one that would run a seperate query on the same table, and the results would open on a different page. I am trying to combine all these text search parameters from the form to a single query and have the result come up on the form. I have done every search I can thing of from a forum search, a google search, and just reading through query forum posts for about a day; but I still cannot tell what is wrong. If someone could look at my code, and see if I have a mistake, or if I'm even in the right direction. :confused:

SELECT FLIGHTS.[FINISH OD], FLIGHTS.[PIPE OD/SHAFT OD], FLIGHTS.PITCH, FLIGHTS.[TYPE OF MATERIAL], FLIGHTS.[BURNED OD], FLIGHTS.[BURNED ID], FLIGHTS.[RIGHT/LEFT HAND], FLIGHTS.PartNumber, FLIGHTS.COMMENTS, FLIGHTS.[START TIME], FLIGHTS.[FINISH TIME], FLIGHTS.PurchaseOrderNumber, FLIGHTS.QTY
FROM FLIGHTS
WHERE ((FLIGHTS.[FINISH OD]=Forms![KNOWN BURN SIZES]!Text25 & "*") And (FLIGHTS.PITCH=Forms![KNOWN BURN SIZES]!text37 & "*") And (FLIGHTS.[TYPE OF MATERIAL]=Forms![KNOWN BURN SIZES]!Text32 & "*") And (FLIGHTS.PartNumber=Forms![KNOWN BURN SIZES]!Text0 & "*") And (FLIGHTS.PurchaseOrderNumber=Forms![KNOWN BURN SIZES]!text36 & "*"));

The FLIGHTS is the main table, [KNOWN BURN TABLES] is the form that the parameters are entered. I want users to be able to enter any fields they want and leave others blank. Currently all I get is a blank query. Sorry if the answer seems obvious; I tried avoiding posting till I knew that I couldn't figure it out on my own.

Thanks.
-Brian

View 8 Replies View Related

Multiple Parameters

Mar 30, 2005

I often use forms to select a parameter & date range for a subsequent report. Where I have chosen to use multiple combo boxes (two in this case)to select more than one parameter I have run into a problem. The report opens ok the first time but if I close it (report) and change my selection then the report refuses to open. Should I just requery the two combo boxes, is there something else or can't it be fixed?

View 1 Replies View Related

Multiple Choice Parameters???

Apr 22, 2008

I'm trying to setup a parameter that has a multiple choice so to speak. Rather then the user being able to enter whatever data they want.. I would like them to have a choice between two items.

Is it possible? if so.. how do I do it?

Thanks!

View 14 Replies View Related

Filtering Report (with Subreports) Using Multiple Parameters

Sep 17, 2006

hey there

i have a main PatientForm that contains patient information from multiple tables:

[GENERAL] values
Subform - [RefMD] values (referring doctor)
Subform - [Drugs] values (prescribed meds)
Subform - [Diagnosis] values (medical diagnosis)
Subform - [Encounters] values (visits to the doctor)

what i have is a form (image attached) and i need to be able to select any combination of parameters (including state, zipcode from GENERAL, i.e. referring doctor last name from RefMD, Drugname from Drugs, Diagnosisname from Diagnosis, and VisitType from Encounters) and filter PatientForm where all the selected parameters are true..

does that make sense?

all tables are linked using the field HistNum

how do i code this? i am pretty lost right now

View 2 Replies View Related

Single Set Of Parameters For Multiple Queries In A Report?

Oct 21, 2004

I'm pretty new to Access, so if I'm doing this the hard way, that's why.

I work in a quality control position, and I'm trying to set up a single Access database for the QC staff to use instead of everyone having their own seperate Excel workbooks.

The issue I'm running into at the moment is on a report. Each record is graded on four seperate types of criteria, Error Type 1, Error Type 2, Error Type 3 and Error Type 4, all of which need to be reported on seperately. So I have at least four queries set up, all with the same parameters (right now, just review date.) I'm trying to pull through all four queries on the same report, and so far I've had success having the report ask for the parameters only once and then applying it to all four queries. However, I'm running into a problem where Access is now forcing the filters of each individual query on to each of the other three queries, so it's only pulling through records for all four queries that match the criteria of all four. Any record which only matches the criteria of one, two or three of the queries is being left off the report.

Sorry if this sounds confusing. Anyone have an idea as to what I'm doing wrong?

View 3 Replies View Related

Reports :: Parameters Being Requested Multiple Times

Nov 17, 2014

I set up what I thought was a simple query in my DB using it to create a report containing a subreport.

I kind of works, however Access is asking me for the parameter value [Extract Date] multiple times, when in theory it should only ask once!

In addition (and likely related) the Access is then repeating the output multiple times.

View 8 Replies View Related

Modules & VBA :: Make Multiple Tables Using Parameters From List

Jun 11, 2013

I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.

View 13 Replies View Related

Modules & VBA :: Quotation Marks With DCount And Multiple Parameters?

Apr 14, 2014

I created a query in Query Builder which contains a DCount with multiple parameters and it runs as it should. I am trying to convert it to VBA, but my inability to put in the quotations marks correctly is frustrating me terribly.

Here is the SQL version from Query Builder:

UPDATE [Daily Status Update Table] SET [Daily Status Update Table].NumberOfChases = DCount("[ChaseOtherID5]","[Chases_View_ALL - TX_Mbr 9 Digit]","[ChaseOtherID5] = 'U - Initial Contact' AND [ChaseStatus] = 'A'"), [Daily Status Update Table].ChaseStatus = "A", [Daily Status Update Table].NewStatus = "A", [Daily Status Update Table].ChaseAssignment2 = "Unscheduled"
WHERE ((([Daily Status Update Table].ChaseOtherID5)="U - Initial Contact"));

View 3 Replies View Related

Obtaining Multiple Report Parameters Via Menu Form Lookup Control

Feb 11, 2015

In our Student Administration database, we have a Student Evaluation Report which prints a 1 page per student report in memo like format. The instructor had previously input rating numbers (4=Best, 1=Worst) for 9 categories for each student via a database form into the Evaluations Table.

Two other elements of the rating are an Attendance based on number of days absent and a Grade Point Average (GPA) calculated from the student's test scores. The instructor then prints and reviews the report with each student.

There is an Evaluations Parameter table which has the following fields:

Field
Description

ID
The autonum key field.

Class
eg., 2015-1

Evaluation Number
A single digit (eg., 1, 2). There may be more than 1 evaluation for each class.

EffectiveDate
The "as of" date of the evaluation.

There are then several other tables that are input to a query that will be the record source for the report:

Table
Purpose/Data

Students
Student Name, Class

Absence
Has a record for each student's absence with date and a 1 or .5 indicating a whole or half day absent.

Test Grades
Has a record with each student's test results with date and score.

Evaluations
Holds the rating score for each of the 9 rating categories.

The Student Evaluation report is launched from an unbound Reports menu form via a button. On the Reports menu form I wish to have controls for the user to select which evaluation to report on. The Absence and Test Grade information needs to include records that are <= the EffectiveDate in the Evaluation Parameter table. The Evaluations records need to match the Class and Evaluation Number in the Evaluations Parameter table.

I would like the user to be able to select the Evaluation Parameter via a combo box vs. specifying the Class, Evaluation Number and Effective Date separately and then have the query record source for the report use those two fields as criteria. But my limited understanding of the Combo box indicates that only 1 field from the lookup query of the Evaluations Parameter table can be stored in the control whereas I need 3 (Class Evaluation Number and EffectiveDate).

How the user can select the desired Evaluation Parameter record on the Reports menu form and use the 3 fields from the selected record as criteria in the report's record source query.

View 10 Replies View Related

Reports :: Pass Listbox Parameters To Pull Multiple Separate Reports

Nov 23, 2013

I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.

I am having issues figuring out how to get it to pass them to a query or filter the reports.

I have tried many different examples and nothing seems to work.

View 4 Replies View Related

Queries :: Report Or Macro To Run Multiple Queries Using Same Parameters?

Aug 9, 2013

So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.

Query 1: Total Distribution
Query 2: Total Distribution
etc....

Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?

View 1 Replies View Related

Query With Parameters

Aug 30, 2004

I have a parameter query with 5 possible user entries.
The criteria for all my fields is set up as follows:

Criteria: IIf(IsNull([Enter County]), "", [Enter County])
Or: Like "*" & [Enter County] & "*"


If the user enters something for 2 or more parameters it seems to work, but if they just enter a county, for example,, it gives an error message about the expression being too complex.

Any suggestions.

Thanks.

View 9 Replies View Related

Passing Parameters To A Query

Feb 1, 2006

Guys I need your help/Advice...

In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.

also i have qry2 based on qry1
then qry3 based on qry2
and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.

now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...


strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"

Set MyDB = CurrentDb

Set rst = MyDB.OpenRecordset(strsql)



but when the last line is executed I get this error message:

Runtime Error '3061':
Too few parameters, expected 2.

i also tried doing this:

strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"

Set MyDB = CurrentDb


MyDB.QueryDefs("qry4").sql = strsql

Set rst = MyDB.OpenRecordset("qry4")


but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)

again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!

Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!

I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers

View 1 Replies View Related

How To Set Query Parameters For Use In OpenRecordset

Sep 13, 2006

I’ve the following query definition “selOrders”

PARAMETERS [DateFrom] DateTime, [DateTo] DateTime;
SELECT * FROM Orders WHERE OrderDate BETWEEN [DateFrom] AND [DateTo]

I want to open this query as a DAO.Recordset but have problems to assign values to the parameters. I tried different possibilities but invain. My latest try was as follows:

Dim QryDef As QueryDef
Dim Date1, Date2 As Date
Dim Orders As DAO.Recordset

Set QryDef = CurrentDb.QueryDefs("selOrders")
QryDef.Parameters("DateFrom") = Date1
QryDef.Parameters("DateTo") = Date2
Set Orders = CurrentDb.OpenRecordset("selOrders")

During execution error 3061 (Too few parameters, expected: 2) occurs.

View 1 Replies View Related

Parameters In A CrossTab Query

Apr 10, 2007

Hey,

I am trying to enter a user-defined parameter in a CrossTab query with little luck. It works fine if I enter the code already defined as in: >= #12/06/2006# but when I enter the code for a user-defined / input such as: >= [Enter date:], or if I try to redirect to a textbox on the active Form such as: >=[Me]![dfrom.Value] I get the error "Invalid field or Expression), although this syntax / code works fine if I do this in a normal query.

Any suggestions?

Kind regards, Adam.

View 2 Replies View Related

Form To Set Query Parameters

Sep 11, 2006

I have created a form with multi-select list boxes, behind which is the following code to transform the users' selections into query parameters.


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Test")

If Me!lstAB.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstAB.ItemsSelected
strCriteria = strCriteria & "Centres.[Area Board] = " & Chr(34) _
& Me!lstAB.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Centres.[Area Board] Like '*'"
End If
strSQL = "SELECT * FROM Centres " & _
"Where " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "Test"

Set db = Nothing
Set qdf = Nothing

End Sub



I would like to allow the user to set, on the same form, which field they want the resultant data to be sorted by, preferably by having a tick box alongside each list box, which sets that field as the sort field. The form will obviously need to allow only one of these tick boxes to be selected.

Can anyone advise me on how to modify the above code to make this possible?

Thanks,

Gary

View 1 Replies View Related

N00b...query Parameters

Oct 1, 2006

Hey guys, I have a simple design question I hope someone can help me with. I have little to no experience with Access, but I know enough to get around. Anyways, I'm working as an extern for a rock band. One of my assignments is to create a publicity contact database. I have all my forms and tables set up but I'm having some trouble creating my query. There are about 10 fields in the contact table. I want the user to be able to search any one of those 10 fields OR any combination of them. Is there a way to do this with only one query, where it willl prompt the user for only the fields they have selected to search?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved