• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

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.

1 Solution
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
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.
Norbert2000Author Commented:
Is there any way of doing it given that you have already created the recordset?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Aaron TomoskyTechnology ConsultantCommented:
You can max or min a range of columns in excel
dwe761Software EngineerCommented:
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.
Norbert2000Author Commented:
I have a recordset created in dao in Visual Basic and need to get the data from there...
dwe761Software 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)
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now