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
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..
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:
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!
I have another...
How do I group the dates on my query to show only a certain month and year?
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
I have tried to do this using DateAdd but I cant get it to add both the month and year.
I have a date field [HireDate] and I need to add one year then get then get the first day of the next month.
For example: HireDate = 1/15/2012 - I need access to return 2/1/2013.
how to build that expression?
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!
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
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
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
I have a reports form where the user can enter enter a from date and to date for the report criteria. I have an unbound field on the report which i want to display the month and maybe the year in which this date criteria is in. This will be needed to display on the actual form. Say if the date range is between 01/01/05 and 31/01/05. I need the field to say January 05.
Hope this makes sense and can be done
I have a field that is:
Current length of continuous allocation of Care Co-ordinator. ( years and months )
Ideally I would like to have two drop down menus - one with years and one with months.
Would I have to have two fields in my table - one for years and one for months or can I have one?
Whats the best way?
Thanks.
On my form day/month/year has switched to year/month/day. On a table there are three separate date cells. Order Date, Shipping Date, and Report Date. They are each formated to medium style. At first I also used an input mask then I found that Access would convert to the format I choose. I had missed one of the input masks during the removal and when I removed the last input mask Access now converts to yy/mmm/dd. What just happened and how do I reverse this? It is more convienant for some of the users to not have the input mask but the order has to be uniform.
View 3 Replies View RelatedI'd like the user to be able to search a subform only by month and year. We have lots of reports with different days, but none of them are pertinent to searching.
I don't mind if it looks a little bit messy (such as using the calendar drop down and it discounting the day) but I'd like to have it all in one box, preferably where the user can just select ex. 05/2013 and it would filter the subform, showing only reports from that month.
I have a query that I need to total by each month of the year. Field names and types are as follows
ID (PK)
Month (Date/Time)
PoliceResponse (1 or 0)
TerminalCheckTrue (1 or 0)
RampCheckTrue (1 or 0)
AOACheckTrue (1 or 0)
TotalTerminalRampAOA (TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
TotalChecks (PoliceResponse+TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
Now, I need to just get the total of each of these fields per month for the entire year. So it would look like
Jan, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Feb, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Mar, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Below is a sample from the query. I guess Im just not sure where to start since there are multiple months the same value, but i need them to total up..
Code:
SELECT DISTINCTROW tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks
FROM tblBlotter
GROUP BY tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;
I try this code
Code:
SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot,
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg,
Round(Avg(ReportTbl.avg_len),2) AS LenAvg,
Round(Avg(ReportTbl.avg_in),2) AS InAvg,
Round(Avg(ReportTbl.avg_out),2) AS OutAvg,
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg,
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY ReportTbl.lot_date;
But it not working and have warning box with "you tried to execute a query that does not include the specified expression 'ReportTbl.lot_date' as part of an aggregate function"
and when I try
GROUP BY Month(ReportTbl.lot_date),Year(ReportTbl.lot_date) or
GROUP BY datepart("m",ReportTbl.lot_date),datepart("yyyy",R eportTbl.lot_date)
or other code that I can search in google
I get the warning message that resemble with above message
and when I try to GROUP BY ReportTbl.lot_date it work but the result does not meet the requirements.
I am working on an access database for my team. We keep and maintain a trail log for different departments.
The person will send a trail sheet to us we will input the info and attach what we call Production Instructions Number in this format (YEAR-Month-001), the next trail will get (YEAR-Month-002), the last 3 digits will start over when the month changes, I would like the PI number to populate automatically when the info is inserted.
I've looked and looked and am afraid that I didn't find this because it's not possible...
The forms in my db are not the clearest, but one way of simplifying it for the users is to have them click on the calendar icon and then have a few fields automatically populate with the selected date's month, another with the year, and another with the day of the week.
Is this possible? I figured out how to have a field populated with the date, but with all of the specific queries needed, I have to do it this way...
Any thoughts? Also, is it at all possible (this is totally separate) to have an icon on the desktop that brings up a login sheet for which the entry users only see the front end, but the db admins log in and see the back end?
Thanks in advance!!
E
Hiya
This is my first post here, but I've two questions so I'll divide it into two threads.
Normally I can google these to help find the answers but a bit of a loss at this one, probably beginners stuff to some of you.
Heres a link to the database below
http://casp.gamecommunity.co.uk/database.gif
Total count is easy: =Count(tblMain!ID1)
What I want to do is count the amount of records for each individual month of the current year so I don't have to change the year date everytime a new year comes round. As soon as the next year comes around they all reset to 0.
The clever way would be to count the records for the current month -1 current month -2 etc and automatically update the month labels but I think that would be getting a bit comlicated.
Thanks in advance for any help. :)
I have a database where all reports are by month. I want a simple way to allow the user to select the month/year and want to know if I can use the header portion of the calandar control for this (although I can't seem to figure that out). The alternative is to use two combo boxes (Month and Year) and in a hidden text box I concatenate them into a date since that is how the report query reads the month (using datediff). I would prefer the calandar control because it is more visually pleasing. If anyone has any ideas or suggestions please !
View 1 Replies View RelatedI 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.
I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy.
This works well as Access only wants one variable in the column header. My problem is that it is not sorted nicely and with my data being between Aug 2013 and Jun 2014 the headers look like this.
1/2014,10/2013,11/2013,12/2013,2/2014, not a very nice order.
Is there a way I can reformat MTHYR or a completely different solution?
My current code is
Code:
TRANSFORM Count(VOR_CALD_REGION.[Case Number]) AS [CountOfCase Number]
SELECT VOR_CALD_REGION.[Dealer Name], Count(VOR_CALD_REGION.Case Number]) AS [Total Of Case Number]
FROM VOR_CALD_REGION
GROUP BY VOR_CALD_REGION.[Dealer Name]
PIVOT VOR_CALD_REGION.MTHYR;
I have a report filter that filters the reports by month and year:
Code:
DoCmd.OpenReport "AttWholeCity", acPreview, , " Month = '" & txtCourseDateMonth & "' AND Year = '" & txtCourseDateYear & "'"
I want the code to also show these two columns where there are null/blank values aswell, for example if I filter by apr 2015 i want the report to show these columns as well as blank columns is this possible
I have a table that has a field for month represented by a number. I would like to report by month but group by year. The report would group months 1-12 into year 1, months 13-24 into year 2, months 25-36 into year3, etc..... up to possible 20 years. Is there an easy way to identify and group the year? I was thinking of adding another column to the table and when I write the values to it include a year. I would rather do it some other way as the data is spilt and would require a lot of table modifications to distributed db. It would be easier to do it with a query or some code in the front end.
View 2 Replies View Relatedhow to search records by month and year.
example i will have form that contain combobox that will list Jan-Dec.
and also unbound box for user to enter the year.
then, it will have search button. after the user click search button, it will show report of the selected month and year.