Finding Date range in a dao recordset

Hi Experts,

I have a recordset (SELECT Sales.* from sales) which contains a field SalesDate - can I easily extract the oldest and newest date from with my code?

I don't want separate dmin/dmax functions on the original data as the source may change dynamically - I need to pull the answer from the above recordset.

Cheers,
Norb.
LVL 1
Norbert2000Asked:
Who is Participating?
 
dwe761Connect With a Mentor Software EngineerCommented:
So the problem is that you don't want to or are not allowed to change the query that was used to create your recordset in the visual basic code?

As you've probably already discovered, you cannot use DMax or DMin on a recordset and you are not allowed to re-sort an existing recordset.  So the options that you're left with are:

1) Sort the records by SalesDate when creating the recordset
   Dim rs As Recordset
   Set rs = CurrentDb.OpenRecordset("SELECT Sales.* FROM Sales ORDER BY SalesDate")

  Then you'll have to do a rs.MoveFirst (to get min) and rs.MoveLast to get Max

2) Do the Min and Max right in your query like I originally suggested.  Then you don't have to use time to do MoveFirst and MoveLast.  You'll just reference the MyMin and MyMax like any other field in your recordset.
   Set rs = CurrentDb.OpenRecordset("SELECT Sales.* , (SELECT max(SalesDate ) from sales) AS MyMax, (SELECT min(SalesDate ) from sales) AS MyMin
from Sales")

3) Write a loop in your code to go through all records in recordset to find the min and max (least efficient)
0
 
dwe761Software EngineerCommented:
You could accomplish it in the query and have the min and max on every line as follows:

SELECT Sales.* , (SELECT max(SalesDate ) from sales) AS MyMax, (SELECT min(SalesDate ) from sales) AS MyMin
from sales
0
 
als315Commented:
SELECT Min(Sales.SalesDate) AS [Min-SalesDate], Max(Sales.SalesDate) AS [Max-SalesDate]
FROM Sales;

You like to open Recordset and find min and max values? Sort it by SalesDate - first and last records will be min and max.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Norbert2000Author Commented:
Is there any way of doing it given that you have already created the recordset?
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
You can max or min a range of columns in excel
=max(b1:b5000)
0
 
dwe761Software EngineerCommented:
Norbert2000:
I'm not sure I understand your question.  My suggestion was to use the query I provided in creating your recordset.  Then your min and max would be available on each line of your recordset.
0
 
Norbert2000Author Commented:
I have a recordset created in dao in Visual Basic and need to get the data from there...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.