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 Replies


ADVERTISEMENT

Modules & VBA :: Passing Parameter To A Query - Data Sent To A Table

Jun 5, 2013

I have a function that when called transfers a query recordset to an excel spreadsheet then emails it. At the end of the function I use code to write the date sent to a table. Each time the function is called I only need records in the query that have been modified since the last time the function was called. I have a field in the query 'LastModified' with a criteria '>[Enter Date]'. I then look up the date in the table and enter it manually. I know how to look up the last date sent in table using code but getting the >#SomeDate# in the query with VBA.

Code:
DoCmd.TransferSpreadsheet acExport, , "qryUpdateWebmaster", _
"C:SubmarinersUpdates_Sent" & fname & ".xlsx", True, "Webmaster_Update"

View 1 Replies View Related

Modules & VBA :: Passing RecordSet As A Parameter

Sep 19, 2014

I'm trying to the following sub to automate the creation of RecordSets but I'm confuse how to get it done properly.

Public Sub OpenRecordSet(SQLString As String, NameRecordset As Object)
Set NameRecordset = New ADODB.Recordset
NameRecordset.Open SQLString, adOpenStatic, adLockOptimistic

I want to pass the name of the recordset as a parameter but I don't know how to set it.

View 1 Replies View Related

Modules & VBA :: Passing Excel Cell Date To Access Query

May 1, 2014

I'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.

