Access :: Passing Parameters To A Query

Passing Parameters From Excel To An Access Query

Jun 4, 2007

Hey folks,

Not sure if this is the best forum but decided to put it here anyway.

I have a Access query that reads:

SELECT bleh
FROM blah
WHERE something > 10

Then in Excel I pull the data across using:

Set qdf = db.QueryDefs(qryName)
Set rs = qdf.OpenRecordset

and paste the data using

ws1.Range("IV1").End(xlToLeft).Offset(0, 2).CopyFromRecordset rs

I now want to change the query so the where statement reads

WHERE something > [amt]

So the question is how do I pass the value for the parameter value for [amt] from excel to access?

View 1 Replies View Related

Passing Parameters To A Query From A Form To Filter A Report

Aug 15, 2006

I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.

Here is the query created as a stored procedure:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];

What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?

Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?

Thanks.

View 14 Replies View Related

Reports :: Navigation Form - Passing Query Parameters To A Report

Aug 4, 2014

I have a navigation form that will have 6-8 tabs. We were using about that many databases, but we are finally consolidating them into one. The result of us using so many databases has been the multitude of forms and reports that were necessary for each database prior to merging them together.

The problem: There will be anywhere from 12-20 (text boxes) that the user can use to search anything in our database. What we need to have happen, if possible, is for those search parameters to show up in the header of our report if they have text in them. If the text box is blank, it should not show up in the header of the report.

I have read how to to do the start/end date technique, but I do not know if that would work for what we are doing since the boxes would only show up if they are populated by the user.

View 4 Replies View Related

Modules & VBA :: Query To Search For Records - Passing Parameters In A QueryDef

Aug 27, 2013

I have a query that searches for records that are between two dates using the WHERE clause. The two dates are referenced to two respective text boxes on a form. On the same form I have a button that will launch the query in VBA using querydefs. I get the error 3061 saying I need to input the parameters. Therefore I am a bit unsure how to set the parameters in VBA. So far I have done this:

Code:

Dim Db As DAO.Database
Set Db = CurrentDb
Dim QDef As DAO.QueryDef
Dim rst As DAO.Recordset
Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")

[Code] ....

Where Date_VL is the field to be filtered. I know this is wrong but all examples I have seen have equated the parameter to a fixed value i.e 30/12/2012 for example, but I want this to be at the users discression. The only way I know of to get around this at the moment would be to write a temp query in VBA with PARAMETERS in the SQL code instead using the method above/

After this I'm going to assign the recordset to a matrix but that's a different story!

View 10 Replies View Related

SQL Server - Passing Parameters

Jun 15, 2005

Hi,

After years and years of working with Jet, I am now trying to use SQL Server backands and am having problems passing parameters to a stored procedure.

How can I build a query which where I can say "WHERE ([AField]=""" & Forms!BlarBlaretc.value & """)" as I can in access because as I understand it you can not look at front end information from the SQL backend.

Any help (and start at the beginning) would be much appreciated.

Cheers

Stu

View 1 Replies View Related

Passing Parameters To A Form

Dec 22, 2006

Hi all,

I have a continuous form that is bound to a SQL Server view.

For each record in my form I have a button, which when pressed opens up a second form. The second form is bound to a stored procedure that takes a parameter. The parameter value that I want to pass to this second form is the value of one of the fields in the first form.

I did the following in the click event of the button on my first form:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SecondForm"

stLinkCriteria = "[Field1]=" & "'" & Me![Field1] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

However, when I run this it keeps prompting me to specify the value of Field1 so this value is obviously not getting through. Do you have any idea why this might be happening?

Thanks in advance
Kabir

View 4 Replies View Related

Passing Several Parameters To Subform

Dec 22, 2006

Hi,

I would like to transmit several parameters from my main form to the subform when I open it.

Parameters could be used in the subform by onload event, for eample, to define form caption, hide or show buttons, enable or disable edit boxes, give them a color, ... It could also allow to use the same form in different application contexts, which would reduce so the development time.

More than one technique might be possible.
I used the following one :
- I put the parameter value I want to pass into myParameter
- when click on the button to open the subform :
DoCmd.OpenForm myDocument, acNormal, , , , , myParameter
- when loading the subform, retreive the parameter value in Me.OpenArgs and using it (eg disable a button)

It works fine. But I can pass only a single value with this technique and it's not enough. I tried to give an array as myParameter and fill it with my parameters values, but Access refuses an array to be in the DoCmd statement for OpenArgs option.

Any idea about passing several parameters to the subform ?
Or may be an alternative to the DoCmd technique ?

Thanks.

View 5 Replies View Related

Passing Parameters From Form To Queries

Mar 23, 2006

Good Afternoon,

I am trying to create a form where a user will enter in a value into a text field. Afterwards, when the user clicks "Enter", a query will run and will LOOK FOR THE VALUE THAT WAS ENTERED INTO THE TEXT FIELD. i.e.
User enters their address into the field and clicks the enter button.
Afterwards, a query will run like
select * from customers where address = @address <== the value the user entered into the text field. This is where the mystery lies. How do you pass values?????

Thanks,
Nervous Jervous

View 6 Replies View Related

Modules & VBA :: Passing Parameters To Report Doesn't Work

Feb 23, 2015

I have a query that sums up the number of parts used. This works fine.

I want to be able to limit this query to parts used after a specific date.

I have in my report

DoCmd.OpenReport "Part Totals Report", acViewPreview, , "[Part Date])>= " & SQLDate

The report is bound to a query that has 2 group by fields, 1 count field a a further field, a date field ([Part Date], that I put a default criteria on. This field is not displayed. If I don't put a criteria on this field disappears when I close and open again.

I pass a date to the program via a form and this ultimately ends up in SQLDate. When I run this I get promted to enter [Part Date] even though I'm setting it equal to SQLDate above. I can out garbage to a proper date in here either way the report picks up the default date entered in by the query.

1. Get rid of all of the parameters off the query.

2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.

1.Not sure what this means but when I get rid of the criteria for the parameter the field disappears (I'm setting the show field to no as I don't want totals group by date). Getting rid of the field gives me all parts used.

2.I think I'm doing this in the above but will bow to superior knowledge!!

or is it I can't pass a parameter to a report run by a query that is grouping fields together to produce a count.

Incidentally once the report has been run (albeit with the wrong parameters) and I go into design mode and look at the property sheet for the report the correct filter is there (i.e., the date that has been input) but it quite clearly ignores this.

View 1 Replies View Related

Passing Parameters Thru Combo Selection: ERROR:Data Type Mismatch Criteria Expression

Oct 17, 2006

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:

Attached is my db. Please help me out here.

View 2 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 Criteria To A Query

Dec 6, 2005

Hi

I have recently started using queries to base my forms on. Even for basic forms i am using the query as the record source.

If for example i have a standard query with no criteria e.g. a employee form. How would i then use that query to create a report that filters for example a list of employees for a certain company?

Would i have to create a seperate query to base the report on? because the form when opened will always filter that criteria?

If this is the case, is there a way to keep a query set up with no criteria but when the object being used is opened some code will pass to the query the criteria e.g. a combo box on a form list all the companies and when one is selected it passes this to the query e.g. without the criteria already being in the query?

Hope that makes sense.

thanks

scott

View 1 Replies View Related

Passing A Criteria As A Value From One Query To Another

Apr 11, 2006

I have a combo box in a form which allows users to select a Client Group. One of the choices in the combo box is ALL.

I have some code in a STEP1 query that says
SELECT IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]) AS Expr1
FROM MPI_PERSON
GROUP BY IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]);

I have a STEP2 query to select all records on the MPI_PERSON table and do some slight processing.

Now, in a STEP3 query, I want to select all records from the STEP2 query where the field [Client Group] satisfies the STEP1 criteria. If the Client Group chosen in the Combo box is one of the existing categories i.e. SEDP or MD or LH, it works but where the ALL has been entered, it is returning no records in the final query.

I have tried pasting the results of the STEP1 query into the criteria box as a test - and in that case it does return all records I want. What do I need to do to get the criteria recognised in the QBE box - do I need to use Eval or something?

Here is the SQL for the final query
SELECT [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) AS Expr2
FROM [Find all Clients STEP 2], [Find all Clients STEP 1]
GROUP BY [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1])
HAVING ((([Find all Clients STEP 2].[Client Group])=[Find all Clients STEP 1].[Expr1]));

Hope there isn't too much "Social Services speak" in there - I can also strip down non-crucial fields if it makes it easier to follow what I'm on about.

View 4 Replies View Related

Insert Query (passing Nothing)

May 9, 2006

I have an insert query that works fine when all fields are entered:
INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '2Main');

BUT...if I have Address2 as blank b/c its an optional field I get a validation rule error...even though there is no validation set up on this field in the table.

INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '');

Please let me know if you know why this would be.

Thanks,
Kbreiss

View 2 Replies View Related

Passing Parameter To Query

Apr 11, 2008

Hi all,

Using Access 2003. Really need some help. Have the below code which executes a SELECT query and passes the results as an excel spreadsheet attached to an email. Problem is that the query store_report has some parameters that need to be passed to it. Is there a way that with my current code I can pass those parameters. If not is there some other commands or code I can use to accomplish this?

DoCmd.SendObject acSendQuery, "store_report", acFormatXLS, _ "john@john.com", "c.w.k@gmail.com", , _ "Store Reports", "Here are your reports.", True

Thaniks in advance.

William K

View 1 Replies View Related

Passing Date To A Query

Jul 31, 2005

My form has a date field (with a default of today-105 days)( and can be updated by the user) which is part of the following query

SELECT dbo_CM_AS_LG.AS_LG_DT, dbo_CM_AS_LG.AS_LG_WO, dbo_CM_EQ_MA.EQ_MA_ID, dbo_CM_EQ_MA.EQ_MA_DE, dbo_CM_EQ_MA.EQ_MA_PS
FROM dbo_CM_AS_LG LEFT JOIN dbo_CM_EQ_MA ON dbo_CM_AS_LG.AS_LG_TP = dbo_CM_EQ_MA.EQ_MA_NO_P
WHERE (((dbo_CM_AS_LG.AS_LG_DT)>[Forms]![F_Q_RISC_SWAPS]![SearchDate]) AND ((dbo_CM_EQ_MA.EQ_MA_DE) Like "RISC/6000*" Or (dbo_CM_EQ_MA.EQ_MA_DE) Like "ibm power pc*"))
ORDER BY dbo_CM_AS_LG.AS_LG_DT;

The query is thr root query for the form, but when the form opens the default date has not been set and I get an error.
Is there a good way to pass a parameter into a query ?
I have tried running the qury using the ON Open Property too, which works until I try and call this form from another one.

I'm sure there's a simple answer, I just can't see the wood for the trees (yet)

Cheers

View 1 Replies View Related

Help Passing A Parameter To A Query

Oct 26, 2007

I have a form with a combo box that when I select a value I want the hidden value (UserID) passed to a query that would open up a form that is set up like a data sheet. When I try to set up a form that would display the records related to the UserID I end up getting all records and the UserID column replaced with the passed value when I set the control source to = Forms!frmEmployeeInstances!cboShiftEmployees.Colum n(0)
I don�t know if I can pass the value to a query (if the query can even acknowledge the passed value), so any help would be greatly appreciated.
I have the OnClick set to open a form called frmShowInstances via a Macro but I could never get the Where Condition to work either.

View 6 Replies View Related

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

Queries :: Query Asking For Parameters?

Dec 1, 2013

I have a query that pulls information from two tables. Some of the fields that are being queried share the same name in the tables, [Reimbursed_Amount] and [Cancel_Fee] specifically. In Design View I have specified that I only want the query to pull these fields from the Event Information table. An error occurs when I try to run it, saying that I need to define which table the field is from in the SQL code.

But then after I added clarification in the SQL, when I run the query it now prompts for a parameter for each of these fields. Why is this happening? I leave it blank, so a parameter has no impact on the query. How can I stop this?

Here's the SQL, after I added the table clarification:

Code:
SELECT (Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee].[Event Information])-nz([Reimbursed_Amount].[Event Information]))) AS Total_Cost, [Shared Billing Information].Paid, [Shared Billing Information].Shared_Billing_ID, [Event Information].Shared_Billing_ID
FROM [Shared Billing Information] RIGHT JOIN [Event Information] ON [Shared Billing Information].Shared_Billing_ID = [Event Information].Shared_Billing_ID

[Code] ....

View 7 Replies View Related

Query / Data Parameters

Jul 26, 2012

I have a report and export function which is based on date parameters. The field name called "date". However, I added a new field called "followup-date" because we would like to track customers that are returning back. Now, my problem is when I go to Export by date parameters or generate the report by certain date eg. July1st to July 28th, it only pulls up the record based on DATE field.

Is it possible for the date parameters to look up july1st to july 28th under date field as well as followup-date and pull up those records that are meet the date parameters? The reason I have to do this is because some customers do not come back for followup so I have to look under 'date' field too. While some customers can have more than one followup.

View 2 Replies View Related

Passing Operator Into Query Via Function

Jan 16, 2006

I have built a function which passes values into the criteria of a saved query. It has worked very well until I recently built a query builder based on this same concept. The user can select from a list of operators "<=", ">=", ">", "<", "=".

Example, the function will pass into the criteria the following:
<150

Unfortunately, I have exhausted all avenues for passing the "<" less than or greater than signs into the criteria. The Access SQL forces an equals sign before the passed in criteria and thus treats the "<150" as the entire criteria.

I have overcome no criteria by defaulting the function value to pass in the wildcard "*" and putting "Like" in the criteria.

Has anyone every handled a "variable" operator in a saved query in Access? :confused:

I am aware that I can change the QueryDef, use temp tables, and do several other longer methods, but the success of this concept will reduce an large amount of coding effort as well as make the software much easier to transfer knowledge on.

This overall method is really a great way to reuse criteria based on a form without hard coding.

Thank you in advance for your help,

Dave

View 3 Replies View Related