Calulating Months
Sep 14, 2006
Hi,
I am trying to create a query that will show data in a rolling four month period. Example: For September, show June through September; for October, show July through October.
I am using a general date format in the field. I can use >DATE() - 120 to get a 120 day period, but that obviously does not give me data for the actual Calendar dates. Is there a better way?
Any and all suggestions will be greatly appreciated.
Thanks in advance,
Jake
View Replies
ADVERTISEMENT
Mar 22, 2013
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]))
View 2 Replies
View Related
Oct 29, 2007
Hello mates, :cool:
I'm a bit confused with date codes. What I'm try to complish here is that how to add years and months from separate fields to text box, where's difference between two dates as an years and months? So basicly first I read year and month values from text boxes to a integer and then I'm going to add them to somewhere at last, but I'll get like 1 year and 14 months for result so, could you hit me with some tips or tricks what I should try out.
I'll approciate your time to help me out with this and thanks for advance. =P
View 1 Replies
View Related
Sep 15, 2006
Can this be done?
I have a single $ amount and an effective date. I need to take that dollar amount and disperse it out evenly over 12 months. Is it possible to create a query that will create the fields 1/1/2006, 2/1/2006 and enter those amounts?
For example, total savings is $12k and effective date is 2/1/2006. I need to show $1k in each month field in my query from 2/1/2006 through the month of 1/1/2007. I've tried DateSerial and some other methods but I can only get it to give me the divided amount in the effective date column and nothing else. Is DateSerial the way to go?
Thanks,
Toni
View 2 Replies
View Related
Nov 10, 2006
Hello,
I'm trying to calculate months not days between
EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?
:confused:
View 2 Replies
View Related
Feb 27, 2007
How do I write an expression to pull data greater than six months from a user entered date.
View 3 Replies
View Related
Oct 18, 2007
The table I want to run the query on has a date in the format of dd/mm/yyyy. I want to be able to run a query so that the user can type in a month and all records with dates in that month will be shown.
I have done this before many years ago but now have a mental block and cant figure it out.
Any help with this would be greatly appreciated
View 3 Replies
View Related
Mar 27, 2005
Searched and can't find the specific answer to my age/month question so I hope you will bear with me.
on my form I need to calculate someones age and show it in years & months, then display the age (years & months) in the format 7.5 ( i.e. 7 years 6 months displays as 7.5 ).
I also need to round that figure down to the nearest 0.5 of a year so 7.4 would display as 7 and 7.9 would display as 7.5.
Any help would be very gratefully received.
Cheers
RussG
View 2 Replies
View Related
May 23, 2013
1) I am trying to use the same set of data for different months. I will try to explain...For each month I want to mark the data as "Statement Received" and "Statement Reconciled". I don't want to create a brand new database every month with the same data. The database will become massive too quickly. Is there any way that I can set the date by using the filter date combo box I have created on the frm_SearchMulti form and having a fresh set of data for just April. When May comes around I can create a new date and have a fresh set of data, but still reference April data... I hope I am explaining this okay
2) Would it be possible to add a column into the "SearchResults" ListBox for Statement Received and Statement reconciled? I would want to update it through the text boxes I made directly above it. The buttons right now do not do anything... they are just there for formatting purposes. I want want to be able to select multiple record in the listbox and update them all simultaneously
3) How can I make the "New Account" button actually pull up a new account?
View 1 Replies
View Related
Aug 28, 2006
I need to (accurately) calculate a rental period to fractions of a month.
ie) 1/1/2006 to 2/14/2006 would equal 1.5 months.
DateDiff("m",[Start],[End]) returns whole months.
DateDiff("d",[Start],[End])/30 is very close , but not precise.
Is there a way to accurately calculate fractions of a month?
Thanks,
Sup
View 14 Replies
View Related
Jun 7, 2005
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
View 2 Replies
View Related
Jun 28, 2005
Is there something I can put into the criteria of a date box to only use the last 6 months. I dont want have have to do this type of criteria: >12/1/2004. I want it to just use information from the last 6 months no matter what the current month is. If anyone knows what I am trying to say, please help me out! Thanks
Bowes
View 4 Replies
View Related
Dec 12, 2005
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database
Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
On Error GoTo Err_Handler
If IsNull(StartDate) Then
getTimeElapsed = "No startdate"
Exit Function
End If
Dim YY As Integer
Dim MM As Integer
Dim DD As Long
' use current day if EndDate is not supplied,
' + 1 makes both days inclusive.
EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate)
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
End If
YY = MM 12 ' integer division.
MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
MM & IIf(MM < 2, " month ", " months ") & _
DD & IIf(DD < 2, " day", " days")
Exit Function
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
View 1 Replies
View Related
Feb 15, 2006
I have been through this board looking for an answer, I've come close but not quite getting it.:confused:
I (obviously) need to count how many records each user has completed (easy)
I need to count how many they did in a months time. I need to report multiple months in one report.
A little back ground on me...I only know the Query Design view and it's pretty limited. I have tried to move into the SQL view and alter the information there but it's not working in my favor. I also tried changing the format in the date properties to mm/yy but it's still sorting by the full date and time. :mad:
Any help would be awesome. Here's a copy of the SQL statement if it's easier to just change it there. Thanks in advance for the help.
SELECT Count(qry_QuotesIssued.Quote_Number) AS CountOfQuote_Number, qry_QuotesIssued.FName, qry_QuotesIssued.Creation_Date
FROM qry_QuotesIssued
GROUP BY qry_QuotesIssued.FName, qry_QuotesIssued.Creation_Date;
View 2 Replies
View Related
Sep 30, 2006
I have a table which lists rentals, all have start/end dates and a rental fee p.a.
I want to query this table to show rentals that are effective for any part of 2 dates, [Enter Start Date] and [Enter End Date]. The purpose of this query is to calculate rental income between these 2 selected dates.
Now I have this much working, but I want to group all the figures by month, in order to produce a graph to view the rental income by months.... this bit has me stuck....:confused:
How can I group all these records, when I only have 2 dates... for eg, start 01-01-06 and end 31-07-06...
I want to see figures for Jan06, Feb06, Mar06...July06etc... rather than just a single total figure for the entire period...
Any ideas?
Help! I'm completely stuck here and have spent hours searching the internet but haven't found anything suitable...
View 10 Replies
View Related
Oct 9, 2006
Hi
I have a table with a column which has dates in it.
Eg
Column a
01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005
I need to count how many records are in each month
Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc
I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.
Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!
TA
IAIN
View 14 Replies
View Related
Oct 9, 2006
Hi
I have a table with a column which has dates in it.
Eg
Column a
01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005
I need to count how many records are in each month
Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc
I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.
Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!
TA
IAIN
View 2 Replies
View Related
Mar 19, 2007
Hey
I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating
View 14 Replies
View Related
Dec 3, 2007
I have been assigned a task to create a query that gets record from Access database by grouping it by year and then by months.
DATA ILLUSTRATION
date incidents
01/01/2007 2
01/02/2007 3
01/03/2007 4
01/01/2008 1
20/01/2008 1
01/02/2008 1
Based on the above data illustration, what I am trying to do is to get total by year and then break it down by the months
year 2007 total_incident: 9
Jan total_incident: 2
feb total_incident:3
mar total_incident: 4
year 2008 total_incident:3
jan total_incident:2
Feb total_incident: 1
I am using Microsoft Access, but trying to get the result into a web form. Many thanks in advance
View 2 Replies
View Related
Mar 12, 2008
Can't think how I would do this. If I create a query with a certain date then it will need to be changed every time the query is run. How would I write a query that automatically looks for a date 3 months or more old.
Thanks for any help. :D
View 2 Replies
View Related
Mar 16, 2006
I have a series of subforms that show customer mix percentages by month. These forms will be used for data entry and to ease the process I would like to have months in order of our fiscal calendar, July through June. Right now the months are listed alphabetically. Is there a way I can list the months in order without having to resort to a naming convention such as:
01June
02August
03September
ect.
Thanks
View 1 Replies
View Related
Feb 8, 2005
I need a formula or a function that will return a person's age in years and months. All the functions and formulas that I have seen to date and that I have used only calculate a person's age in years. Can anyone help?
View 2 Replies
View Related
Jan 5, 2007
Hi,
i have a displayed(textbox) birthdate (txtDoB) and a textbox "input_date" (txtInputDate) inside form "form Two".
how do i calculate the years and months difference upon clicking the "OK" command button at "form Two"?
the result of the years and months will be displayed at a textbox (txtResult) at a form called "form Result".
thank you.
View 2 Replies
View Related
Dec 17, 2012
I have a sum (i.e 50,000) want distribute it evenly base on period of months entered.and to show the months.
View 3 Replies
View Related
Nov 29, 2005
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 Related
Apr 28, 2006
I want to convert my dates to months so I added the statement below in the Field section of my query. It is not working. What am doing wrong?
Month:IIf([dateActivity]>=7/1/2004,IIf([dateActivity]>=8/1/2004,IIf([dateActivity]>=9/1/2004,IIf([dateActivity]>=10/1/2004,IIf([dateActivity]>=11/1/2004,IIf([dateActivity]>=12/1/2004,IIf([dateActivity]>=1/1/2005,IIf([dateActivity]>=2/1/2005,IIf([dateActivity]>=3/1/2005,IIf([dateActivity]>=4/1/2005,IIf([dateActivity]>=5/1/2005,IIf([dateActivity]<=6/30/2005,”June”,”May”),”April”),”March”),”February”),” January”),”December”),”November”),”October”),”Sept ember”),”August”),”July”))
Thank you.
View 5 Replies
View Related
