Grouping Date By Year And Month In ADP+SQL
Feb 19, 2007
Hi all.
I have chart in ADP project/MS SQL Express and I want to see in chart table (date Field, data field) grouped by year and month: 2005.01, 2005.02, 2005.03....
I try this str = DATENAME(yyyy,date)+ DATENAME(m,date), SUM(data) FROM table GROUP BY DATENAME(yyyy,date)+ DATENAME(m,date) ORDER BY DATENAME(yyyy,date)+ DATENAME(m,date)
problem is with sorting: 2005April, 2005februar,2005,Januar - its sorting not by date, but alpabhetic.
Please, help with other grouping way!
View Replies
ADVERTISEMENT
Jul 22, 2007
I have another...
How do I group the dates on my query to show only a certain month and year?
View 2 Replies
View Related
Sep 16, 2004
I have a Date field, (an Expiry Date) and I want to use this in a Report that with Group the Report into Months. So we know, such and such a month, these are set to expire. Rather than have a separate Month column... how can I make use of exisiting Date in expiry field. Some one must know... Thanks in advance.
View 1 Replies
View Related
Oct 20, 2005
I need a subgrouping for a report I am working on.
I have a DateIn field that contains date as mm/dd/yy. I have managed to get a count of all of a certain type of document that came in during a certain period of time that usally spans a quarterly period. So typically there are three months in the query. I would like a subgroup that breaks the numbers up in the 3 month period by each month.
So if a total of 150 docs moved in 3 months it shows 50 moved the first month, 50 the second, etc. I believe this is grouping by month. I have not been able to determine the criteria for getting the query to group by a single month as well as the three months period being grouped by document type. Here is what I have so far.
SELECT ctblDocuments.DocTypeID, tlkpDocTypeList.DocType, Count(ctblDocuments.DateIn) AS CountOfDateIn
FROM ctblDocuments INNER JOIN tlkpDocTypeList ON ctblDocuments.DocTypeID = tlkpDocTypeList.DocTypeID
WHERE (((ctblDocuments.DateIn) Between #7/1/2005# And #9/30/2005#))
GROUP BY ctblDocuments.DocTypeID, tlkpDocTypeList.DocType
ORDER BY tlkpDocTypeList.DocType;
View 5 Replies
View Related
Dec 18, 2005
can i change date format that contain day, month, and year to month and year only..
i try change at fromat at porperties, but it change back into dd/mm/yy at combo box..
this is bcoz i want to filter up my subform that contain parts that purchased by customer by month..
thanks..
View 3 Replies
View Related
Oct 9, 2005
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
View 9 Replies
View Related
Apr 19, 2007
Hey guys.... stucked in one thing..... I need to extract Month and Year together from a date in a Query. for example if date is 4/19/2007 i want to put this as April 2007.
Any idea ...?
thanks
View 5 Replies
View Related
Aug 16, 2005
hi friends,
I want to get year and month field of datetime field so that i can get data by comparing them with other table fields.
ex: date field column contains 1/28/2005 as data
how to get year(1/28/2005)
how to get month(1/28/2005)
expecting your help
Thanks,
Spec
View 3 Replies
View Related
Aug 20, 2007
I have a table a
year month day
2005 1 1
2005 3 1
2006 5 1
I want to generate the associateed
1/1/2005
3/1/2005
5/1/2005
which funciton I can use to do that?
Jeff
View 3 Replies
View Related
Oct 22, 2006
I have a need to enter dates so that they always appear as the first of the month. These dates are used in vlookup functions in Excel and need to be constantly recorded as eg 1 Nov 06. Currently I have to rely on notes and training to make sure users only insert the date in this way.
What I would like to do is get them to enter Nov 06 only (without a day) and have that stored as 1 nov 06. I have searched for date formatting functions and nothing addresses this. Perhaps it is something that could be achieved using VB if it is possible to do it.
Thanks for the help.
View 4 Replies
View Related
Apr 7, 2015
I need to use VBA to get a string which is essentially just a month and year (so date, not including day)
Would Prefer it to be 04/15 as opposed to April-15, as the number is easier to sort than the word (how to sort month names in numerical order as opposed to alphabetical id).
So that I assume i could do by =format(now(), mm/yy) (though havent actually tested it)
However to add to the complexity id also need to add a month to it, so for instance todays date is 08/04/15, id need the string to read "05/15"
or if the date was 10/12/15 id need the string to read "01/16"
View 3 Replies
View Related
May 31, 2007
Hi
Would be great if I could get some help on this:
I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:
It somhow need to be check what the date is today and then caclulate back.
Any help on this would be much appreciated.:D
Thanks
Daniel
View 6 Replies
View Related
Jul 13, 2015
I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:
The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.
In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by SQL statement into a string. But this also didn't work, the types were different.
Code:
UPDATE tblSAPOD
SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]<
DateSerial(Year([sapOD_OrderDate]),6,16),1,0)
WHERE sapOD_OrderDate Like "*/*/94";
.. but I guess it's wrong, it gives me a syntax error.
View 11 Replies
View Related
Apr 3, 2013
My question is that I wish to have a query that looks up on a name in a form (No problem got this bit) and also the Date field from the same form so that from the date :-
example 24/03/2013 only cares about the Month and Year so would look up only March in 2013
The end result would be to supply me with a number (using count) of how many times this persons name has occurred within the calendar month and year of the date supplied in the form
View 1 Replies
View Related
Aug 17, 2015
I have 2 columns that are listed as such:
AssumptionMo AssumptionYr
MAY 2014
JUN 2015
JUL 2015
OCT 2016
I need to create a field called AssumpDate that converts the month into a date field on the 1st day of the month. ex May 2014 needs to read 5/1/2014. When I use the expression AssumpDate: DateValue("1-" & [Assumption_Month] & "-" & Year(Date())) of course the year changes to the current one--2015. How can I I change the expression so that the year is based on the AssumptionYr column?
View 3 Replies
View Related
Oct 16, 2013
How can I set Month or Year default as per the system date. ie Only select records by Month (October) or Year (2013) as per system date?
View 3 Replies
View Related
Mar 5, 2013
I need to put a date field in that is the month and year of the reporting period...
for example
February 2013
and then another date field that is the 10th day of the month following the reporting period.
example: March 10, 2013
I have tried a number of different formats and can't get it right.
View 4 Replies
View Related
Jul 18, 2005
Hi,
Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?
I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...
Any help would be much appreciated!
View 2 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
Oct 27, 2005
This is probably an easy question for you all:
I want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.
I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?
Thanks very much
View 1 Replies
View Related
Apr 25, 2006
I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.
What do you suggest? Many thanks.
Mehran
View 7 Replies
View Related
Apr 25, 2014
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have been trying to tweak this:
SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))
but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"
View 2 Replies
View Related
Apr 5, 2006
I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:
Format([date],"mmm - yy")
And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.
How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???
:confused: :confused:
View 1 Replies
View Related
May 1, 2006
For the record, I have indeed searched the forum for help with this, but no luck so far, so here goes. (C++ programmer, little background in access).
I have a table that stores a person's name, gross amount spent, gst spent, pst spent, and total amount spent.
I need to be able to create a query or something that asks me to enter a year, and it will sum up the amounts, gst, and pst over the course of that year, and print it neatly on screen.
Also, I need to be able to create a similar query that asks for a year and a month (does access possess Java's ability to easily create a pop-up calendar applet-like instance that provides a GUI for selecting months/years, or does the data need to be entered through a command prompt?) which will create a month-end report in the same manner, but only spans a 1 month period?
Any information that could be provided to me, even a means of getting started on this problem, would be great. Thank you and cheers everyone!
View 2 Replies
View Related
Aug 28, 2006
if i have a table like this:
4/3/2006
4/3/2007
4/12/2006
4/12/2008
5/2/2003
5/5/2003
i would like to extract only the distinct month and year date, so my query should return
4/12/2006
4/3/2007
4/12/2008
5/5/2003
can anyone plz help me with the sql query? thanks
View 2 Replies
View Related
Aug 14, 2007
Hey all, I have a form that has two textboxes, one box is for month and the other is for the year. After I enter the month and year I want, I click on a command button that previews a report. Well, the report is not giving me the information for that particular month and year, actually it gives me nothing.
The report gets its information from a query. In that queries criteria field is the below expression that I have.
Like [forms]![frmPEL]![cboMonth] & "/*/" & Right([forms]![frmPEL]![txtYear],2)
Can anyone see anything wrong with this? I should be able to enter the month and year and click on the report button I want and preview that report, but no joy.
Your help is greatly appreciated.
RR
View 5 Replies
View Related
