Date Query (last Week, Last 2 Weeks, Last Month, Last Year, All)
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.
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.
I am trying, and getting nowhere...so would appreciate your help... to create a query where records with dates from 8 weeks, or two months ahead appear in the query.
That is I have contracts ending in two months in my table and I want to run a query on who I should call now to renew contracts.
I have a contract end date in table.
I have tried >=DateAdd("m",2,Now())<=DateAdd("m",2,Now()) only to get all sorts?
>=DateAdd("d",60,Now())<=DateAdd("d",68,Now()) only to get all sorts of things appearing?
Anyone that could help would be great. I have contact info in a TBLCONTACTS and linked to TBLMOBILES via COMPANY_ID
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
I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.
How can I get Access to sort in ascending order correctly on the formatted date?
I have found multiple ways of calculating the week of a month for a given date. Now, I want to reverse it, i.e. given a month and week and day of week calculate the date.
Note that in week 1 and last week, there will often be days with no value.
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 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 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.
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"
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
I analyse things by the date they are ordered by the office.
The date gets put into a spreadsheet and i import this into access for the querying.
I need to summarise it by weeknumber and year.
I use the following queries:
To get the week: Week Number: DatePart("ww",[DateOrdered],0,2) To get the year: Year: Year([DateOrdered]) To put it together: Weekyear: [Year] & "_" & [Week Number]
The problem is when the year changes.
The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.
Therefore when the year and week number is put together the following results are given for that particular week:
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.
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?
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
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...
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
However, since the week beginning 12/26/05 crosses over both '05 and '06 (as week 53 and week 1), I get two groups of dates recorded for the week of 12/26/05 when I run my query.
Since my chart is based on weekly totals, I can't have two separate totals with the same 'week beginning' date.
How can I adjust my code to get one total group of dates for the week of 12/26/05?
