Solved

How do I exit the Loop when a new month is found

Posted on 2013-12-13
10
295 Views
Last Modified: 2013-12-24
I currently have the below Access Module where I am looping thru dates provided by a table called  tbl-Dates.  Once the loop is complete and exits the loop the module does 3 queries.  My problem is I am loading a months worth of days into the table at a time since the queries that run at the end are specific to a particular month.  What I would like to do is load a years worth of data in my date table (tbl-date) and have the loop recognize once the end of a month has occurred and exit the loop, run the 3 queries outside the loop and then go back to the loop to process the next month.

Any suggestions.


Function LoadData()

Dim rst
Dim dbs
Dim rs
Dim strDt as String

Set dbs = CurrentDB
Set rs = dbs.OpenRecordset("tbl-Date")


rs.MoveFirst
Do While Not (rs.EOF)

strDt = (rs!Date)
  XXXX
  XXXX
  XXXX
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing


FinalQuery1
FinalQuery2
FinalQuery3

End Function
0
Comment
Question by:upobDaPlaya
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
Comment Utility
I can't tell what the loop is supposed to do so I'm not sure how to control it.  Is it updating a table?  Are you sure you need a code loop and can't do the update in a query?  Queries are much more efficient than code loops.  Also, what are the three queries at the end doing?  Tell us a little more about the real application.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 150 total points
Comment Utility
That would be something like this:

Function LoadData()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim datDt As Date
Dim intMd As Integer

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset("tbl-Date")

rst.MoveFirst
Do While Not rst.EOF
  datDt = DateValue(rst!Date)
  intMd = Month(datDt)
  ' XXXX
  ' XXXX
  ' XXXX
  rst.MoveNext
  If rst.EOF = True Or Month(rst!Date) <> intMonth Then
    FinalQuery1
    FinalQuery2
    FinalQuery3
  End If
Loop
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Function

/gustav
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 50 total points
Comment Utility
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 150 total points
Comment Utility
You can also use nested loops:
Function LoadData()

Dim rst
Dim dbs
Dim rs
Dim strDt as String
Dim strMY as String

Set dbs = CurrentDB
Set rs = dbs.OpenRecordset("tbl-Date")


rs.MoveFirst
Do While Not (rs.EOF)
strDt = (rs!Date)
strMY = Format(rs!Date, "YYYYMM")
     Do while strMY = Format(rs!Date, "YYYYMM") and Not rs.EOF
           XXXX
           XXXX
           XXXX
           rs.MoveNext
     Loop
     FinalQuery1
     FinalQuery2
     FinalQuery3
Loop
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing

End Function

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You need to use a query for the recordset rather than a table because you need to order the resultset by date.  That is the only way to keep all the records for a date together.  You cannot rely on record order in any resultset if it is not specifically sorted.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Closing Comment

by:upobDaPlaya
Comment Utility
I pondered everyone's response and realized that I could solve by issue with a table that a start date and end date as fields; then loop thru this table.  Although I did not necessary use anyone's solution (also I realize I should have probably provided the database) the dialogue above allowed me to venture down several different critical thinking paths.  Thus, the question/answer was a huge success for me...
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You didn't mention my most recent comment so I will reiterate - tables and queries are unordered sets.  The only time you can rely on row order is in a query that has an order by clause.  

Many people get fooled into thinking tables will always present their rows in order but that's because they typically don't scroll record by record through large sets.  Access reorders each table into primary key sequence whenever a database is compacted so immediately after compacting and assuming no record has been added or updated, a table will be in PK sequence.   But, beyond that, SORT if you want to ensure order.
0
 

Author Comment

by:upobDaPlaya
Comment Utility
So if the date is a primary key would I be ok ?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
NO!!!  If your process depends on row order - and yours does, you MUST use a query with an order by clause.;
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Pat is right. You can never rely on an order of records from the table directly.
My sample code was only to sketch the idea.

This line should read like this as you can create the query on the fly:

Set rst = dbs.OpenRecordset("select * From tbl-Date Order By tbl-Date.[Date]")

/gustav
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

17 Experts available now in Live!

Get 1:1 Help Now