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 Replies


ADVERTISEMENT

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

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

Modules & VBA :: Add WHERE Clause To Query (queryDef)

Dec 1, 2014

I have a TRANSFORMED query:

Code:
TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

This query is record source for a report, then this report show all calculations in a form.

I have 7 of each (query + report) all showing on the same form.

All those queries calculate data for all departments.

In the form, I have placed a combobox.

What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.

Code:
WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats

However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.

Where do I place the vba statement? Is it under combobox AfterUpdate event?

I am planning to use this: (As I never done it before)
[URL] ....
Modifying SQL On-The-Fly section

View 5 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

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

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

Access Critereia Query - With Blank Fields And Search Parameters

Jul 19, 2007

Hi everyone...

I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.

Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status

Only "Email" is mandatory, ID is autonumber, the rest are optional.

I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"

Presently, I am using the similar criteria for all the fields:

Like "*" & [Forms]![Search]![txt_FirstName] & "*"


The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"

If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.

How can I alter the criteria for it do search correctly?

I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""


Thanks,
Gautam

View 7 Replies View Related

Access Critereia Query - With Blank Fields And Search Parameters

Jul 19, 2007

Hi everyone...

I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.

Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status

Only "Email" is mandatory, ID is autonumber, the rest are optional.

I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"

Presently, I am using the similar criteria for all the fields:

Like "*" & [Forms]![Search]![txt_FirstName] & "*"


The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"

If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.

How can I alter the criteria for it do search correctly?

I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""


Thanks,
Gautam

View 2 Replies View Related

Modules & VBA :: QueryDef And Export To Excel

Jun 13, 2014

So I had this code working and then I cleaned it up a little and it no longer works. It should export data from a created query using criteria selected by the user on a form and put it into an excel file that exists. I get no errors but it does not export anymore. After pouring over it for a while checking for mistakes with my form control references and variables I have yet to find anything. I did change my form name and edited the code accordingly after I already had it working, and changed a few form settings but changing them back did not fix the issue. I am not very experienced and stumped since I am not getting error messages.

Code:

'First set variables for the SQL string and CreateQueryDef command
Dim strExport As String
Dim qdf As dao.QueryDef
'Then define the SQL to be exported (Static Response Info by ItemID)

[Code] ......

View 6 Replies View Related

Modules & VBA :: Filtering Form / Report - QueryDef Criteria Using OR

May 7, 2015

I have a form with 3 combo boxes that filter another form/report. The first combo (cboByCategory) contains options from 2 different fields within the same table. Before I added this add'l piece of code, all 3 combos worked fine. I am not getting error messages, it just does not filter on the other 2 combo boxes - cboDiv and cboGender.

Code:
Private Sub cmdModifyRecords_Click()
On Error GoTo Err_cmdModifyRecords_Click
Dim stDocName As String
Dim strFilter As String
Dim stLinkCriteria As String
stDocName = "Modify_OpenItems"

[Code] .....

View 5 Replies View Related

Modules & VBA :: Finding Last Record (QueryDef Or Recordset Method)

Feb 11, 2014

My question is which method of finding the last record is best, QueryDef or Recordset? Here is my data:

Table:
tbl_module_repairs

Field:
aps_rma

Textbox to insert last record RMA into:
txt_test

Here is some code I tried but get an invalid argument msgbox:

