i need 2 comboxes. 1 with year and 1 with month. the year is easy just do this in rowsource sql:
SELECT DISTINCT (DatePart('yyyy',uitvoering.begindatumtijd)) AS Jaar
FROM uitvoering;
but the month is a lot harder because it needs to show the months where the year is the same as the combox with year and only show months where there is data in my table. for example i have data for only 3/12 months in a year then i need to show only those 3 months.i made this in vba because i think it can't be done in rowsource with sql code.
Private Sub Tekst19_Click()
Dim year As Integer
year = Me.Tekst17.Value
CurrentProject.Connection.Execute "SELECT DISTINCT (DatePart('m',uitvoering.begindatumtijd,,)) AS Maand FROM uitvoering WHERE (DatePart('YYYY', uitvoering.begindatumtijd)) = year"
End Sub
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 !
In words, read_date would list dates for the current year. Second column of query would give read_value for current year. Third column would give read_value for same day/month of previous year.
Have thought about using Dlookup to get value one year back, but not sure if is the best approach?
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..
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???
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!
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.
I am trying to create a query which will return records which are from a specific month of the previous year...
To better explain:
If I have sales from 2013 and 2014 in Jan, I want a query which will show me the results from Jan in 2013 only... I'm just unsure on how to make a query do this and it's probably really simple.
But then in 2015 I will want it to show results from 2014 without having to change the expression or criteria.
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.
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.
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?
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.
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.
I'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.
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;
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'm setting up some analytical reports and i'm having a hard time with some date criteria.
I've gotten monthly averages already and what i want to do is take those monthly averages, and average them throughout the year. (the year being from the specified date on a form control back one year)
Month([Forms]![Report Runner]![Date1]) and Year([Forms]![Report Runner]![Date1]) worked great for specifying a certain month in a certain year, but what i'd like is to show all the monthly numbers for every month going back one from [Forms]![ReportRunner]![Date1].
I tried this:
Code: >(Year([Forms]![Report Runner]![Date1])) And <((Year([Forms]![Report Runner]![Date1]))-1)
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?
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.
