Lookup Table Fiscal Year - Getting 0 Back
Feb 8, 2012
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?
View Replies
ADVERTISEMENT
Apr 25, 2006
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
View 7 Replies
View Related
Feb 2, 2006
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
View 2 Replies
View Related
Dec 12, 2006
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:
View 2 Replies
View Related
Jan 25, 2007
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!!
View 3 Replies
View Related
Oct 1, 2007
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)
View 14 Replies
View Related
Nov 4, 2004
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.
View 1 Replies
View Related
Jul 9, 2006
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 Related
Oct 14, 2005
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.
View 7 Replies
View Related
Jan 23, 2014
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)?
View 3 Replies
View Related
Jan 21, 2014
I 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
View 2 Replies
View Related
Aug 26, 2011
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?
View 14 Replies
View Related
Mar 30, 2015
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?
View 3 Replies
View Related
Feb 18, 2015
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.
View 14 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
Aug 5, 2014
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.
View 8 Replies
View Related
Aug 25, 2014
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?
View 1 Replies
View Related
Aug 13, 2015
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]))
View 4 Replies
View Related
Nov 26, 2004
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
View 6 Replies
View Related
Aug 12, 2005
I have a database that keeps track of employees absences and tardies but after a certain amount of time is the data valid. I want to be able to type into my text box a date and have the query collect all infromation a year back. I know how to do it if I was just using the current date but how do I pull the information from a date a user can input?
In the date field I have the following:
=[form]![selectdate]![date]< 365
I've even tried [form]![select]![date] - 365
Can someone help me out? :)
View 2 Replies
View Related
Sep 18, 2005
SELECT Loans.CustomerID, Loans.LoanID, Loans.LoanAmount, Loans.StartDate, Loans.EndDate, Loans.LoanLender
FROM Loans
WHERE (((Loans.StartDate)>DateAdd("d",-32,Date() And ((Loans.EndDate)>DateAdd("y",-1,Date())))));
I want my query to do a monthy lookup of bussiness where a loan is either opened or closed in the last 32 days. This works except its pulling up records from all previous years. I tried to filter the year as shown above, of course its not working. So what do i have to do? Thanks for any help in advanced.
Scott
View 7 Replies
View Related
May 30, 2013
I have a date field that is just a typical date field. When I do a query, I want to look up by just the YEAR in that date field. Can I do that or do i need a separate text field with JUST the year entered in? or can i have a field in my table that just looks up the main date field and displays/uses ONLY the year??
View 3 Replies
View Related
Jul 16, 2013
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,
View 3 Replies
View Related
Apr 25, 2014
I'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"
View 2 Replies
View Related
Sep 30, 2013
I split my db to front-end and back-end. 20 users are using the database. But now I have the problem: In my admin front-end db I created a new table and some reports to the table, but this new table I have only in my front-end db and it doesnot appeares in back-end db - so that other users has no data in hte report for the new table. How I can create new table to be in back-and table too?
View 5 Replies
View Related
Aug 1, 2005
I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.
This is being done in a query and we're using Access 2000.
Any help would be greatly appreciated.
Thanks,
View 6 Replies
View Related
