[RESOLVED] FindNext temination

[PHB010108]

Please could someone help me understand how to terminate a loop using the FindNext method in VBA. I'm trying to search through sets of records in a recordset to add up some values when my search criteria are met, so want the FindNext loop to terminate when the search criteria are no longer met. I'm sure this is a very simple request but can't work out what to do. All pointers appreciated.

Thank you.

PHB

[Fromethius]

how to terminate a loop

Wouldn't you use the Exit keyword? Such as Exit For?

[jmcilhinney]

What does the FindNext method in VBA have to do with VB.NET?

[PHB010108]

Wouldn't you use the Exit keyword? Such as Exit For?

Don't think so, need to know when the FindNext completes ....

[PHB010108]

What does the FindNext method in VBA have to do with VB.NET?

Don't know. Are you telling me I'm asking in the wrong place? or are you trying to be helpful?

[jmcilhinney]

Are you asking for help with VB.NET code? If not then you're in the wrong forum, given that this is the VB.NET forum.

[PHB010108]

Are you asking for help with VB.NET code? If not then you're in the wrong forum, given that this is the VB.NET forum.

Yes I'm asking for help with VB.NET code. The FindNext method seems to be valid for working with RecordSets in the VB.NET environment.

Can you help?

[jmcilhinney]

Well it's got nothing to do with VBA then. It's a member of the Recordset class.

In what context are you using a Recordset? It would generally be advisable to forgo ADO altogether and use ADO.NET instead.

[PHB010108]

Well it's got nothing to do with VBA then. It's a member of the Recordset class.

In what context are you using a Recordset? It would generally be advisable to forgo ADO altogether and use ADO.NET instead.

Thanks. I've got a (large) set of records with multiple entries having the same date. I'm trying to subtotal the value of a particular field for the current date then write a record back to the record set for the next date using the subtotalled value, ie a simple bit of summation SQL won't work because each date sub-total is dependent on the previous date.

I'm therefore trying to use FindNext to find all records for a particular date so that they can be subtotalled. The problem I'm having is in understanding how to know when the FindNext "fails", ie does not find another record for the current date, ie so that I'm able to know to do the calculations/write the new record for the next date.

Hope that makes sense? - that you can help.

Many thanks.

[techgnome]

Well, if you truly are using .NET, then you should be using ADO.NET and not ADO ... Then you would be able to do something like this:

For Each dRow As DataRow In myDataTable
.
.
.
'Do a calculation with dRow
.
.
.
Next

-tg

[Tom Sawyer]

Lose the recordset, pull out the value in a DataTable. Use the DataTable.Compute method to sum up all the values where the date column has the date you need.

[Shaggy Hiker]

Why not do the whole thing in one query? Aren't you just getting the SUM of one field WHERE date = x? That could be written in a single SQL statement and returned using ExecuteScalar. However, if there are other needs for the records other than just the sum, then the datatable solution might well be better.

[jmcilhinney]

If you need to do multiple calculations then you'd get all your data into a DataTable and then use its Compute method, e.g.

Dim sum As Integer = myDataTable.Compute("SUM(Amount)", "[Date] = #1/01/2008#")

Follow the Data Access link in my signature to see how to get the data into the DataTable in the first place.

[PHB010108]

Why not do the whole thing in one query? Aren't you just getting the SUM of one field WHERE date = x? That could be written in a single SQL statement and returned using ExecuteScalar. However, if there are other needs for the records other than just the sum, then the datatable solution might well be better.

Do indeed need other records which will be created as part of the partial summation of previous dates so seems like datatable is the way to go.

Thanks for your response.

[PHB010108]

Well, if you truly are using .NET, then you should be using ADO.NET and not ADO ... Then you would be able to do something like this:

For Each dRow As DataRow In myDataTable
.
.
.
'Do a calculation with dRow
.
.
.
Next

-tg

Thank you for your response

[PHB010108]

If you need to do multiple calculations then you'd get all your data into a DataTable and then use its Compute method, e.g.

Dim sum As Integer = myDataTable.Compute("SUM(Amount)", "[Date] = #1/01/2008#")

Follow the Data Access link in my signature to see how to get the data into the DataTable in the first place.

Fantastic help - much appreciated, especially the Data Access link, then further links to the connection strings - fantastic!

(used to be a "classic" Fortran programmer working on giant geophysical modelling simulators so VB is a bit of a learning curve for me - will come back to you if get stuck if you don't mind)

[jmcilhinney]

I should point out that Compute returns an Object reference because it can return different types. That means that with Option Strict On, which it should be, you would need to cast the result as its actual type, e.g.

Dim sum As Integer = CInt(myDataTable.Compute("SUM(Amount)", "[Date] = #1/01/2008#"))