Solved

Finding Date range in a dao recordset

Posted on 2010-08-25
7
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 40

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 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