Search results

Thank you for the responses! Ah, that is good to know so I don’t get my hopes up! I mean the kind that you mentioned where the user enters the date in a form and all of the returned records are filled-in with that date. I did actually forget about what you said about using a form for that :[...

Close but still need help I haven’t quite got it yet, but I managed to get a result close to my goal by changing some of the SQL and also using design view. The query result is basically what I want, except I can’t edit the rows (which would actually create new records). When I try to enter...

Hi jal, Sorry to come back to this two months later, but I never got it working and am trying again now. I have been leaving the form out of the mix and only working just with the query to get that right first. When I run this query, SELECT F.* FROM Fund AS F LEFT JOIN (SELECT FundID FROM...

Hi jal, Access is giving an error, “JOIN expression not supported,” and then highlights, “FundsWithAReturnOnThisDate.FundId IS NULL,” from the last line; can this be fixed? I’m sorry, I know only very basic SQL!

Here is an update for anyone who is interested. There are parts that are specific to the named ranges I’ve defined, but some of the more general logic is touched-up. Option Explicit '=============================================================================== ' Function: PIRR...

Hi jal, Yes, there will be no zero or null values as you thought. I will give your response a try and let you know how it works out. Thank you!

Hello, My database holds the monthly returns of mutual funds and financial indicies. After the end of a month, I put in the values in with the Datasheet View of a form where I have to expand each parent row to access the subform records. (I use Datasheet View instead of Form View because it is...

Oh, good to know; thanks!

Yes, I have seen from others that using something like, “FundId,” is the more common way to go. I chose to use only, “Id,” because I like how it isn’t redundant, and it is almost always clear from the context which “Id” is meant. I also like how it makes a SQL statement more simple, like Fund.Id...

It works Excellent! Thank your very much. I got it working from your suggestions. I have attached screenshots of the the form now and the query I used. For the query, I took out the AssetClass and FundManager tables. I pulled the fields Fund like you suggested, but I added FundPerformance as a...

Hello, I am trying to make a form from a query [query.png], and I would like the form to be organized by the table, Fund, where Fund is a table on the “many” side of two one-to-many relationships [relationships.png]. That is, I would like the form to look similar* to the form in the attached...

Working code I have come to a version of the function that works correctly. My previous post had all sorts of basic problems, but the most significant problem was the use of SUMPRODUCT via VBA. Because SUMPRODUCT is used in a non-standard way (to look-up and return a value)...

Revised I found some problems, but the “Show calculation steps > Evaluate” underlines the whole function (with the arguments) now. Does anyone have any ideas, please? The problems I fixed were: Declared the variable WorkingReturn Declared variables for the named ranges Changed the EoMonth...

Back to the original question (using percentages) For various reasons, using percentage values instead of price values will be better for me, so I am switching back to my original post about a UDF. I have written a function, but it errors as it takes-in the first argument. This is my first time...

Ahh, I see. Thanks!

Ah, I understand now why -- works for Columns A & B, which is because, by using the comparison (=), the returned value is a boolean, which is easy turned into a number by --. This makes me realize that it makes sense to get an error when using the double unary on Column C (when including the...

Thank you for your links and explanations—I think I understand this use of SUMPRODUCT now. To answer my own questions for the record, The first SUMPRODUCT works like it does because, yes, the range=value pairs are comparisons, but really return a TRUE or FALSE, not a 0 or 1. Multiplication (*)...

Thank you for your replies and sorry for the delayed response. ptm0412: Thank you very much for your formula; it looks like this could be a big help, but I am struggling to understand it—can you please explain it to me? If the formula is...

Hello, I have a situation where I would normally use a combination of the INDEX and MATCH functions to look-up a value in a cell range along the lines of =INDEX(range, MATCH(some_value, column, 0), MATCH(another_value, row, 0)) The first MATCH returns the row number of its match and second...

Well, I searched and searched some more and found this page, which confirms that relative links cannot be made in Word. The discussion did teach me new about the linked objects though: They are handled as fields in the same way a mail merge uses fields. This means you can use Shift+F9 to view...