I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """

This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"

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

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

Passing Parameter From Form Into Query?

Sep 3, 2007

I know this is probably a basic question- but Im not finding a clear answer here.

Basically- I have a value that I want to select from a drop down box on a form (not created yet). That value will get inserted into my query for a calculation I am doing. The form will pop up the results of the query in a table/dataset.

How do I designate the variable in the query that is being inserted from the form? I am using Access 2002- is there a way to visually perform this task (ie- drag/drop type thing)?
Thanks guys!

View 6 Replies View Related

Passing Text Box Value Into Sql Query Parameter

Apr 23, 2014

trying to pass a textbox value as parameter in a sql query.I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.This is an example:The user enter the data:

[Text0]
A1C556CC3C-TNNN
C010070H13

The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"

Code:
test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
[Forms]![Search]![Text0].Value = test
DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly

In the SQL code I use the IN operator to find the exact value for each record:

[code]...

I'm passing the value incorrect into the sql code?

View 7 Replies View Related

Passing Criteria To Append Parameter Query Through VBA

Dec 6, 2007

Hello Everybody,

I have an append query that contains a parameter [PTIdent] under the field named PTID. I would like to be able to set the parameter through VBA from a control on a form eg Form!PTID

I use the following code as normal
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

Dim stDocName As String

stDocName = "apNewPres"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub

Is there a way within this code to do this? I realise I could set the form criteria within the query itself. However I wanted to be able to use this query from multiple forms.

Thanks for any help

View 2 Replies View Related

Passing Multi Parameter Values To A Query

Aug 14, 2005

I have a query set up which needs to have different criteria at run time depending on values selected by the user. If no option is specified, I need to pick up all records with 'Nulls' in them else, if date is specified by user, I need to pick up all records with date > than specified date. The query is able to pick up the date value from an unbound text box in the form. I used the same field in the form and populated "Is Null" in the field and it does not work. How do I pass this as a paramater to the query from the form?
Is there a way around without setting up 2 sets of queries and reports?

Thanks,Priya :mad: :confused:

View 5 Replies View Related

Queries :: Passing Parameter To Select Query?

Jun 5, 2013

I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.

Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far.

Private Sub SingleItemRptB_Click()
If DCount("[FieldName]", "[Table]", "[FieldName]=[TextBoxValue]") = 0 Then
MsgBox "Item not in database. Please check value and re-enter."
Else
DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly
End If
End Sub

View 4 Replies View Related

Queries :: Passing (Is Not Null) As A Parameter In A Query?

Jun 16, 2015

I have a form which users can toggle whether they want to only see entries that has data in a certain field. Previously I have set up a separate query with a hardcoded 'Is Not Null' in the criteria, and set the form to call the different queries based on the status of a toggle button. This time there is 22 queries that need to be modified so I'm hoping there's a better way.

I have a hidden textbox on my form that has value "Is Not Null" or Null based on the position of the toggle button.

In my query for the field criteria I have [Forms]![MainForm].[txtCriteria] where txtCriteria is the textbox previously mentioned.

When I run the query no data is shown.

View 10 Replies View Related

Queries :: Passing Textbox Value Into Sql Query Parameter

Apr 23, 2014

I'm having a very simple issue (I think) trying to pass a textbox value as parameter in a sql query.I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.This is an example:The user enter the data:

[Text0]
A1C556CC3C-TNNN
C010070H13

The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"

Code:
test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
[Forms]![Search]![Text0].Value = test
DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly

In the SQL code I use the IN operator to find the exact value for each record:

FindPartNo sql query:

Code:
SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy
FROM Classifications
WHERE Classifications.PartNumber In ([Forms]![Search]![Text0]);

The problem here is, the query doesn't return results but if I modifiy the query and I put:

Code:
WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");

... the query returns the correct results.I'm passing the value incorrect into the sql code?

View 1 Replies View Related

Queries :: Passing Column Name Parameter To Query

Aug 29, 2013

SUMMARY:
In Access 2007, can I pass a Column Name as a parameter from a Combo Box in a form?

DETAILS:
I would like to use a String value from a Combo Box as a parameter in a Inner Join query:
...
WHERE (((AAA.HSC) Like Forms!My_Form!My_TextBox)
AND((CCC.Forms!My_Form!My_ComboBox)="X"))

Where "CCC is a Table from my Join Query

I'm able to run the code above without generating any errors. However, instead of the query accepting the value from the Combo Box, it opens a pop up asking for the value of "CCC.Forms!My_Form!My_ComboBox", not once, but twice! After it finally runs, the result set is empty when it should not be. I also find it strange that it is asking for the combo box value with the Table name appended to the front.

View 4 Replies View Related

Passing A Parameter In A Query To Update Linked Table?

Oct 8, 2015

I have a linked table tblHome which is stored in a Sql Server DB and I want to create a form with 3 fields in it i.e. fieldA, fieldB, and FieldC in it and a button.

I want to add values to fields fieldA and fieldB and fieldC and when I click the button I want the value in fieldA to update any records in the linked table tblHome which contains the values in fields fieldB and FieldC.

how to do this?

View 5 Replies View Related

Reports :: Passing Date Range Parameter To Multiple Sub-reports

Nov 4, 2013

I am trying to pass a date range parameter & an additional parameter (Type of Audit) to 6 subreports based off individual crosstab queries and housed on one Unbound Report ("rptFinal").I have an Unbound Form "frmDate" passing a date range and Type of Audit using [Start Date] and [End Date] to rptFinal with a button that simply opens the rptFinal as follows:

stDocName = "rptFinal"
DoCmd.OpenReport stDocName, acViewReport

In each Crosstab query, I have set the parameter criteria (in both the Parameter section & the query itself) to:

[Forms]![FrmDate]![Start Date] And [Forms]![FrmDate]![End Date]
and also,
[Forms]![FrmDate]![Enter Type of Audit]

On each subreports On Load Event, I have added:

Private Sub Report_Load()
Me.Filter = "[DteAuditDate] BETWEEN #" & Forms!frmDate![Start Date] & "# AND #" & Forms!frmDate![End Date] & "#"
Me.Filter = "[Type of Audit] = #" & Forms!frmDate![Enter Type of Audit] & "#"
End Sub

and I've set the Filter On Load property to: Yes..I can open frmDate, fill in the date and Type of Audit, launch the report and it runs with no error, I have 6 blank subreports in report Preview. The headers are showing up but none of the data.

View 2 Replies View Related

Modules & VBA :: Passing Date Strings Into Access Chart

Sep 11, 2013

I would like the user to be able to select the months he wishes in case they want to look at calender year, financial year or just a custom group of months. If I use the wizard and pick dates say the start and end of the year I get the following code in the row source of the chart control:

Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN #01/01/12# AND #31/12/13#) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));

So I decided all I needed to do was replace the dates in the above code with my own global varible which i would pass custom dates into via a form. Which I called getds() and getde()

Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN >=#getds()# And <=#getde()# ) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));

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

Passing A Date Field To A Query

Apr 9, 2007

Hi All,

I wonder if anyone can help me? I am at the stage now of building a query in design view. Rather than using a dynamic parameter field to capture a range of dates (between...[InputDate] And [InputDate]), I have created a text box in a form and want to pass the contents to my query. I have got this to work providing the variable that is passed is 'text'. I need to pass two dates though. When I put paths to the forms textbox in the 'Between' statement above, it just doesn't return any records. I think Access sees these text boxes as 'text' rather than 'dates'. I don't know how to change it so Access sees these as dates. Any ideas?

View 3 Replies View Related

Passing Parameter

May 23, 2007

i have a working stored proc and a working access pass through query

the below is working in access
exec appcheck @myapp='1'

but i want to point directly to a form

ive tried this but with no success
exec appcheck @myapp=[forms]![frm_test]![pass]


Also i would like to know how to return the results directly into a table?

View 2 Replies View Related

Passing A Parameter To A Combo Box.

Sep 25, 2005

Hello,

I currently have a student membership database with a main Members form. When it opens it prompts me for a parameter which determines if only active, inactive or all members will be displayed. Once the form opens I use a combo box to search through all of the records. Currently this combo box lists all of the members names regardless of the parameter. To address this I set the same parameter query for the combo box. This works fine but it means that I have to enter the same parameter twice. What I would like to do is to have the 1st parameter, when the form opens, pass to the combo box. Any pointers would be greatly appreciated.

Also, if anyone knows of a more elegant way to accomplish this other than using a parameter query please feel free to pass this along.

Thanks,
Chris

PS: I am running Access 2003.

View 3 Replies View Related

Passing List Of Parameter

Sep 14, 2011

I have list (server names) in excel file (200 rows), I would need to query the Model of these from in MS access, How do I pass these list in excel to the query.. I can't be running the query 200 times nor I could type them one by one.

View 3 Replies View Related

Passing A Parameter For The Order By Clause

May 26, 2005

Can someone please tell me if it is possible to pass in a parameter for the sorting field to a stored procedure in MS Access.
When I tried to execute a query similar to below, neither did the query give me an error nor did it execute correctly.

PARAMETERS ORDERBY_FIELD as Text;
Select * from Employees Order By ORDERBY_FIELD;

It will be great if someone could help me with this.

Thanks.

View 1 Replies View Related

Passing Parameter From A Data Access Page.

Jul 28, 2005

Hi - I am looking for some help understanding the instructions in the msdn article on how to pass a parameter from a DAP. Please refer to the article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_PassParam.asp


I was able to follow the instructions until Page 2 of this article...where it says: "Add the functions to write, read and delete cookies in a separate global script block."

I do not understand how to create a global Script block. Do I just copy and paste this code right below the 1st code on Page 2? Or, is there a way to create a global script block separately and add this code there?

I am unable to find that much Online resources on DAP as other Access topics. I thought posting my question here would be the last resort for assistance! :confused:

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