I am building a Db that will report against individual transactions. Each transaction will be on a specific day.
Now I will need to slice and dice the data in a number of different ways depending on what the end user wants to see.
Therefore I need to build a date table that will aggregate all the individual days by Week, Month, calendar year, fiscal year...etc.
Being a rookie, I was simply going to build a table that has all the days for the last three years and then the corresponding weeks, months etc in columns to the right.
Because of this design, I will have over 1,000 records. Could I do it differently to keep the size of the table down?
For example use ranges as a cell value (Our week 1 is named "Feb 1st Wk", it is 1/30/06-2/5/06 for fiscal Yr 2006, 1/31/04-2/6/05 for fiscal Yr 2005...etc)
Function GetFiscalYear(ByVal x As Variant) If x < DateSerial(Year(x), FMonthStart, FDayStart) Then GetFiscalYear = Year(x) - FYearOffset - 1 Else GetFiscalYear = Year(x) - FYearOffset End If End Function
I call on the function in my query to determine the FiscalYear for a date. Expr1: GetFiscalYear([ProdDate])
It sends back the correct Fiscal Year. However, when I try to put criteria in for Expr1 to show only Fiscal Year 2007 instead of all fiscal years, it gives me a data mismatch type error.
The [ProdDate] has the following formula: Prod_Date: IIf([40Day]="True",DateAdd("d",-40,[Date Code]),DateAdd("d",-50,[Date Code])) where [40Day] returns True or False to determine if I should subtract 40 or 50 days from the Date Code. The Date Code is in the format ##/##/#### and is sometimes null.
I have tried everything I could think of and am completely stumped. Thanks in advance for any help!!!
I've got the conditional formatting thing down and am able to determine if a date is close to expiring by using 'value is less than now()+30', but I'd like to replicate the same thing, but the expiration date is based on the fiscal year (October to October).
The date entered is the date of qualification. How can I conditionally format to fit my needs?
I have a form with four fields, date1, date2, date3 and date 4. These have all a mm/dd/yyyy format. I need to add an unbound control which will count in months and years the difference between the dates.
Ex. date1: 01/01/1970 date2: 05/05/1980
date3: 06/06/1990 date4: 06/30/2001
Fields date3 and date4 may be blank, therefore the code should consider only counting the first two.
I have a problem in calculating the difference between two dates and displaying the output into Years Months Weeks Days. My problem is that dividing the number of days by 30 and requesting an integer is not accurate enough. I have looked at Pat and Jons examples shown in these threads
which either method would suit me as the accurate answer is what I am after.
My problem is that not all of my records would have a start and end date. Therefore both examples would result in an error.
Can anyone please give a hint on how to perform the calculations on those fields which have dates recording the results but ignoring those fileds which do not have a start / end date.
I am having a bad day and I'm sure the answer is so easy that I am going to kick myself but having played around with the modules by limited knowledge is not enough.
Find the difference between two dates is simple if you're simply looking for the number of days between them. However, if you're looking to express the difference in a bit more user-friendly way, you can use the following code to calculate the difference in years, months, and days. The code accounts for zero values, plurals and the Oxford comma. Further, it automatically sets the later of the two dates to the End date, so the dates can be used interchangeably.
Examples: June 4, 2010, July 3, 2012 returns "2 Years and 29 Days" June 4, 2010, July 5, 2011 returns "1 Year, 1 Month and 1 Day" June 4, 2010, June 5, 2010 returns "1 Day"
Code: Function DiffOfTwoDates(dtmDate1 As Date, dtmDate2 As Date) As String ' Written by Will Knapp, Freelance Access Developer, 2013 Dim dtmStart As Date, dtmEnd As Date Dim strDiff As String ' Resulting String Dim yDiff As Integer ' Year Difference Dim mDiff As Integer ' Month Difference Dim dDiff As Integer ' Day Difference Dim CommaLoc As Integer
I am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
THEN . . . . I need to also isolate certain periods, for example July- March for YTD (year-to-date) analysis and compare YTD of 2006 with that of 2005.
I have a table that lists computers I want to know the age of them for replacement purposes. I am using this function to populate a textbox on a form that updates my table. the results I am getting is a negative whole number I would like to get a positive number with one decimal place. so if computer was shipped September 24, 2010 my textbox would say 3.5 not -3
Here is my function
Code: Private Sub Form_Load() Dim theDate As Date Dim age As Integer theDate = Nz(Me.compDate.value, 0) If theDate > 0 Then age = DateDiff("yyyy", Now(), theDate) Me.compAge = age End If End Sub
so the Army uses a fiscal year 1 Oct thru 30 Sep I have a report that needs to be run every quarter. i curently have my a field formated as [date],"q/yyyy" wich givs me april in the 2nd quarter for me April is in the 3rd quarter. any ideas?
I have created a query in Access 2000 which contains a date field in short date format.
I would like to add a field to this query which calculates the financial (fiscal) year from my date field. Our financial year runs from Apr 1st to Mar 31st.
I'm totally stuck - any ideas would be greatly appreciated!!
I have a database that tracks call-monitoring scores for call center employees. They are given a bonus based on their scores which is calculated every pay-period. The following attachment is what I need the query to show. Or perhaps an excel document linked to the database. Just wondering if you all could take a look at this and tell me what the easiest method would be. Also, what would be involved. My major concern is the query being able to differentiate the different fiscal weeks and or payperiods.
I need to provide a report on data over a fiscal year that has the following format:
apr 03 may 03 jun 03 q1 total jul 03 aug 03 sep 03 q2 total oct 03 nov 03 dec 03 q3 total jan 04 feb 04 mar 04 q4 total year total
I can group by quarter and sum() in the group footer which works, but when I group by year I get totals for 03 and 04 separately. I have also tried sum() and dsum() in the page footer, which gives #error, and vba in page footer:
Dim pageTotal As Long Sub Detail_Print(Cancel As Integer, PrintCount As Integer) ' Add to Page Total pageTotal = pageTotal + Me![ClientsServed] End Sub Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer) ' Show Page Total Price Me![yrClientsServed] = pageTotal End Sub
which calculates properly in the detail section (var watching "pageTotal") but outputs nothing in the footer, though the name of the text box in the footer is correct. If anyone can see an error in what I'm doing or suggest another approach, I would really appreciate it, this is driving me nuts.
I'm trying to change the way date calculations are done in a training db. I show them now in an expression in my query as Between Date() And Date()-365, but I need to show them based on a FY beginning 7/1 of any year and ending on 6/30 of any year. Any suggestions?
I have a table named PO table...How do I get the fiscal month from the date column I dont want to search a particular date but the date field column and retrieve the fiscal month using DLookup function?....
I'm trying to create a table in an Access Form or create a block of code to export the table with query results to Excel or PDF. In the end I would like to run a query or queries to populate the table or export the query results creating the table. I'm not sure the best way to create the table.
I would like to sort records based on fiscal year for a chart. The fiscal year would be 7/01/2003 to 06/30/2004. I would like to sort this based on oldest date to newest date. This query could span several fiscal years. Any help would be greatly appreciated.
If my fiscal year ends 12/31 but the pay period doesn't end until 1/10, how do I get a query to tell me there are 25.3 pay periods remaining (counting from the last pay period 1/11)?
writing a query to grab the Fiscal period based on my Fiscal Calendar table. I need to create Month, Fiscal Year, Qtr, and Period columns to match with the dates from the Calendar Table based on the data in the Purchase Orders Summary.
I have attached the database with the calendar and the data set that I am suppose to match it to.how/where to link the two tables together in a query,
