upobDaPlaya
asked on
How do I exit the Loop when a new month is found
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-Dat
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
Any suggestions.
Function LoadData()
Dim rst
Dim dbs
Dim rs
Dim strDt as String
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset("tbl-Dat
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PatHartman
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.
ASKER
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...
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.
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.
ASKER
So if the date is a primary key would I be ok ?
NO!!! If your process depends on row order - and yours does, you MUST use a query with an order by clause.;
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
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