One thing I was going to ask in a new thread was this, which I think your solution addresses if it is a problem.? I have CJ_London's EVAL solution to try out and also one of my own, which I think is slightly easier for me to implement and similar to what you have posted. I do believe however that modifying the qdf is the way I would like to go. I created a letter creation DB with help from this site, and it has never been used, users happy to modify templates indivisually.
#Ms access query parameter how to#
Short sightedness perhaps, but as eveything takes me a while to create/work out how to do it, I only do what is required at the time. Now it would be nice to report on any selected types of record. The report and query in question simply reported on one type of record. I realise that this is not the correct way to do things, but as I have no spec to start with and the DB is modified when I have time from other tasks, I pretty much build it up as I go along. Thinking about it last night that argument could just as easily be the query name.? I know I could pass the criteria as an argument, in fact I pass an argument at the moment, but that is just to flag which query to run, A or B I do admit I am trying to achieve this with the minimum of change but also with a view to the future. TBH I try and stay away from lengthy sql strings unless absolutely necessarily due to my inexperience and generally generate them via the query design window, then modify to suit. MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _Įrr.Number & vbCrLf & "Error Source: CreateQuery" & vbCrLf & "Error Description: " & _Įrr.Description, vbCritical, "An Error has Occured!" CreateQueryDef(sQryName, sSQL) 'Create the queryĭb.QueryDefs.Refresh 'Refresh the query list to display the newly created query On Error GoTo Error_Handler 'Reinitiate our standard error handler
![ms access query parameter ms access query parameter](https://oakdome.com/programming/images/accQueries6.png)
![ms access query parameter ms access query parameter](https://debradalgleish.com/blog/wp-content/uploads/2017/08/accessquerydates07_thumb.png)
QueryDefs.Delete (sQryName) 'Delete the query if it exists 'we set our error handler to resume next it will skip over it and continue 'If it exists it will be deleted, otherwise it will raise an error but since 'In the next line we try and delete the query It may not be sold/resold or reposted on other sites (links ' copyright notice is left unchanged (including Author, Website and ' Copyright : The following may be altered and reused as you wish so long as the ' Purpose : Create a new query in the current database If I can just get the construct of this IN clause correct and into the query I am there.įunction CreateQuery(sQryName As String, sSQL As String) I wish to tailor it further to show the selected types of records processed, be they Workdays, sick days etc.Įssentially I would like the report to run either without the IN clause (which would show me all the records for an employee within selected dates or with it (limiting the records to selected types). Initially the report only reported null hours (records to be processed) and can now show (records processed). The reason I am doing it this way is because the source for the report can be changed on open depending on an argument to the report.
![ms access query parameter ms access query parameter](http://www.databasedev.co.uk/image/enter_a_region.gif)
I am trying to work out the syntax to insert/change the citeria? Qdf.Parameters("ParameterName") = strParam I've seen on here in the past something along the lines of 'DoCmd.OpenReport "rptHours", acPreview,, ,, "Selected" StrParam = Left(strParam, Len(strParam) - 1) StrParam = strParam & ctl.ItemData(varItem) & "," ' Now select what type of day records from listbox 'Open the report with an OpenArgs value to get selected hours for employee ORDER BY tblDates.DayDate, Year() & DatePart("ww".) TIA WHERE (((tblEmployeeDay.EmployeeID)=!!) AND ((tblDates.DayDate) Between !! And !!) AND ((tblEmployeeDay.DateType) In (15,16))) SELECT tblEmployeeDay.EmployeeID, nz(WeekdayName(Weekday(.,2),True),"") AS DayName, tblDates.DayDate, & " " & AS FullName, tblEmployeeDay.StartTime, tblEmployeeDay.EndTime, tblEmployeeDay.Lunch, IIf(=15 Or =16,0,calctime(,)/60) AS Hours, tblEmployee.ReportsTo, & " " & AS Manager, tblLookup.DataValue, tblEmployeeDay.DateType, Year() & DatePart("ww".) AS GroupDateįROM tblDates INNER JOIN (((tblEmployee INNER JOIN tblEmployeeDay ON tblEmployee.EmployeeID = tblEmployeeDay.EmployeeID) INNER JOIN tblEmployee AS tblEmployee_1 ON tblEmployee.ReportsTo = tblEmployee_1.EmployeeID) INNER JOIN tblLookup ON tblEmployeeDay.DateType = tblLookup.LookupID) ON tblDates.DayID = tblEmployeeDay.DayID