Solved

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

Posted on 2013-12-13
10
298 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 36

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 49

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
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.

 
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 36

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 36

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 36

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 49

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
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.

839 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