[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-12-13
10
Medium Priority
?
305 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 39

Assisted Solution

by:PatHartman
PatHartman earned 600 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 52

Accepted Solution

by:
Gustav Brock earned 600 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 31

Assisted Solution

by:hnasr
hnasr earned 200 total points
ID: 39718652
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 40

Assisted Solution

by:als315
als315 earned 600 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 39

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 39

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 39

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 52

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

650 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