Search results

Tried that too, no dice- I tried putting a rstXIRR.MoveLast line before the problem line ReDim CFlow(rs.RecordCount - 1)

It is not returning any records. I think there is a problem with the filter definition in the SelectSql statement, where I have specified WHERE [Match Code]='MatchCode' The idea was that MatchCode would be passed as an argument to the AXIRR function through the query I had shared, and I would...

Ok, fixed that. My bad. Here is the new code: Option Compare Database Function AXIRR(MatchCode As String, Optional GuessRate As Double = 0.1) As Variant Dim rsXIRR As DAO.Recordset Dim db As Database Dim CFlow() As Currency Dim TDate() As Date Dim SelectSql As String Dim I As Integer...

I currently have a query XIRR_Array, that returns three fields: Match Code (a concatenation of investor name and product code), CFlow, which is the cash flow (negative for outflow, positive for inflow) and TDate, which is the date of the cash flow. I have been trying to derive the XIRR for each...

Fixed it, thanks. A stupid error on my part in cutting and pasting part of the sub query. I had to change the [Mod Ttype] in the [Orig Units] criteria at the end to "P" or "SI". It works now. Thanks for the help yet again, Arnelgp.

I have a table “trxns_expt”, and a query “Max RDate” in the attached access database. Trxns_Expt contains a lot of fields of raw data pertaining to investments in various funds, of which only the following are relevant for this query: 1. Folio_no: the account number of the investor for that...

Hi Arnelgp, sorry for the delayed response. Your Dsum with the date being set to a format works like a charm, and may fit the bill perfectly. As of now the running total problem has been solved. Will begin building the other parts of the query now. Many thanks for the response.

arnelgp, thanks for the solution. However, have a couple of problems still: The data table displays immediately on executing the query, but it takes a long time for the number of records to display. This is a truncated table of 2001 records; my main table has 60,000 plus records The initial...

I have a transactions table which has transactions of various types done by different investors in various folios and products. I am interested in the transactions with have the [Mod Ttype] field set to "DR". I would like a date-wise listing of DR transactions for each [folio_no] and...

sneuberg, you genius, you! I had a concatenated field ( Code& NAV Date) as primary key for the Equity_Adj_NAV table. But when I used your method of designating both code and NAV date as primary keys, and removing the concatenated field, the query just sped up. I used this query as an input...

Thanks, Uncle Gizmo, for the response and the attention. Much appreciated. Smig, you are right. What I mean when I say "Sometimes Date+365" will not work- it may be a holiday, or a weekend. In which case, I need the query to return the nearest working day, that is available in the table...

I have a table of Mutual Fund net asset values (NAVs) on a daily basis, called “Equity_Adj_NAV in my Access 2013 database. This table had the following fields: Code- Each Fund has a unique code NAV Date- The date on which the NAV is recorded NAV- the value of the fund on that day. A sample...