Queries :: Filter Query To Calculate Sum Of Fields In Date Range
Nov 18, 2014
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.
I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.
An example of the parameters used for one of the checkbox fields in Query Builder is as follows:
Field: SumAnger: Sum([Anger]*-1) Table: Default as only one table Total: Expression Show: Checked
This works fine.
My latest parameters for the date range are this:
Field: [cDate] Table: Default as only one table Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM! Show: Checked or Unchecked makes no difference Criteria: Between [From Date:] And [To Date:]
This gives a statement in SQL view of:
SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate] FROM tblCommsLog WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));
what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.
I am trying to write a function in an access 2007 query that will calculate the date range for each of my records based on the month-ending date and the In-transit days assigned to each record (excluding any weekend days) (i.e. if the in-transit days number is 5 & the month ending date is 5/26/12 then my date range would be 5/22/12-5/25/12). I want the funtion to calculate the range, I cannot use parameters as each record will have a different range based on it's in-transit days .
I m trying to make form which filters my records and generates a report..here's where I am
Code: Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Hi all, Please help me correct this criteria for a filter in my query. I want to show all records between todays date and the past 14 days. I've tried,
I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.
Problem 1 I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?
Private Sub cmdReport_Click() Dim varItem As Variant Dim strDocName As String Dim str1MainCate As String Dim str2MainCate As String Dim str2MainCateCondition As String Dim strDate As String Dim strSQL As String Dim strFilter As String ' Build criteria string for 1st ComboBox For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2nd Combo Box For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1toggle button condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " 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.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";" ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.dateTo, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If ' filter string strFilter = strDate ' Open report strDocName = "RptCateDateQry" DoCmd.OpenReport strDocName, acViewDesign, , strFilter With Reports(strDocName) .RecordSource = strSQL .Filter = strFilter .FilterOn = True End With DoCmd.Save acReport, strDocName DoCmd.OpenReport strDocName, acViewPreview Exit_cmdReport_Click: End Sub
Problem 2 I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.
Private Sub cmdOK_Click() On Error GoTo cmdOK_Click_Err Dim blnQueryExists As Boolean Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View Dim varItem As Variant Dim strDate As String Dim str1MainCate As String Dim str2MainCate As String Dim str1MainCateCondition As String Dim str2MainCateCondition As String Dim strSQL As String ' Check for the existence of the stored query blnQueryExists = False Set cat.ActiveConnection = CurrentProject.Connection For Each qry In cat.Views If qry.Name = "QryCateDateForm" Then blnQueryExists = True Exit For End If Next qry ' Create the query if it does not already exist If blnQueryExists = False Then cmd.CommandText = "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" cat.Views.Append "QryCateDateForm", cmd End If Application.RefreshDatabaseWindow ' Turn off screen updating DoCmd.Echo False ' Close the query if it is already open If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then DoCmd.Close acQuery, "QryCateDateForm" End If ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.textStartDate, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If
' Build criteria string for 1MainCate For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2MainCate For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1MainCate condition If Me.optAnd1MainCate.Value = True Then str1MainCateCondition = " AND " Else str1MainCateCondition = " OR " End If ' Get 2MainCate condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " 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.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _ str1MainCateCondition & strDate & ";" ' Apply the SQL statement to the stored query cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("QryCateDateForm").Command cmd.CommandText = strSQL Set cat.Views("QryCateDateForm").Command = cmd Set cat = Nothing ' Open the Query DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point ' DoCmd.Close acForm, Me.Name ' Restore screen updating cmdOK_Click_Exit: DoCmd.Echo True Exit Sub cmdOK_Click_Err: MsgBox "An unexpected error has occurred." _ & vbCrLf & "Procedure: cmdOK_Click" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description:" & Err.Description _ , vbCritical, "Error" Resume cmdOK_Click_Exit End Sub
Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code: Option Compare Database Private Sub cmdCal_Click() Dim sql As String Dim rs As Recordset Dim qdef As DAO.QueryDef Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName FROM Staff INNER JOIN myTable ON Staff.ID = myTable.StaffID WHERE myTable.myDate >= #3/2/2014# AND myTable.myDate <= #3/3/2014# AND myTable.myTime >= #8:00PM# AND myTable.myTime <= #11:00PM# ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
I'm trying to produce a query that shows all records of patients that have a 'non-active' status (stored in the 'Patient Details' table) and haven't had any deliveries after 31/10/2011 (date stored in the 'Deliveries' table). I've tried a few different ways including using NOT IN (which access didn't like!) but I'm still no closer to getting the correct records.
I am trying to create a simple database to keep track of employee Car Insurances and MOT information.I am trying to create a query that will show me the following:
When the field "motexpiry" is Empty OR has a date within 30 days from todays date (including if today's date is in the field) OR the date is in the past.It also needs to show records with the same criteria for the field "insuranceexpiry".
And needs to show records where the field "cowensform" is blank.These are all OR queries, so that as long as ONE of all of those criteria is met, the record shows up.Once that query works, I need a very similar query but only showing records where one or more of those criteria is met, but only if the record also has "Oldham" in the "area" field.
I can then copy that query and edit the "Oldham" bit to have a query for each of our area offices.I tried putting "Oldham" in the criteria line of the area field in the query design, but it seemed to have no affect.
I have a query that is hard coded with a date range.
SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI, FROM tblCase WHERE (((tblCase.Letter_AMPI) Between #4/1/2014# And #3/31/2015#)) OR (((tblCase.ReqReceived) Between #4/1/2014# And #3/31/2015#)) ORDER BY tblCase.CaseId;
I would like to create a parameter query that allows me to only enter the year and the query would append the rest of the date range. So for example, if I prompt the user to enter the date and they enter 2014, the query would know that it means Between #4/1/2014# And #3/31/2015# or if I enter 2015, it means Between #4/1/2015# And #3/31/2016#.As well the date would need to go into both fields ReqReceived and Letter_AMPI.
I am trying to calculate the total hobbs time (Ending Hobbs - Starting Hobbs = Total Hobbs) based on a user inputed date range. The query that I created (see attachment) doesn't seem to give me what I'm wanting.
I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
I am trying to use VBA to create a filter by date range. the user inputs 2 dates and the database filters all records by dates in between the 2 dates,
Code below
Dim var_CustDate1 As String Dim var_CustDate2 As String var_CustDate1 = InputBox("Please enter start date in format DD/MM/YYYY", "Enter Date", Date) If Not IsDate(var_CustDate1) Then MsgBox ("not a valid Date")
[Code] ....
I've tried every combination of format for the dates but this is the closest ive got it to work,
if i enter dates 01/09/2013 and 12/09/2013 the filter works for the days in the month but also displays previous years, but if i change the dates to 01/09/2013 and 13/09/2013 it starts displaying all dates for all years in the months September, October, November and December.
The data the query pulls is employee name, course ID, course name and course completed date. I have added criteria in the query that asks for the Course ID to filter on a given course and a dynamic field in the query AnnualReqDate: DateAdd("d",+365,[TrainingCourseCompleted]). What this returns is a list of employees that have completed course X the date they completed and the date (12 months) when the course is due. This works great, have created a report that reflects this very well.
The issue comes in when I try to add the ability to filter by date range on the AnnualReqDate dynamic field. the AnnualReqDate dynamic field does not exist until the query is run the Between [Start Date] And [End Date] criteria add to the AnnualReqDate dynamic field wont work.The query fires off but returns zero records.
I have an database that uses a couple of different date ranges, so I created a table that shows the different date ranges that may be required (xReport Dates) so I didn't have to keep manually editing queries or entering dates every time.
I have one query that appends data from one table into another based on a date range that you need to manually enter when prompted; I can't seem to get it to refer to my xReport Dates table for the range.
i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.i have two databases one is the transaction file and the other is the user file.
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click() Dim SQL As String SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _ & "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _ & "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
created a query (in Access 2010) that joins several linked tables (to an Oracle database). The query runs in about 20 seconds when I filter with a hard coded date (e.g., #12/31/2014#). The Oracle table column Im filtering on is defined as date/time.
When I attempt to change the hard coded value to a soft coded value (e.g., Forms![Form1]![Latest_Extract_Date]), the query runs over 5 minutes. In this case, the form field has the exact same value (12/31/2014).
Ive encountered similar issues using Access 2000, 2003, etc. This is quite frustrating. Does Access interpret #date value# is a special way? Is there a way to trick Access into the thinking a soft coded date is a hard coded date?
I have a form for entering safety training which includes Training Date and Expiration Date, this form is called "Scheduled Training".
The table containing training courses with their requirements and frequency is called "Course Requirements". Within this table I have a "Frequency" field (1, 2, 3, 4, 5, 6) and another field "FrequencyPeriod" (Year, Month, Initial, As Needed).
What I'd like for the Expiration Date on the form to do: Use [ScheduledTraining].[Training Date] and look at both the [CourseRequirements].[Frequency] & [CourseRequirements].[FrequencyPeriod] to populate the Expiration Date.
