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 Replies
ADVERTISEMENT
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
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
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
Jun 10, 2012
I have a query with an expression as follows in criteria:
Between #1/1/2012# And #1/1/2012#
I want to pass into that expression a value from another table.
So, I created a table, DateRange, with fields StartDate and EndDate
Then, I modified my expression as follows:
Between [DateRange]![StartDate] And [DateRange]![EndDate]
Which, of course, didn't work, as the expression didn't know which 'row' to use.
View 4 Replies
View Related
Aug 31, 2005
I have a button on a form that creates an email and inserts fields from my database. I have a field that is set to Long Date format type, but when it populates the email, it shows as a Short Date format type. Is there anyway to retain the Long Date format?
View 1 Replies
View Related
May 16, 2005
Hi,
I have a control on a form which opens up Word and completes fields in the Word document with data on the form. It basically fills in an invoice form.
Everything works ok apart from when the invoice date and order date fields are passed. In the tables and in the forms, the dates are in Long Format ie '17 May 2005'. However, when they are apssed to the Word document, they are in a shortened format ie '17/05/05'. I am required to show the Long Format but am at a loss to find out how to achieve this.
I use bookmarks in the Word document and the code on my form relating to these fields are:
.ActiveDocument.Bookmarks("OrdDate").Select
.Selection.Text = (CStr(Forms!frmOrders!OrdDate))
.ActiveDocument.Bookmarks("OrdDateInv").Select
.Selection.Text = (CStr(Forms!frmOrders!OrdDateInv)
The Word document cannot be changed as far as I can see. I believe the answer could be to change the code above to change the format, but cannot get anything to work?
Your assistance would be helpful.
Thanks
View 1 Replies
View Related
Aug 5, 2006
In form1 I've a command button to open form2.
Code in form1:
Private Sub button_Click()
On Error GoTo Err_button_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "form2"
stLinkCriteria = button.Caption
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_button_Click:
Exit Sub
Err_button_Click:
MsgBox Err.Description
Resume Exit_button_Click
End Sub
button.Caption contains a valid date value
In form2 I want to display the date in a label.
Code in form2:
Private Sub Form_Load()
Dim DatePassed As Date
DatePassed = CDate(Me.OpenArgs)
label.Caption = DatePassed
End Sub
On executing I get an error like "Invalid use of null value."
Who helps?
View 5 Replies
View Related
Apr 12, 2013
I have the date set in the table and it shows up fine in the tblDate cell but doesn't pass through to the txttblDate in the form. I did have it working just fine until I rebuilt my database.
View 14 Replies
View Related
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
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
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
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
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
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
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
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
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
Jan 23, 2006
i have a query in design view to which i'd like to pass a criteria value for one field... I have some routine in VBA which creates a string as below:
"SupplierName1" And "SupplierName2" And "SupplierName3"
I am passing this to a textbox on the form exactly as shown above.
I would now like to use this value as criteria input for the query. However, it seems that Access doesn't recognise this....if I write just SupplierName1 in the textbox, the query works fine but as soon as I put in the quotes or else more than one supplier name, it does not work...
any ideas how can i resolve this?
Just FYI, the string of names of suppliers is being generated through string manipulation from a listbox using a value list.
Thanks in advance.
View 3 Replies
View Related
Jun 21, 2006
When I have my form I have an unbound control called rtdDate. In rtdDate's AfterUpdateEvent I have it run a append query. Within the append query I have the following:
RTD Date: forms!masterListQ!rtdDate
Yet it keeps coming up blank when you run the query. Its like its not finding the value of it. I'm sure you can tell I'm pretty much an amateur at database building but I thought this would be pretty easy.
What am I doing wrong?
View 4 Replies
View Related
Aug 8, 2006
I currently have a table showing activity for multiple staff and their availability throughout the day in 30 minutes segments.
I am currently trying to pull the information on who is working by 30 minute timeslice, but as the information is held in a different field for ech period, it is proving difficult.
My thought was to make a query rounding the current time to the nearest hour/half hour and use this to choose the field, but I don't know how to make a query which will allow me to pass a variable (Field name) into the Select query?
can anyone help on this, or have any other ideas?
Thanks
Andrew:
View 4 Replies
View Related
Feb 7, 2007
OK, I am trying to build sql strings from a form buy the user selecting criteria from boxes on a form.
I was given some code which I have adapted very slighty
Dim strCrit As String
If IsNull(Me.server) Then
' proceed
Else
strCrit = strCrit & " And [Server type] = '" & Me.server & "'"
End If
If IsNull(Me.RAM) Then
' proceed
Else
strCrit = strCrit & " And [Ram] = " & Me.RAM
End If
DoCmd.OpenReport "Your Results", , acPreview, strCrit
End Sub
however when I run this I get the following error
http://i145.photobucket.com/albums/r232/stuartkeith/errorsyntax.jpg
or trying to edit the code this error
http://i145.photobucket.com/albums/r232/stuartkeith/error.jpg
how do I pass the built SQL string directly into the query defs?
the strcrit when displayed looks like this
http://i145.photobucket.com/albums/r232/stuartkeith/stricrit.jpg
Thanks
View 1 Replies
View Related
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
Nov 13, 2007
Dear All, I have a problem which I need to solve and am in need of a clue!
I have a table which contains lots of line items relating to quotes. Each quote usually contains three line items and is prefixed in all cases with a number which relates to number of users. For example
5 5 User Software Details Price Cost Note
in the table there are up to 100 users and each has line 3 items. I automate quote generation by using a form. In the form a quote reference is generated and a text box for the number of users. I have a command button which then, based on the value of users text box, selects from the line items table and sends the records to my TblQuotes.
My Tbl quotes is exactly the same structure as my line items table except it also has a field for the quote reference which is in the form.
Where I am stuck is I cant seem to get Access to send the quote reference into the TblQuotes as part of the append query.
Please help! :confused:
View 9 Replies
View Related
Sep 16, 2005
Ok, i have a form in wich i want to set the parameters to a query.
On the search button it will open another form to display the data.
i used this code stLinkCriteria = "[ID]=" & Me![id] And "[status]=" & "'" & Me![sta] & "'
the 1st value for status is "active"
it returns a msgbox asking the value of the parameter and a field to input the data.
I can't understant whats wrong....
View 4 Replies
View Related
Nov 15, 2005
I have a hidden field on my form called "key". I can reffer to it as [forms]![MyForm]!key Is that right?
I also have a subform on this form that uses an sql query. I want this query to match on the key from above.
Shouldn't this work? select field1, field2 from table1 where keyvalue = [forms]![MyForm]!key
It doesn't. It returns a blank record.
Any help would be greatly appreciated.
View 1 Replies
View Related
