Our physical year starts 10/01. This is what I have for the criteria, but I still getting a error. Can anyone tell me where Iam going wrong.
FYear: Year([Award Dtd])-IIf([Award Dtd]< _ DateSerial(Year([Award dtd]),10,01),1,0)
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
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.
What do you suggest? Many thanks.
Mehran
I want to create reports for each year my company is in business but by my fiscal year Oct - Sept instead of then normal Jan - Dec. any ideas?
Thanks in advance
Here's my code for the fiscal year
Year([ValueDate])-IIf([ValueDate]<DateSerial(Year([ValueDate]),11,1),1,0)+1 AS FYear
No matter what date it reads from "ValueDate" it returns 1905
eg 11/01/2000 returns 1905
12/01/2003 returns 1905
When I click in the field of each record the date is displayed
11/01/2000 FYear displays 06/23/1905
12/01/2003 FYear displays 06/26/1905
Any ideas what I'm doing wrong?:confused:
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 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?
View 1 Replies View RelatedI 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.
View 7 Replies View RelatedIf 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)?
View 3 Replies View RelatedI have having an issue with determining how to use a customized fiscal year with a DCount function.
My Code:
ThisYear: DCount("*","Open Issues","year([RequestDate]) = " & Year(DateAdd("y",-1,Date())))
The code only does from Jan 1, YYYY TO Dec 31, YYYY
I'd like the code to read from Oct 1, YYYY TO Sept 30, YYYY
So far, in my query, I have a column for Quarters and under the field of that column I have put a statement:
Quarter: IIf(Month([VisitDate]) In (4,5,6),"1",IIf(Month([VisitDate]) In (7,8,9),"2",IIf(Month([VisitDate]) In (10,11,12),"3","4")))
The statement works fine but the problem is it does not tell me which year of does that quarter belongs to. Example: whether it is Quarter 1 of 1999 or Quarter 1 of 2000. Right now it only output either 1 or 2 or 3 or 4.Therefore, is there a way where I can command to give me an output like "Quarter 1, 1999", "Quarter 2, 1999", "Quarter, 1, 2000". Basically with respect to the Year that I have in my data?
I was trying to do this in a SQL Q but thought it would be easier to do multiple calculations in my form if I used Calculated Controls instead.
I have a table Lookup_Tbl_Fiscal_Year with Start_Date, End_Date and FSA_FY.
In my form I want to take the date entered in Last_Signature_Date and look up the FSA_FY.
The Last_Signature_Date would fall somewhere between the Start & End dates.
Here is what I got but no matter what date I enter I get 0 back.
=Nz(DLookUp "[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Last_Signature_Date]">="[Start_Date]" And "[Last_Signature_Date]"<="[End_Date]"),0)
I read that this might slow down the system. Is it better to use SQL?
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'm making a query that has the following Fields
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 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.
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:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
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));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
Issue: We are trying to remove duplicate information for one fiscal year in an ODBC then import and append the correct information from an Excel spreadsheet. The process we have followed is
Copy Master Access file and paste new file (to test, so we do not loose original data)Rename new file TEST.accdbCreate a delete query for Fiscal Year dataDelete information for duplicated Fiscal yearImport correct Fiscal Year information to a new table from ExcelAppend table to master table (ODBC)
Questions:
After being granted permission to delete information, it worked once and now no longer works. When the permissions are reviewed it appears that everything is still granted but the following message is received:
Why does Access change all copies of SDA, even if filenames are different and in different folders?
When we append table, why does it duplicate information?
How do you grab a custom Fiscal Year's values based on the system clock's date?
I am building a query where I want to see the number of closed cases based on the current custom fiscal year with the system clocks date. The report that it feeds only cares about the current FY.
I need the System Clock's FY value in this query
Code:
SELECT shortname AS Station, NZ(TotalCount.TotalCases,0) AS [Cases Complete]
FROM StationList LEFT JOIN (SELECT station, count([Open Issues].ID) AS TotalCases FROM [Open Issues] WHERE [Status]="Closed" GROUP BY Station) AS TotalCount ON StationList.shortname =TotalCount.station;
Within the Query Open Issues I have the FY broken up
Code:
fiscalYear: IIf(Month([Issues].[Opened Date])>=10,"FY" & Year(DateAdd("yyyy",1,[Issues].[Opened Date])),"FY" & Year([Issues].[Opened Date]))
Hi all. I have created a db for tracking different types of request (corrective action, preventive action, rework, scrap, etc). My problem is that the hourly overhead cost is different each year. (I use this cost to figure out actual cost of rework and scrap actions.) I know this is a simple solution but it is eluding me - I need to have this cost change each year but not affect the previous year's figures. Right now my formula is basic:
ReworkCost =[ReworkTotalTime]*69 ($69 is the 2005 overhead cost - this is the first year of use for the db)
I was toying around with using a table for cost alone and then using an If statement to pull the cost based on the year. Does anyone have a different idea on how to do this?
Thanks
Camille
So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.
I have that working on a variable detail DB where the historical data is correct for the report.
i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)
So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50
Good - no problem
NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)
So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15
I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014
Would it require a different report per year?
Hi,
I've currently got several formulas like the one below in my query for which when dividing by 0 I get #error (which I was aware occurred). However, I though my formula would return 0 in such a case (lilke Excel would), but it hasn't
The fields which it is using to calulate the formula have number values, for which if no score is entered then the default value is 0. I would rather not leave this blank and retain the zero value (I haven't looked in to nz function any further because this if for blank fields?) so any help is gratefully appreciated.
The forumla returning #error is:
UKAdj%: IIf(IsError([UKAdjTot]/[UKTotal]),0,([UKAdjTot]/[UKTotal]))
Cheers,
Iain
Hi,
can anybody spot what is wrong?:confused:
System name is a drop down list, with different options
Text125 changes based on the dropdown.
Entered in the text125 control source
=IIf([tblSystemName_System Name]=Null,"",IIf([tblSystemName_System Name]="AS400",[text125]="AS400 Profile",IIf([tblSystemName_System Name]="Credit Apps",[text125]="Credit Apps","Misc")))
Thanks
I am trying to paste this formula into one of my queries, but I get the "the text is too long to be edited" when I paste the formula into the designated field. Is there anyway to get this paste without getting the error?
Access experience level = medium.
CurrentPhaseMetrics: IIf([CurrentPhase]="Phase 1",IIf([NumDaysInPhase]>5,"RED",IIf([NumDaysInPhase]>3,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 2",IIf([NumDaysInPhase]>35,"RED",IIf([NumDaysInPhase]>25,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 3",IIf([NumDaysInPhase]>25,"RED",IIf([NumDaysInPhase]>20,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 3a",IIf([NumDaysInPhase]>5,"RED",IIf([NumDaysInPhase]>3,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase 5",IIf([NumDaysInPhase]>5,"RED",IIf([NumDaysInPhase]>3,"YELLOW","GREEN")),(IIf([CurrentPhase]="Phase
[code]....
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
View 7 Replies View RelatedI'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have been trying to tweak this:
SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))
but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"