Queries :: Subtract 1 Month From A Date
Mar 25, 2015
My [datefield1] is formatted as dd/mm/yyyy
I wish to create a query that produces a return of the previous month and year. i.e.
25/03/2015 (though a query formula) becomes Feb-15
01/01/2000 would return DEC-99
etc
This way I can then link all of my February data (formatted "mmm-yy" to my [datefield1]
I have tried subtracting day and formatting to months and subtracting then the value of a month but it all becomes too complicated because of the different days in the months and Jan to Dec and year as well.
View Replies
ADVERTISEMENT
Jul 14, 2014
I have a table contains
1. full name
2. email
3. date
I want to make a query that will contain
1. full name
2. email
3. date1
4. date2
The date2 will be date1 + 90 days
So if the result is a sunday i want to add one day or subtract one day automatically...
View 7 Replies
View Related
Jan 14, 2008
I have a report based on a query where I get results sorted by month after i have chosen 'from date' and 'to date' in the beginning. I also want a set number for each month to be subtracted from the total sum, and then added to the next month (even if it is a negative number). Ex. Total in january is 100, then it is supposed to subtract 50 from that number, report the result of 50 in one own field at the end of january, then send this to february where the total sum is ex. 200, then the total sum for february is supposed to be 250, and then again a different number, like 60 is automaticly subtracted from the total sum and displayed in one field as the result 190. And so on.. Does anyone know if this is possible, and how? I am a noob, both in access and in the english language :P
View 1 Replies
View Related
Apr 20, 2015
I've two fields to work with:
[Date of Device]
[DischDate]
If i was explaining it, it would be as follows:
If [DischDate] Is in the next month after [Date of Device] then Y else N.
to add for example if the [Date of Device] is April 2015, and the [DischDate] is also April then i'd expect a N answer
to add for example if the [Date of Device] is April 2015, and the [DischDate] is May then i'd expect a Y answer
View 2 Replies
View Related
Sep 16, 2014
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10.
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
View 2 Replies
View Related
Jul 11, 2013
I am trying to query any date before current month. My data is employee start dates, thus my end aim is to pick up all employees at end of last month.
View 1 Replies
View Related
Jun 9, 2015
I have two date columns in my table called "End date" and "Closing date".
An example could be 14-06-2015 and 13-04-2017.
I need to make a query which is checking if the two dates are equal to the last day of their respective month. I don't have two columns in the table with the last day of month, so I first need to find out what the last day in the month is.
View 2 Replies
View Related
Feb 5, 2014
How do you calculate or find Month To Date in a query as it relates to a hand keyed criteria.
For example I have a field called Operating Day and right now my criteria in my query is Between DateSerial(Year(Date()),Month(Date()),1) And Date(). Works perfectly. I'm using this data in a sub report.
But now my requirements have changed and its possible that I may need to report on something from January (or December and so forth). Well this criteria will show data from February. But the Month To Date data should show totals for the Month I'm reporting on.
Is this even possible?
View 4 Replies
View Related
Jul 18, 2013
Using Access 2000
I have a field which lists a date. I would like to determine which financial month the date is.
However the financial month starts and ends at odd times, for example
The start time of the month is the first Sunday after the last Friday of each month. With the end date being the day before this.
So for July it would have been
30/06/2013 - 03/08/2013
and August is
04/08/2013 - 31/08/2013
I would like the output to show something like "July 2013"
How to do this?
View 4 Replies
View Related
May 16, 2013
Is there a way to take today's date and calculate the last day of the last month? Without the user needing to enter any parameters. In other words, if I ran the query today with this criteria, it would only show information for April 30, 2013. Is that possible?
View 3 Replies
View Related
Feb 8, 2015
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
View 11 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
Jun 11, 2013
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies
View Related
Apr 16, 2015
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
View 2 Replies
View Related
Sep 13, 2013
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
View 1 Replies
View Related
Jul 22, 2014
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
View 3 Replies
View Related
Oct 31, 2012
Any way of setting up a table containing the following date/time fields.
StartDate
StartTime
EndDate
EndTime
Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.
In either scenerio, how do I subtract the two in a query for a report?
View 1 Replies
View Related
Mar 29, 2013
I having a problem with Access
I have one row in table, for example:
km_counter
18000
18100
18300
18800
and when I run a query, I want to get:
km_counter km_diff
18000
18100_____ 100
18300_____ 200
18800_____ 500
is it possible to do that?
View 3 Replies
View Related
Mar 6, 2014
I have two date fields. I want to subtract them to determine the number of days elapsed. The dates are in the following format: Dateserial (Year, Month, Day). When I subtract them I get 7670 as the result when the number should be 365 if they are a year apart. How can I subtract the dates in a query?
View 3 Replies
View Related
Jul 22, 2014
I have two calculated columns in my query for example... table.num1 - table.num2 as "Col1" and table.num1 - table.num3 as "Col2"
I then want another column equal to:
Col1 - Col2
I know that I could say:
(table.num1 - table.num2) - (table.num1 - table.num3)
but I am wondering if there is an easy way to just take two calculated columns of the query to use in the calculation of another column.
doing something like:
Col3: Col1 - Col2
doesnt work because it doesnt see Col1 and Col2 within the tables.
View 7 Replies
View Related
Jul 23, 2014
ACCESS 2007
I've got a table in which some records are associated with a number (numeric field): 691, 692, 693 etc. My parameter query asks for the number I'm looking for (parameter) and should return all records associated with that number or with that number minus one.
Something like this:
enter parameter
find records
in which number = parameter
OR
in which number = (parameter - 1)
How can I do that?
View 4 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Sep 5, 2014
I have the following source data:
Code:
Item Revision Start End
332 B 9/2/11 9/15/11
332 C 9/21/11 9/22/11
332 E 11/2/12 11/29/12
2A9 A 1/13/13 1/14/13
2A9 C 1/16/13 1/18/13
I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):
Code:
Item Revision Span
332 C 6
332 E 407
2A9 C 2
I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?
View 4 Replies
View Related
