Filtering a Variant

Hi,

Been a while since I last coded in VBA, so appreciate some help. I'm trying to filter a variant that has data coming from the database. Simplified example of the code as below:

Please Login or Register  to view this content.

The data will look as below:

date	state	population
1-Jan-21	State A	5
2-Jan-21	State B	7
2-Jan-21	State C	8

In the example data above, I would like the code to filter for 2-Jan-21 and display the results on the Excel sheet.

Appreciate any help on how I can filter the variant vTest to do so.

Thank you.

Hi, it's been a long time since I did anything with ADO but, if only to get the ball rolling...

rather than filtering the resulting Variant could you, instead, apply a Filter to the Recordset itself ?

see the (VB) "Filter Property Example": https://docs.oracle.com/cd/E19531-01...11_ADO115.html

Maybe specify a date via a parameter of the GetDataFunction, then copy the recordset to Excel using the CopyFromRecordset method?

Thanks XLent & ByteMarks. The original code that I have, which I didn't post, is doing something similar, I believe. In the original code, I have the date passed through the SQL query to get only data for the particular date. However, as I'm looping through a large dataset with a lot of dates, the function is calling the SQL database too often. I think it's slowing down the entire process. Hence, I'm looking to pass a large part of the data into a variant and then loop the dates and get the data. Hopefully this will be faster.

Originally Posted by sintek

Why not just ...

Please Login or Register  to view this content.

Or am i missing something...

Mmmm... might be what I am looking for. Let me have a crack at this. Thanks.

Originally Posted by sintek

Why not upload a sample file representing actual so that a more simplistic solution can be offered...
Also ... is it just to filter the data or to extract the filtered records to another sheet

Ah yes, good idea. Please see attached sample file. It has half hourly data going for a number of days. Data are all from a SQL database. Currently, I've an SQL query that filtered data for each half hour. This is basically done by looping the start to end date & time but as explained before, this means a lot of trip to the database.

Instead of making multiple trips to the database, I'm hoping to query all the needed data into an array, then extract the filter the data for a specified date & time to another sheet.

Say, for example, I want to display the data for 1-Jan-21 01:00, I would like it to show on Sheet1 as below:

A	22
B	35
C	56

Have you tried something like

Please Login or Register  to view this content.

Originally Posted by jindon

Have you tried something like

Please Login or Register  to view this content.

Yes, that is what my current code, that I've not posted is doing. As mentioned, it's making too many trips to the database and I think is a reason for its slower performance.

Something like this which will take a start and end date and filter for every 30 min interval and append the results?

Please Login or Register  to view this content.

Why not just Open csv by sequential access?

e.g

Please Login or Register  to view this content.

Originally Posted by ByteMarks

Something like this which will take a start and end date and filter for every 30 min interval and append the results?

Please Login or Register  to view this content.

Sorry, silly question... if I'm pulling data via a function, how do I assign it to another recordset and do steps that you've outlined? In my example, I've the data coming from GetDataFunction. If I replace your code with GetDataFunction as below, it still looks to be calling the function & SQL query for each loop. I am missing something but too rusty on VBA to think through it atm.

Please Login or Register  to view this content.

I didn't use GetDataFunction. I just got one recordset at the start of Test containing all the data, then looped through the time intervals and filtered that recordset for the results.
So the database is queried once, then filters applied subsequently.

Originally Posted by ByteMarks

I didn't use GetDataFunction. I just got one recordset at the start of Test containing all the data, then looped through the time intervals and filtered that recordset for the results.
So the database is queried once, then filters applied subsequently.

Thanks ByteMarks. I was wondering how I could do it if the data comes from a function. My code in the spreadsheet has multiple functions pulling different data from the database.

You could clone the recordset returned by GetDataFunction. But that function creates and terminates the connection each time it's called.

Please Login or Register  to view this content.

Originally Posted by ByteMarks

You could clone the recordset returned by GetDataFunction. But that function creates and terminates the connection each time it's called.

Please Login or Register  to view this content.

Thank you, appreciate the help.