Solved

Finding Date range in a dao recordset

Posted on 2010-08-25
7
470 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:Norbert2000
7 Comments
 
LVL 10

Expert Comment

by:dwe761
ID: 33521737
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
 
LVL 39

Expert Comment

by:als315
ID: 33521741
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
 
LVL 1

Author Comment

by:Norbert2000
ID: 33521785
Is there any way of doing it given that you have already created the recordset?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 33521838
You can max or min a range of columns in excel
=max(b1:b5000)
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33521946
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
 
LVL 1

Author Comment

by:Norbert2000
ID: 33521967
I have a recordset created in dao in Visual Basic and need to get the data from there...
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
ID: 33522199
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now