One Query To Return Either Records Within "Date Range" Or "12-Month Rolling"
I have a query that selects usage records from a table where a data field falls within a rolling 12-month period that ends on a month and year selected by the user on a form.
PARAMETERS [Forms]![frmReport]![cboMonth] Short, [Forms]![frmReport]![txtYear] Long;
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((DateDiff("m",[dtUsage],DateSerial([Forms]![frmReport]![txtYear],[Forms]![frmReport]![cboMonth],1))) Between 0 And 11));
I also have a query that pulls data between two dates that the user specifies using two textboxes on a form.
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]));
On the form I have an Option Group control that sets the visible properties of the 4 controls;
12-Month Rolling
cboMonth - Month Combobox
txtYear - User entered year
Date Range
txtStartDate - User entered Start Date
txtEndDate - User entered End Date
What I was planning on doing was adding two new textboxes (txtQryStart & txtQryEnd) and depending on what option is selected, change the dates accordingly
blnDateRange = (Me.optDate = 1)
blnMonth = (Me.optDate = 2)
If blnDateRange Then
Me.txtQryStart = Me.txtStartDate
Me.txtQryEnd = Me.txtEndDate
End If
If blnMonth Then
If Me.cboMonth = 12 Then
EndMonth = 1
EndYear = Me.txtYear + 1
StartMonth = EndMonth
StartYear = Me.txtYear
Else
EndMonth = Me.cboMonth + 1
EndYear = Me.txtYear
StartMonth = EndMonth + 1
StartYear = Me.txtYear - 1
End If
Me.txtQryStart = DateSerial(StartYear, StartMonth, 1) - 1
Me.txtQryEnd = DateSerial(EndYear, EndMonth, 1)
End If
Now I can use one query to accomplish both types of query;
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtQryStart] And [Forms]![frmReport]![txtQryEnd]));
This approach works. It saves me from having to duplicate the queries and some future headache if anything needs to be changed in the queries.
What I was wondering is there an better/simpler way to do this?
:confused:
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Return 'month' For A Given Date
I have a table which has a column 'Date', which contains dates in the format dd/mm/yy. I wish to produce a quiery which will produce an extra column entitled 'month', which will match each date to the month of that date. I know there is a formula which does this (called 'month' i think), but i dont know how to use it to achieve what I want. Any help would be greatly appreciated!
View Replies !
View Related
Date Range By Month Number
I am trying to make a query that finds all records by the "Month Number" and have problems. What I did: Month(DateField) I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine. I want: Between{month1] and [month2] Month1= 9 (example) Month2= 12 (example) Need results to be from the requested months but any year I have to imagine it's how the date is converted to an integer, but not sure. Any help would be great!
View Replies !
View Related
WorkUnit Totals By Month Via Date Range
Below is the code I have for a query that shows me the number of WorkUnits via a date range that is put in via calendars. This works great. It gives me one total for the date range. What I would like to be able to do is see the WorkUnit totals by month via a date range. If I put in the date range: Jan 1-April 30 I would like to get four WorkUnit totals instead of one. I would like to see the WorkUnit totals for each month. Can anyone assist with this? I asked a similar question like this yesterday but he SQL for this code is different and I have not been able to figure out how to apply yesterday's answer to this one. I have tried everything I can think of to no avail. SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals] FROM [Select Distinct [WorkUnit] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("G004","E818","N005","F813","D024","C879") AND PossibleCause NOT IN ("Out of Stock") AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;
View Replies !
View Related
Rolling 12-month Total
Hi all, I have a problem creating a query calculating a rolling total based on the last 12 months grouped by product. The table: Product no. / Period / Gross Rev / Clients 810 / 01-01-2006 / 1.000 / 10 810 / 01-02-2006 / 1.000 / 10 ... 810 / 01-01-2007 / 500 / 10 990 / 01-01-2006 / 1.000 / 10 990 / 01-02-2006 / 1.000 / 10 From this table I want to create a rolling total so that the rolling gross rev of product 810 MAR07 is based on APR06-MAR07 data. The same applies to the number of clients. Is this possible? E.g. Rolling sum of clients in OCT07 = No. of Clients (NOV06+DEC06+JAN07+ ... +SEP07+OCT07) Rolling sum of clients in JUN07 = No. of Clients (JUL06+AUG06+ ... +MAY07+JUN07) The problem is that the output has to be a table/query (a complete list) with following data: - Product - Period - Gross Rev (prenst) - Clients (present) - Rolling Gross Rev (last 11 month + the present) - Rolling Clients (last 11 month + the present) The table is to be exportet to Excell and used in a pivot. I have tried using the Dsum function but I have only managed to calculate a running total (not based on the last 12 months). Can somebody help me here?
View Replies !
View Related
Query For A Month Range Based On Combobox Entry
Hi, I currently have a form that creates a report based on a query that takes in a start date and an end date. What I would like to do is to create comboboxes that will let the user choose a month and year for the report. Is there a simple way to choose an entire month in access? I've tried using the Month() function to no avail. The SQL statement for where I select the start and end date is below: WHERE ((([tblData].[EntryDate]) Between [Forms]![frmByRange]![BeginningDate] And [Forms]![frmByRange]![EndingDate]) Any help would be much appreciated!
View Replies !
View Related
Create Multiple Records Based On Date Range
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one. Any suggestions or assistance would be appreciated. Thankyou
View Replies !
View Related
Rolling A Date Back 12 Months Question
I have a query that takes a date from a selection form. Is there a way to window my data by taking this date and going back 12 months to give me a block of data covering this 12 month window. Example: Date entered is 03/dd/06 Date window desired is 04/01/05 thru 03/14/06
View Replies !
View Related
Date Range Query
I am trying to extract records within a certain date range. My structure is as follows: Query 1 = pulls data direct from a table. There is a date field which is in the format YYYYMMDD. Query 2 = pulls data from Query 1 and amends the date format to: dd/mm/yyyy Has anyone any suggestions on how I pull data from query 2 from within a certain date range. i.e 01/01/2005 to 01/05/2005 Thanks
View Replies !
View Related
Query Date Range
I have a query which requires date parameters, which the user enters into a form. The form enters the parameters into 4 different queries then runs them to produce a report. This all works fine EXCEPT for one query. If I enter my desired date range into the query (in this case it is between 01/11/2004 and 30/04/2005) it returns no results. As the only values in the date fields of the table are 01/03/2005 and 01/04/2005 it should return all the records. However if I enter the date range between 01/01/2005 and 30/04/2005 it works fine. It also works if I enter 01/01/2000 and 31/05/2005 - it just doesn't seem to like the year 2004!!! The problem occurs whether I enter the parameters from the form or simply type them into the criteria of the query. Any ideas, it's driving me nuts!!
View Replies !
View Related
Date Range In Query
Hello all I was wondering if anybody could help me out with a query problem I've been having. I've been trying to use a query to display a list of available cars for a given start and end date entered by the users. I have found some guidance to make an attempt but it isn't working. The text in the Input boxes isn't what I'd like. Also the query is displaying all the cars in my database even when I deliberately trying to exclude some. I'd really appreciate any help As this is my first database and Im really struggling with the use of criteria. I've included a screen grab including my formulas http://img.photobucket.com/albums/v242/b3rnie/access_screen.jpg Apologies if I've missed this advice in my searching
View Replies !
View Related
Date Range Query
I am trying to design a report that uses a range of dates as column headers. The row headers are vehicles and the intersection between the columns and rows will display details about that day's vehicle usage. To do this I need a query that will produce a row of date headers based on parameters from a form. Also, the report needs to display date headers even for days on which no events occur. Any ideas? See also: http://www.access-programmers.co.uk/forums/showthread.php?t=130335
View Replies !
View Related
Query And Date Range
hello I've set up a query which contains among others the following fields ProjectID WorkstreamID MonHoursDate MonHours MonHoursDate and Monhours come from another query where the date is a calculated value. If I now run the query, it works and lists the hours done for that project. If I enter the following expression in the criteria for MonHoursDate Between [Start] And [End] and then run the query and enter start and end dates, I get nothing. Any ideas what I'm doing wrong? The date formats are set to Medium Date and follow the following fomat DD/MM/YYYY Thanks sunil
View Replies !
View Related
Date Range Query
Hello all, I would like to ask for some help. I am trying to make a select Query that will give information based upon a date range the user enters. I know how to make it so the info shows up for a particular date, but for some reason cannot figure out how to make it so it works for a date range. Can someone please help. Thanks
View Replies !
View Related
Query Within A Date Range
I am working on a database of investments. I have a purchase date and a matured date. I would like to create a query where I enter a month and any investment that is active will show. For example if I have an investment that is purchased 1/1 and matures 2/28 and I query February it would show. If the query is for March it wouldn't show. I can't seem to find the answer for this so any help would be appreciated!
View Replies !
View Related
Enter Query Date Range On The Fly
Below is the expression I have in a query. I have a Combo Box on a form that will show all my query's so the one need at the time can be selected. My question is: is there anyway when I select this query to run from the Combo Box on the form that I can input the date range on the fly? The date range will change periodically and I would like to somehow input the date range when running the query from the Combo Box. FORMS: DCount("[Date_of_Change]","all_trucks_table","[FORM #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#") Thanks for all help!
View Replies !
View Related
Getting Date Range In Crosstab Query
My question involves the table below AutoNumber(primary key) SerialNumber Station Defect Type Date Basically now I need to be able to get the result i've gotten by a date range, but the crosstab query won't let me do it the way you can in a regular select query...help.. ------------------Station 1 ----------Station 2------------Station 3---------------Station4 RxFailure ------------52-----------------52 Bit Error Rate 10 -----0------------------15
View Replies !
View Related
Having Trouble With Date Range Query
I am creating a database for a workplace for staff management. i want a query where the user can input a month or a date range and be able to view all of the staff on holiday during this period. I originally had a working query however it ignored staff that were on holiday during this period for example searched 11/2006 showed holidays in november but not staff who are on holiday from before and still on holiday during this time. At this moment in time the fields i have included for beginning the query is BookingID, StaffID, EventName,StartDate,EndDate any help would be appreciated
View Replies !
View Related
Query Criteria - Date Range
Hi. I want to set up a simple query criteria where it searches based upon a column called Date. So I want a criteria that says when you run the query ... Between which date range? And which date? Now I know I need to use Between and And within the Citeria, however I would be grateful for someone to show me the exact wording for the criteria. Thanks and kind regards. Phil Drury.
View Replies !
View Related
Date Range In Crosstab Query
Please help I have a cross tab query which is based on a simple query which gives me a summary of sales (sales by sales rep and sales by job type) is there any way i can put a date range into this? everything I've tried doesn't work. It works in a simple query but i cant summarize the simple query. Thank you in advance.
View Replies !
View Related
Help With A Date Range Query Problem
Hi all, Can someone please help me with a date range query. Basically I have a query that shows delivery dates, from 01/01/2006 to 01/01/2009. I just want to modify the query so it shows all deliveries within the past 21 days and all outstanding deliveries until 2009. Please note that in some cases, the delivery date may have been left blank in the table (as this it maybe unknown). If that is the case, the query criterion needs to pick that up as an outstanding delivery. Can someone help me with this? So far I’ve figured out Between Date() And Date()-"21" Thanks
View Replies !
View Related
Query Date Only For Month
Hi. I want to make a query in a field date (dd/mm/yyyy) but I only want that it appears the month that will be asked for. I have this code: WHERE (((customers.creationDate) Like "*" & [Which Month] & "*")); Thanks
View Replies !
View Related
Query - Records Within A Certain Range
Hello, I've been wrestling with this problem for a while now and any assistance would be greatly appreciated. I have a single table containing flight data. Each record represents a single flight. What I'm attempting to do is count of pairs of flights that come within a certain range of each other. For simplicity sake, let's say that any flights A and flights B passing through Point X within a certain window of time (-15/+15 minutes) of each other are counted as a pair. Each record has a unique numeric key and all the neccessary information. Getting a table of all the possible matches is easy. The tricky part however, is that I only want to count each pair once. Is there any way this can be done with an SQL query? I apologize in advance if this isn't specific enough. I would really appreciate any feedback whatsoever.
View Replies !
View Related
|
TRACKER
