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 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)
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.
Product (Table PDZRN1) Whse (Table PDZRN1) Description (Table PDZRN1) On Hand (Table PDZRN1) Unit Cost (Table PDZRN1) Unit Price (Table PDZRN1) LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales Table - Sales_History Total - Sum Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code: SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1 AS FYearExport FROM tblProject GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted HAVING (((tblProject.FPAccepted)=True));
hi, i want to calculate how long each person has worked at a company.
i am using access 97.
would i be able to enter something in the criteria that would work this out for me?
something like year([START DATE]) - year(Now()) this does not work but i want to find out the number of years a person has worked for the company from the field START DATE .
I have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.
I'm trying to breakdown a date into years, months, and days. Simple right?
The code I've been using for months returns a negative month if it happens to be the next month. Instead of returning 1 Year, 1 Month, 23 Days, it will instead return 1 Year, -1 Month, 23 Days.
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 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'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,
