Hello, all.
So, I have a Table that consists of a Date and a Value. When the date is the most recent, I'd like to get the NetAssetValue associated with that date, AND use it to multiply to a field from a different table in a query.
I have been unsuccessful in using DMax or Sort Descending (to get the most recent date), and thus I am stuck asking this question. award.jpg
I, essentially, would like to replace the NAV_Date with the most recent date, thus, it would return the appropriate NetAssetValue. As seen in the image, when the NAV_Date is correct (9/30/2013) it used the correct NetAssetValue ($2,500.00).
Anyone have any ideas?
Thanks
The Date() function returns the current date. Unless you have a time stamp or a serial number that can tell you which one was entered last there is no other sure way to know which one is the most recent. In VBA there is a LastModified property to the recordset object, but unless you can guaranty that no one can modify existing records, then you can't be sure that it will return the most recent record.
9 1442
You can run a query that gets the closest date and then do a dlookup to get the NetAssetValue. Your query would be something like - SELECT * FROM AwardsByYearRptQry
-
WHERE DateDiff("d", NAV_Date, Date()) = (
-
SELECT MIN(DateDiff("d", NAV_Date, Date()))
-
FROM AwardsByYearRptQry
-
)
In this case, this query would return the top four records. You would then just have to figure out which of the four you want returned in your DLookup.
Thanks for your reply. What does that Date function do? Also, it returned some records with the most recent NAV_Date, however I am still unsure of how to correlate that data to the original query to use the most recent NetAssetValue?
The Date() function returns the current date. Unless you have a time stamp or a serial number that can tell you which one was entered last there is no other sure way to know which one is the most recent. In VBA there is a LastModified property to the recordset object, but unless you can guaranty that no one can modify existing records, then you can't be sure that it will return the most recent record.
So I think I found a solution. I created another query that merely gets Max NAV_Date and Last NetAssetValue.. I used that query in the main query and used only the NetAssetValue from my "RecentNAV" query and it seems to be working..
Thanks all.
Thanks for your help, Seth.
NeoPa 32,556
Expert Mod 16PB
NB. If you want the current date with the current time element included also, use Now() instead of Date() .
The Now() function wouldn't help if the field didn't have a time element in it though, right?
NeoPa 32,556
Expert Mod 16PB
Not really. No.
If both the setting and the checking are done with Now() instead of Date() though, it all works more as required.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Nova's Taylor |
last post by:
Hi folks,
I am a newbie to Python and am hoping that someone can get me started
on a log parser that I am trying to write.
The log is an ASCII file that contains a process identifier (PID),...
|
by: Tim Morrison |
last post by:
MSSQL2000
I have a table that contains customer transactions
CustomerID
Transaction
TransactionDate
....
I need to select the most recent record that matches a specific CustomerID.
|
by: Shaiguy |
last post by:
I have a table containing the following fields:
ProjectUpdateID (PrimaryKey)
ProjectID
UpdateDate
I would like to create a Query in Ms Access 2000 which will return me
the most recent 2...
|
by: doublej0 |
last post by:
I have the following issue when trying to write a SQL statement to
accomplish my goal. I have done a lot of research, but have been
unsuccessful in creating the query. I have two tables, one with...
|
by: evenprimes |
last post by:
Here's the table setup:
table1:
-key
-name
table2:
-key
-modification_date
-phone_number
|
by: mlwils2 |
last post by:
I'm working with a report that uses three views. There are duplicate records because the 'priority' which comes from one view has changed and SELECT DISTINCT sees it as a separate record. The users...
|
by: Jonathan |
last post by:
I have the following query which is retrieving a set of data it is
almost what I want but I can not manage to get the result I desire.
SELECT r1, r1_dev, r2, r2_dev, date, time
FROM output...
|
by: pradkumar |
last post by:
Hi All
I am having the a table(1 million) with data as follows:
Input:
Col1 Col2 Col3
1 1895 4/7/2007
2 1895 4/7/2007
3 1895 3/5/2005
4 6600 3/3/2007
|
by: brewer95 |
last post by:
I use a form to enter data into a table. The user then has the opportunity to recall the data in the form based on a search to review and update the info. For example, on my menu there is a combo...
|
by: jpgoeth |
last post by:
What I'm trying to do is select the most recent record for each customer who created an account between 2004-01-01 and 2007-01-01. I'm using MSQuery.
I have this so far:
SELECT OrderOrAdjust,...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |