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
I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.
View 1 Replies View RelatedHow to code the rolling 12 months in a combo box?
(1) When I click on the combo box this month, the first value should be 2015-07 (yyyy-mm) and the last one should be 2016-06
(2) When I click on the combo box next month, the first value should be 2015-08 (yyyy-mm) and the last one should be 2016-07.
The combo box will check today's month and will automatically generate the 12 rolling months (include this month).
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
Good morning all,
I currently have the following expression in a query, to go back 6 months from the current date.
Between DateAdd("m",-6,Date()) And Date()
Does anyone know of a way from the current date, go back to the end of the previous month ( 30th, or 31st) then go back 6 months?
hope that made sense.
Thanks
Kevin
Not sure if this is actually done with a query (hell, im not sure if it can be done) as my Access knowledge is limited but I've got a list of Patient Appointment Dates and I need to calculate a list of 'Next Appointment' dates that are six months on from the appointment dates not including saturdays and sundays (as the health center is closed those days)
BTW this is for coursework, I don't really work for a health center :S
edit: I seem to have put this in the tables forum, not the queries forum. Apologies for that, I'm not an Idiot, Honest!
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 12 Replies View RelatedIm looking for something that filters information for 6 months prior to any current, non-specific date. Like it will automatically get the data dated from 6 months ago from june, july, or august, without anyone having to go in an change the date criteria.
Below is a non working example of what I am trying to come up with. If I could get something like this, it would save me a ton of time going back and updating all the date criteria for my databases.
Forgot to post it:
>=Now()-Day(180)
I have a field called DATE_END_DEERS which is in a format of YYYYMMDD (20060530). I need to run a query that shows 6 months subtracted from this date. I can never get an answer that is even close. Can someone please help? ACCESS2000.
View 3 Replies View RelatedHello Everyone,I have a query where I am trying to pull records where the user took a class that is 2.5 years old or newer based on their license expiration date. Can someone tell me what I'm missing/doing wrong?SELECT tblCE_Details.LIC_NUMBER, tblCE_Details.Course_Code, tblCE_Details.Course_Date, sociwork1.expirat_dtFROM Sociwork1 INNER JOIN tblCE_Details ON Sociwork1.LIC_NUMBER = tblCE_Details.LIC_NUMBERWHERE (tblCE_Details.LIC_NUMBER = Forms![SW form]!SLIC_NUMBER) AND tblCE_Details.Course_Date >= (sociwork1.expirat_dt - Month(30))ORDER BY tblCE_Details.Course_Date;Thanks,Crhodus
View 3 Replies View RelatedIf I key in to the control source of a text box "=Date()-184" I will get the date 6 months ago but if I tried the same of 06/10/05 I will lose a day because of the 30/31 day months. Does anybody know another way around this?
View 6 Replies View RelatedWe have a field (tbl_Enrolment.CourseCompletedOn) which holds the date a colleague completed a course. I want to bring through all courses completed within 12 months of todays date ( Date() ) from our database. I checked google and it suggested YEAR but that would only bring through dates with the same year, not within a 12 month period.
View 4 Replies View RelatedI have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.
Thanks
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 2 Replies View RelatedHi all,
I need to be able to add x months to a given date which I'm using in a criteria expression. I've figured out that I can just add y number of days, but the answers aren't quite accurate across different ranges of months.
I'm after something like
<[BeginDate] And >([BeginDate]+[3months])
but I haven't turned up anything useful in an hour of googling - finding it difficult to define what I want in search engine terms.
Cheers,
Alex
Hello,
I have two fields in a table, both containing dates. I would like to write a query that returns the difference between the dates in an year, month, day format (i.e. 26y 2m 3d). If that isnt possible, can the difference be returned as years,days (i.e. 26y 63d).
Thankyou.
I am importing data to a program that requires the life of an item. This is in months such as 241 months. The valid format for this program is YYMM. 241 = 2001 or 20 years 01 month.
Another example: 16 months should read 0104 or one year 4 months.
Any idea how to convert this to yymm format based on the number of months?
thanks in advance.
Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.
Source Data (record) examples:
Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650
User Query #1:
Notes:
ReportStartDate will always be entered as the first day of the month;
ReportEndDate will always be entered as the last day of the month
The report will always show 6 consecutive months
User selects
ReportStartDate = 7/1/12;
ReportEndDate = 12/31/12
Notable conditions:
-The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
-The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
-The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
I have a table of data which includes a date field and also various other fields which may or may not be filled with data.I'm trying to populate a listbox with the months for which this data is missing (a separate macro will then loop through these months to fill the missing data) But I only want each distinct month to populate the listbox - not each individual date.
Code:
strSQL = "SELECT DISTINCT month([EntryDate]), year([EntryDate]) FROM [SampleTable] WHERE [ValueField] Is Null"
Me.lstSampleListBox.RowSource = strSQL
I want to return the month in <mmm yyyy> format.
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:
I have designed a query with the following fields.
Date;
NumberofDaystoCallBack;
CallBackDate: [Date]+[NumberofDaystoCallBack]
I have created a formated field called DateToCallBack, which is the calculated field.
For example when I enter say today's date 03/11/07 in the Date field
and enter 10 as the number of days to call back in, the calculation field
works just fine and gives me the correct call back date which in this example
would be 13/11/07.
The problem I am having is,
I am trying to set a criteria on the calculated field for a
Between [Start Date] And [End Date], but when I run the query it gives me
all the day dates in the range I specified properly, but it gives me that in every month, when I only wanted one specific month.
For example,
I select between 1 Nov 07 and 8 Nov 07
It gives me all the 1st to the 8th of every month, when really I only wanted Nov.
Any help or suggestions? :confused:
Hi,
this is my first question in this site.
I have a database with the following fileds and associated values
areacode dt rainfall
---------------------------
AAA 1/1/2005 10
AAA 2/1/2005 4
AAA 6
. 0
.
.
.
AAA 31/12/2005
SOME BODY HELP ME IN FINDING OUT ROLLING SUM OF RAINFALL
FOR THE WHOLE YEAR ,FOR EVERY FIVE DAYS.
To put it simple ,consider the following data
rainfall rollingsum
1 15
2 20
3 25
4
5
6
7
Also ,i would apprecite on tips how to address the fact that a leap year has 366 days in the above scenario.
Is there any way i can calculate a rolling average for a field in a record, based on the 10 previous records?
Cheers,
Ben
is it possible to have a query carry out a running total, so that it counts the record above and the current entry.....
example
Record 1 Total 10 Running total 10
Record 2 Total 10 Running total 20
Record 3 Total 30 Running total 50
Record 4 Total 5 Running total 55
as i said i need this done in a query if Possible...
anyone help?
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?
I have a table comprised of/grouped by agency, payer source and billing date summing dollars the order/display of the data is the same as the grouping; that is, ordered by agency, payer source, and billing date. The billing date is just every Tuesday of every week. Every once in a while, there is a negative value in terms of dollars for a particular agency/payer source/billing date category. When this happens, a voucher isn't created and in billings that follow that 'rolling negative' amount must be accounted for. Is there a way in which I can produce a value on the line that adds the rolling negatives from prior weeks if they exist? An example is below:
Agency Payer Source Date of Billing Dollars Amt to Release
ABC Patient 1/28/13 $500 $500 *full amt releases because no rolling negatives
ABC Patient 2/04/13 $600 $600 *full amt releases because no rolling negatives
ABC Patient 2/11/13 $-200 *nothing released because negative amt
ABC Patient 2/19/13 $300 $100 *$300 - $200 released because a negative occurred in prior week