Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Finding Date range in a dao recordset

Posted on 2010-08-25
7
Medium Priority
?
566 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
Independent Software Vendors: 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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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