Queries :: How To Determine Which Financial Month The Date Is
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 Replies
ADVERTISEMENT
Jun 16, 2014
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 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
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 2 Replies
View Related
Apr 17, 2015
I have the following in a query:
Count: Val(DSum("Num","Google_export_1Cancer_Target_Prea" ,"DatePart('m',[StudyEntryDate])<=" & [AMonth] & " And DatePart('yyyy',[StudyEntryDate])<=" & [AYear]))
Our Financial Year runs from April to March.
The formula works fine up to, and including, December. Then, for the last three months of the financial year (Jan, Feb and Mar), the running total of the 'Num' field stops and the entry for January is just January's 'Num' with the running total starting again for February and March.
How do I rectify this?
View 4 Replies
View Related
Jul 3, 2014
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014
View 6 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
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
Feb 25, 2014
I have about ten append queries to group various financial data to one main table.
I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places.
What is the better approach to have only 2 decimal places for all append data?
View 4 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
Jan 17, 2005
I am hoping someone may be able to help me with this one. I have a set of dates that I would like to query for the next latest date based on certain criteria. Not sure if I explain clearly, if more info is required, please advise. Thanks so much in advance for your help.
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
Oct 2, 2014
I have a database with two tables. One with a schedule that lists all of the events with the person assigned to, with there start date and end date in separate columns.
My second table lists a persons leave. So I have there name and the start and end date of their leave in separate columns.
Is there a form/query I can create using VBA that will allow me to filter out all the criteria that has the same name and then a date that conflicts between the event date and the leave date.
for example,
If Name(in table 1 ) = Name (in table 2)
(if false stop)
AND,
If StartDate (from table 1) and EndDate (from table1) conflicts with StartDate (from table 2)and EndDate (from Table 2)
(if false stop)
If true I want either the query to register it OR have a form appear to show the conflicting entries.
View 1 Replies
View Related
Jul 2, 2015
All of the types? Including Memo?
View 2 Replies
View Related
Jan 29, 2014
I have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".
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