'''''''''''''''''''''''''''''''''
'Opens last RMA into textbox (For opening tag sheet)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
'Get the database and Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_module_repairs")

[Code] .....

View 14 Replies View Related

Modules & VBA :: Edit Recordset Querydef That Has Calculated Field

Jan 19, 2014

Is it possible to add/edit a record from a DAO querydef that has a calculated field in.

Code:

Set qdf = db.QueryDefs("qryOutput_" & strDept)
qdf.Parameters("Enter Date") = dDate
Set rs = qdf.OpenRecordset()

Using the rs.Edit or rs.AddNew worked fine until I put a calculated field in the query.

The calculated field is just a total of some fields in the table.

I thought this was something to do with dbOpenDynaset but it just keeps having the same error

Cannot update. Database or object is read-only.

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

Modules & VBA :: Pass Through Query With Parameters

Jan 22, 2015

This is my first time writing a pass through query pinging sql tables using an input parameter from a form. I have gotten as far as executing the query but I can't seem to display the result to ensure it's pulling the right records. I also want to be able to append the records to a table. Below is the code I have written so far:

Sub GETRT()

Code:
Dim db As DAO.Database
Dim QDF As QueryDef
Dim STRSQL As String
Dim RS As Recordset
STRSQL = "SELECT * FROM LAB_MESR.ODM_RT_DAYS" & _
"WHERE LOCATION_ID=" & [Forms]![PARMS]![STR_NBR]
Set db = CurrentDb
Set QDF = db.QueryDefs("001:GET_LT")
QDF.SQL = STRSQL
End Sub

View 5 Replies View Related

Modules & VBA :: Passing TempVar To SELECT Query

Feb 25, 2014

Picture, if you will, a table with multiple fields, each of which contains the date of a certain action in the process tracked by each record. I have a standard report format that will be used to view the progress of the actions. I want to be able to sort the report - on demand by non-technical users - by whichever date field they choose.

I've created a form with a combo box that is linked to a table with the name of each field in the source table. A Macro has been configured to open the form when the report is opened, which will prompt for the selection of the sort field. That value is then captured into a TempVar.

When the user clicks OK control passes to a procedure in a module that executes a SELECT statement (SQL) that configures the Query that is used for report generation. The "ORDER BY" portion of the statement needs to be modified with the name of the sort field desired. If the statement is hard coded as, for example:

...ORDER BY Main.[Initiated Date] DESC;

it works fine. I want to use the value of the TempVar to provide the name of the date field (ex.: [Initiated Date]), but I can't determine the correct syntax to get the statement to accept it. Concatenating doesn't seem to work, and I've searched high and low for hints on the web. Here's one version of what I've tried (and which fails):

"...ORDER BY Main." & [TempVars].[SortParm] & ";"

The error returned by the above version is: "Object doesn't support this property or method"

(SortParm is the name of the TempVar, and it is correctly populating, and keeping, the value I need from the form.)

View 3 Replies View Related

Modules & VBA :: Passing Parameter And Date To Query

Nov 10, 2014

I have the following Visual Basic code which I am using to dynamically pass the parameters "SAFP" and date 10/31/2014 to the query:

Option Compare Database

'------------------------------------------------------------
' Run_Risk_Assessment_Report
'
'------------------------------------------------------------
'Original macro code
Function Run_Risk_Assessment_Report()
On Error GoTo Run_Risk_Assessment_Report_Err
Dim dbs As DAO.Database
Dim test1 As DAO.QueryDef

[Code] ....

When I run the query, i get the error "Item not Found in this collection"

View 14 Replies View Related

Modules & VBA :: Pass Set Of Known Parameters To Query Then Export

Feb 12, 2014

Table: DailyExport
Field: FailureGrouping (actually is offices)

I want to export all the fields from DailyExport each of our 9 offices to Excel, whetjer or not they have date in the DailyExport table. So if not, the exported workbook would only have columns headings.

I need to loop through the nine offices and export each office report to Excel.

Dim StrQry As String
Dim strfullpath As String
Dim SOffice as String
strSQL = "SELECT * FROM DailyExport WHERE FailureGrouping = " & SOffice
strfullpath = "Y:" & SOffice &" "& Format(Date,"mm-dd-yy") & "_Failures.xlsx"
DoCmd.TransferSpreadsheet acExport, , FailureGrouping, strfullpath, False

View 1 Replies View Related

Modules & VBA :: Between Dates In Where Clause - Query Using Parameters

May 30, 2014

I have this code below which pulls a report based on the current date, I wanted to be able to pull the same report by entering between 2 dates as is done in a query using parameters.

Code:
reworkWhere = "ReworkTech = '" & Me.txt_tech_by_date_techid & "' And ReworkTimeOut = Date()"
repairWhere = "RepairTech = '" & Me.txt_tech_by_date_techid & "' And RepairTimeOut = Date()"
qcWhere = "QC_Tech = '" & Me.txt_tech_by_date_techid & "' And QC_TimeOut = Date()"
strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_RF_TECH_REPORT_UNIT_DAILY", acViewReport, , strWhere

How to make a combo box with the months listed so they can pull this report by the month selected but is a side tangent.

View 3 Replies View Related

Union Query Prompts For Parameters - Returns All Records With Parameter Entered!

May 17, 2006

Hello all,

I realise this issue is a common one and it is usually down to simple typos (I've looked at several similar posts) but none of the advice I've seen has solved my problem. I've designed the following Union Query:

SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 1]

UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 2]

UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 3];


It should be very simple and I've checked it over and over, but when I run the query an Enter Parameter dialog appears prompting me to enter Email.

If I just click OK I get all the records but with the email field blank.

Similarly if I type x@y.com it returns all records but with the email fields all containing x@y.com

Advice would be very much appreciated!! Thank you in advance.

Abi

View 2 Replies View Related

Modules & VBA :: Passing Variables To More Than One Field As Query Criteria?

Feb 25, 2014

My problem is as follows, i have created a report that calculates the total volume of FSC Materials. The user picks two dates from Calender controls that the report will range from. However the needs have now changed and i am required to make the report filter further based on user input, the problem i'm facing is that i cannot figure out a way to pass values from different variables to the report separate from another here is the code i would usually use to pass data to a query/report:

Code:
Private Sub MonthlyFSC_Click()
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()

[Code] ....

However i am now trying to do this, but it gives me an error as it is trying to pass the values to one field:

Code:
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()
sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"

[code]....

It is performing incorrectly within the case select and passing the wrong criteria, as it will only display results that meet the default values' criteria. However the date criteria is not be passed either.

View 4 Replies View Related







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