Financial Functions
Sep 23, 2005
I am doing some work on a loan calculator which I have done successfully in Excel. I now wish to do this with an Access form (A2K). Can anyone please confirm that the Financial Functions (ie CUMPRINC etc) that are available in Excel can be used in Access. The Access help menu calls them Worksheet Functions and I can't see any reference to the above function in this forum. I have entered it in a control and get a #name? error. I dont know if I have made a syntax error or that Access doesn't recognise it.The following is the formula I have used =CUMPrinc([rate]/[freq],[nper],[amount],1,[nper],0).
I have not had the same problems with the following formula =PPmt([rate]/[freq],1,[nper],[amount],0)
Cheers
View Replies
ADVERTISEMENT
Nov 1, 2005
I would like to keep track of 5 seperate funds (or stocks) by price, daily change, total change...etc.
I was thinking of having a table with date as the primary key containing the daily price of each fund. I will enter the prices into this table manually every day.
A second table will automatically makes appropriate calculations and save these changes on a daily basis based on the date. With no user input.
Is this possible?
Can I have the second table automatically grab the dates from the first table? Would the second table use date as primary key for calculations?
Thanks,
~dnnymak :eek:
View 4 Replies
View Related
Oct 20, 2005
I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.
I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.
I do not want to use record by record VB because it is slow and I have a million+ records.
Can anybody help ?
Transactions Table (input)
Account Period Amount
1011100
1015 100
Account Summary (desired output)
Account Period Opening Movement Closing
101 10100100
10121000100
10131000100
10141000100
1015100100200
PS I need to run on Access and SQL Server
View 1 Replies
View Related
Nov 1, 2006
I am trying to create a make table query that extracts information from another table based on its year and month.
The problem I am having occurs when it comes to selecting data across multiple years ie. April 2005 - March 2006. The table that contains the data has two fields that are year and month.
I have created a form to allow the user to enter the required years, but I am unsure how to get the query not to select duplicate months e.g. April 2005 and April 2006.:confused:
Any help would be appreciated.:)
View 12 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
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 4 Replies
View Related
Dec 2, 2012
I'm new to Access and would like to ask if it's possible to create a portfolio manager to track and report financial market holdings - buys, sells and current holdings. The current software managers on the market don't offer what I'd like to see so I'm looking to create my own.
View 1 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
Oct 4, 2013
I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.
So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.
1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days))
2) tbl_Register (where I want forecasted transactions to wind up)
I've got 2 saved queries:
1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register'
SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description
FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description
GROUP BY tbl_InitialPoint.Description;
2) qry_InsertTransactions (populates 'tbl_Register')
INSERT INTO tbl_Register ( PostDate, Description, Amount )
SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount
FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description
WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];
And I've got a form called 'HomePage' that has
1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop
2) A button to start the process
It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).
I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.
Public Sub InsertTransactionsBttn_Click()
Dim db As Database
Dim qdfNew As QueryDef
Dim qdfInsertTransactions As QueryDef
Set db = CurrentDb()
[Code] ....
View 11 Replies
View Related
Aug 31, 2012
I have a query that includes several columns, one of those columns shows returns on investment as a percentage. I need to figure out the annualised returns on investment using the following excel formula
{=PRODUCT(1+B1:B24)-1^(12/24)-1}
This formula gathers the percentages for the last two years then provides an annualized amount.
I'm just wondering whether this is possible to apply to the query so that when I open the query it can provide me with the 2 year annualized statistics.
View 1 Replies
View Related
Nov 15, 2012
I am trying to create a table which would include financial data, by year, by client. For example:
Client #1
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Client #2
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Etc.
Should I have headers as such:
Client #, 2009Revenue, 2009Expenses, 2009NetIncome, 2010Revenue, 2010Expenses, 2010NetIncome, 2011Revenue, 2011Expenses, 2011NetIncome
View 6 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 15, 2005
i have the following line:
Me.Text2 = Right(Me.Text0, InStr(1, Me.Text0, ".") - 1)
and i would like that if Text0 is "test.exe" i want to return "exe", however, with the above i am getting ".exe" ... can someone tell me what i am doing wrong please ?
Thanks
View 4 Replies
View Related
Jan 4, 2005
I currently have a text box that is calculated off of 6 other text boxes. I want the one field that is calculated to update any time I change any of the other fields. I can do this by putting the same calculation in each ones afterupdate event. Below is one section of my code for the arithmetic, and I want to perform that code many times. Can I use a function to do this, and if so some help would be nice? thanks
Private Sub HEAVY_AfterUpdate()
Total_Sheets = SECOND + HEAVY + LIGHT + SCRAP
Total_Sheets = Total_Sheets * YIELD
Total_Sheets = Total_Sheets + D_Sheets
Total_Sheets.Text = Val(Total_Sheets)
End Sub
View 2 Replies
View Related
Aug 18, 2004
In access, I'm trying to create a database to track material that has an expiration date of one year from the appointment letter date. I'm not sure what I need to do here. Here's what I have: I have a table that has fields for the individual (UNIT), the appointment letter date (Appointment Letter Date), and a True/False field (Out of Date) that I want to make True when the current date is greater than the (Appointment letter date + 365 days). Is this possible through Access tables and/or queries or will I have to incorporate VB? I am an extreme novice when it comes to expressions and query building in Access.
Nathan
View 6 Replies
View Related
Aug 17, 2005
Hi,
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
Thanks
View 2 Replies
View Related
Jan 12, 2006
Hi everyone,
Thanks for taking the time to read this.
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae :) I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
How do I get this to happen?
Thankyou for your help
Jess:confused:
View 2 Replies
View Related
Nov 3, 2006
Hello,
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue but I cannot be sure.
Both computers run the same version of Office. Does anyone have any ideas.
View 2 Replies
View Related
Nov 3, 2006
Hello,
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue stopping her running the functions.
Both computers run the same version of Access. Does anyone have any ideas.
View 3 Replies
View Related
Aug 8, 2007
Does anyone know of a good resource to mull through all of the built in functions and learn their syntax, uses, etc?
Thanks
bluke
View 2 Replies
View Related
Dec 7, 2007
Is there a list anywhere that tells what all the functions do in access?
I mean functions such as:
RTrim
RTrim$
SaveSetting
Seek
There is a big long list, and some of them.. I have no idea what they do.
View 7 Replies
View Related
Oct 12, 2005
I have a query that contains a field with 4 codes that I need to replace with values.
Example:
Replace([CE020]![LNCH_FLG],"0","None") Replace([CE020]![LNCH_FLG],"1","Free") Replace([CE020]![LNCH_FLG],"2","Reduced")
Replace([CE020]![LNCH_FLG],"3","State Free")
Replace([CE020]![LNCH_FLG],"4"," Paid")
I get an invalid syntax error, I know if I was to have one Replace function it will works fine, but I’ll still have 3 code to convert.
Any Ideas?
Thanks
--pete
View 7 Replies
View Related
Mar 20, 2007
Hi,
I need some help with a function inserted into a query.
I created a function to convert numbers into text. I then created a query to pull some fields from a table. the last field of the query, I inserted the function using the build feature. I was able to see the function in the list of custom functions for the query.
However, when I run the query I get an error message of undefined function - name-. Can I use my function there? or is there some other way to do so.
thanks for any help received.
Regards,
CathyM
View 2 Replies
View Related
Feb 6, 2008
Hi
Im trying to run two count functions on one column in sql (access 2003). I need to return two new column, pass and fail. something like this; select count(grade) as pass, where grade>=40 and select count (grade) as fail, where grade<= 39.
my table is student_ID, module and grade:
student_ID Module Grade
0012 history 59
0034 history 34
0045 maths 78
0031 maths 45
0046 spanish 66
what i need is:
Module Pass Fail
History 1 1
Maths 2 0
Spanish 1 0
I have achieved it by running two seperate querys, one for pass and one for fail and then merging them with another query but is there any other way?
View 1 Replies
View Related
Mar 31, 2008
I have looked and looked at this expression line and cannot find what is wrong.
Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31])),1) And DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31]))+1,0),1,0))
This gives me an error "Expression entered has a function containing the wrong number of arguements"
However this one:
Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0))
works fine... The only difference is I inserted forms![enterprise report card].[combo31] inside of the Date() function.
Can anyone see what I might be missing??
View 4 Replies
View Related
Sep 24, 2004
Hi there,
I`ve got a problem with access. I Have data in several columns like:
Date Value 1 Value 2 Value 3 Value 4
1-1-01 12 10 11 9
2-1.01 14 7 16 11
etc.
Now I would like to add two columns that determine the min and max value of a row. Like below:
Date Value 1 Value 2 Value 3 Value 4 Min_value Max_value
1-1-01 12 10 11 9 9 12
2-1.01 14 7 16 11 7 16
Is this possible with the standaard min function? or how else do i do this?
View 12 Replies
View Related
