Solved

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

Posted on 2013-12-13
10
302 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
[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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 39718279
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 50

Accepted Solution

by:
Gustav Brock earned 150 total points
ID: 39718454
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
ID: 39718652
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)

 
LVL 40

Assisted Solution

by:als315
als315 earned 150 total points
ID: 39718829
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 37

Expert Comment

by:PatHartman
ID: 39718994
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
 

Author Closing Comment

by:upobDaPlaya
ID: 39736453
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 37

Expert Comment

by:PatHartman
ID: 39736516
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
ID: 39737199
So if the date is a primary key would I be ok ?
0
 
LVL 37

Expert Comment

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

Expert Comment

by:Gustav Brock
ID: 39737575
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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