Solved ADO ListBox and Filter

[ProgramRasta]

Hi All

I'm having some serious problems that I'm just unable to solve which I hope you can be of assistance with once again.

I have a listbox that I am trying to filter a subform with, the problem is when I use queries the filter just hangs as I have a user defined function and it just stops mid way through filtering. The UDF has a number of domain aggregate functions which I've read can hinder performance. when I run the function as an Access Query it loads up in 2 seconds, however, If I right click and filter then its a different story.....

I thought a way around this would be to make a table in memory with the results from the query so that they are static and then the filtering would be very quick. However, it doesn't seem to work though ADO.

I have attached a sample database (The code for filtering was provided by a technical user MAJP).

I would really appreciate any assistance on the matter.

Many Thanks

 

[CJ_London]

the access form filter/sort functionality does not work with ado recordsets - you need to write your own.

I took a quick look at your example db - I don't see any UDF's

 

[ProgramRasta]

the access form filter/sort functionality does not work with ado recordsets - you need to write your own.

I took a quick look at your example db - I don't see any UDF's

Thanks for the reply. I have a UDF in my actual database. Would you be able to suggest any examples, forum posts or any code to get me started. I have literally searched 100’s of websites trying to get this to work.
Appreciate your time.

 

[MajP]

The reason the ADO recordset is not loading is because your DAO sql string is jacked. If you fix the string it will load. However, you cannot filter the in memory recordset AFAIK. I do not think this approach is doable. I think you need to fix whatever is the cause of this problem of loading the query. I do not understand why the query will not load without hanging.

I have a UDF in my actual database. Would you be able to suggest any examples, forum posts or any code to get me started. I have literally searched 100’s of websites trying to get this to work

What do you need help with? It sounds like you just need to debug your query. Without seeing your real query and UDF, no one can make recommendations.

 

[CJ_London]

OK, so a different question

The UDF has a number of domain aggregate functions which I've read can hinder performance

Don't know about hinder - domain functions can kill it if your recordset has a large number of records.

when I run the function as an Access Query it loads up in 2 seconds, however, If I right click and filter then its a different story.

I suspect what you are seeing is the first few records being displayed - a feature of access. To see how long it actually takes to load, time how long it takes for the recordcount to display in the navigation window at the bottom. Filtering and sorting cannot be done until the entire recordset has loaded - and the filtering/sorting actions need to process the whole recordset again so will take a similar amount of time.

Would you be able to suggest any examples, forum posts or any code to get me started.

I agree with MajP - not enough information to go on. I can say in memory filtering of disconnected ADO recordsets can be slow compared with a connected ado or dao recordset because the disconnected recordset does not have indexes to work with. This assumes your connected table is appropriately indexed and the indexing is not effectively disabled when using criteria such as the initial * with 'like *somevalue'

I don't know if you are aware of the importance of indexing - if not, see this link https://www.access-programmers.co.u...ing-is-important-for-good-performance.291268/

Other good practice - only load the data required (number of records/number of fields) so apply this to your form recordsource as criteria (not the 'where' parameter of openform which is actually a filter) and apply any initial sorting as well - don't load all records to subsequently filter/sort.

The way I handle it is to have a form recordsource something like

SELECT * FROM myTable WHERE False

this creates an empty recordset

then with openform command pass the criteria in the openargs parameter, not the where parameter

And in the form load event put something like

me.recordsource=replace(me.recordsource,"False",me.openargs)

 

[Galaxiom]

Use the Filter Property directly on the ADO Recordset itself.

Then reload the Listbox with:

 Me.listboxname.Recordset = Me.listboxname.Recordset

Looks odd but that is the suggestion I discovered years ago and it worked so I never questioned it.

 

[arnelgp]

try this one.

 

[ProgramRasta]

Hi All

Firstly, thank you all for your contributions. This site truly has a wonderful community.

Unfortunately, none of the proposed solutions have worked so far.

I have attached a working example of the UDF with a larger dataset.

As you will see, the 'SampleQueryData' query runs quickly. However, when you try and filter on it, it's a different story.

I'm really struggling to see how I can make this work.

Many Thanks

 

[CJ_London]

I recommended you indexed fields which you will filter or sort on - you haven't done that. Even the primary key is not indexed
your sample data form has a filter set in properties (order by duration) - remove that. Since it is not a displayed field, not sure what the benefit is, but if required, include it in your source query

 

[ProgramRasta]

I recommended you indexed fields which you will filter or sort on - you haven't done that. Even the primary key is not indexed
your sample data form has a filter set in properties (order by duration) - remove that. Since it is not a displayed field, not sure what the benefit is, but if required, include it in your source query

Thanks for the reply - I've indexed the required fields and removed the ordering by and it has improved the speed of my database.

However, it's still very sluggish.

Is the problem that the UDF is too complex?

Perhaps it's my lack of understanding but I thought that the function would return a data type long and it should filter quite quickly on the required records. If I didn't have a function and they were just numbers, the filter would be instantaneous.

Do you think the only solution to make this viable is to insert the records into a table and then filter on the required records?

Appreciate your time.

 

[ProgramRasta]

The answer to my question all along was the index.....

I had ignore nulls set to yes when in fact it should have been set to no.

Unbelievable I've spent 7 days and the answer was right in front of me.

Appreciate all your time.

Stay safe and enjoy the weekend.

 

[CJ_London]

UDF's will slow it down - the example data you sent was quite a small dataset so I did not see any speed issues once I modified as I advised.

Filtering/sorting recalculates the recordset so the UDF is is in heavy use. I've taken a quick look at it and you might want to look at a smarter way to calculate the number of days rather than parsing between start and end days. The holidays can all be done is sql as part of your query. Pretty sure weekends can be as well.

I'll see if I can take a look later

Edit: just seen your last post - glad you got it sorted

 

[CJ_London]

The following sql produces the same result as you have - except your module does not appear to cater correctly for holidaydates on a weekend. It is also significantly faster for sorting and filtering (less than a second on your sample data), but is not updateable. If being able to update is a requirement, then probably not for you.

I've left in the steps so you can see how the calculation is worked up. wDays is the equivalent of your Duration column. If you are happy with it, you can merge the steps into one calculation

SELECT Sample.ID, Sample.[Purchase Date], Sample.[Sell Date], Sample.Price, Sample.SellerName, (SELECT Count(*) FROM tblHoliday WHERE HolidayDate BETWEEN [Purchase Date] AND [Sell Date]and weekday(holidaydate,2) not in (6,7) ) AS Hols, [sell date]-[Purchase Date] AS maxDur, Weekday([purchase date],2)-1 AS stWD, ([maxdur]\7) AS fullWE, ([maxdur] Mod 7) AS baldays, [stwd]<=5 And [stwd]+[baldays]-1>=5 AS HasSat, [stwd]<=6 And [stwd]+[baldays]-1>=6 AS HasSun, [maxdur]-([fullwe]*2)+[hassat]+[hassun]-[hols] AS wdays
FROM Sample
WHERE (((Sample.[Sell Date])<#6/1/2021#))
ORDER BY [sell date]-[Purchase Date];

The process is

1. determine the maximum duration and determine the number of full weeks (maxDur and fullWE)
2. Then determine any remaining days. (baldays)
3. there are 2 weekend days in every full week, and a simple calculation to determine if there are any weekend days in the remaining days (hasSat, hasSun)
4. take the maximum duration, deduct the full week weekends, any weekend days in the remaining days and holidays to give the number of working days

 

[ProgramRasta]

The following sql produces the same result as you have - except your module does not appear to cater correctly for holidaydates on a weekend. It is also significantly faster for sorting and filtering (less than a second on your sample data), but is not updateable. If being able to update is a requirement, then probably not for you.

I've left in the steps so you can see how the calculation is worked up. wDays is the equivalent of your Duration column. If you are happy with it, you can merge the steps into one calculation

SELECT Sample.ID, Sample.[Purchase Date], Sample.[Sell Date], Sample.Price, Sample.SellerName, (SELECT Count(*) FROM tblHoliday WHERE HolidayDate BETWEEN [Purchase Date] AND [Sell Date]and weekday(holidaydate,2) not in (6,7) ) AS Hols, [sell date]-[Purchase Date] AS maxDur, Weekday([purchase date],2)-1 AS stWD, ([maxdur]\7) AS fullWE, ([maxdur] Mod 7) AS baldays, [stwd]<=5 And [stwd]+[baldays]-1>=5 AS HasSat, [stwd]<=6 And [stwd]+[baldays]-1>=6 AS HasSun, [maxdur]-([fullwe]*2)+[hassat]+[hassun]-[hols] AS wdays
FROM Sample
WHERE (((Sample.[Sell Date])<#6/1/2021#))
ORDER BY [sell date]-[Purchase Date];

The process is

1. determine the maximum duration and determine the number of full weeks (maxDur and fullWE)
2. Then determine any remaining days. (baldays)
3. there are 2 weekend days in every full week, and a simple calculation to determine if there are any weekend days in the remaining days (hasSat, hasSun)
4. take the maximum duration, deduct the full week weekends, any weekend days in the remaining days and holidays to give the number of working days

CJ, I am lost for words at your commitment to helping others in the community.

I will try your solution when I get a chance and report back to you on the performance.

Many thanks for your time once again.

 

[ProgramRasta]

CJ, I am lost for words at your commitment to helping others in the community.

I will try your solution when I get a chance and report back to you on the performance.

Many thanks for your time once again.

The following sql produces the same result as you have - except your module does not appear to cater correctly for holidaydates on a weekend. It is also significantly faster for sorting and filtering (less than a second on your sample data), but is not updateable. If being able to update is a requirement, then probably not for you.

I've left in the steps so you can see how the calculation is worked up. wDays is the equivalent of your Duration column. If you are happy with it, you can merge the steps into one calculation

SELECT Sample.ID, Sample.[Purchase Date], Sample.[Sell Date], Sample.Price, Sample.SellerName, (SELECT Count(*) FROM tblHoliday WHERE HolidayDate BETWEEN [Purchase Date] AND [Sell Date]and weekday(holidaydate,2) not in (6,7) ) AS Hols, [sell date]-[Purchase Date] AS maxDur, Weekday([purchase date],2)-1 AS stWD, ([maxdur]\7) AS fullWE, ([maxdur] Mod 7) AS baldays, [stwd]<=5 And [stwd]+[baldays]-1>=5 AS HasSat, [stwd]<=6 And [stwd]+[baldays]-1>=6 AS HasSun, [maxdur]-([fullwe]*2)+[hassat]+[hassun]-[hols] AS wdays
FROM Sample
WHERE (((Sample.[Sell Date])<#6/1/2021#))
ORDER BY [sell date]-[Purchase Date];

The process is

1. determine the maximum duration and determine the number of full weeks (maxDur and fullWE)
2. Then determine any remaining days. (baldays)
3. there are 2 weekend days in every full week, and a simple calculation to determine if there are any weekend days in the remaining days (hasSat, hasSun)
4. take the maximum duration, deduct the full week weekends, any weekend days in the remaining days and holidays to give the number of working days

Just one question if I may, would it be possible to put the above code into a function and pass the dates as parameters?

I’m conscious that I will have to use the code frequently, it might be handier to put it in a function as opposed to copying the sql code frequently. Note that the source table will not change.

thanks again

 

[CJ_London]

It is just a calculation and can be reinterpreted in a module. But would not be copy/paste. Create a function and rework each calculation element - they are all based on a 'start date' and an 'end date' which would be the parameters to your function.

The function should be significantly faster than your current function since it is not parsing each date in a sample - some of which are several hundred days long. I mentioned a few posts back about you may want to find another way to do your calculation rather than parsing to improve performance, translating the sql to vba would be a way to do that.

You could apply the function to your sample data query, but performance will be affected, although the query should then be updateable

 

[ProgramRasta]

Thanks once again CJ!

I have run the SQL and I’m amazed at the speed of filtering the data.

 

[CJ_London]

goes to show the power of using sql

 

[ProgramRasta]

The query runs fantastically smooth as an Access query. I reinterpreted the SQL as a UDF and I noticed a performance hit.

There's is one unexpected outcome from the query.

The 'Hols' field will go to zero if I filter on 'wdays' when there are holidays to discount. This means that the 'wdays' will include the holiday totals

I added the table with the holiday dates to the query and this gets rid of the bug, however, without linking the two tables by a field, this returns an enormous amount of identical records.

Any idea on how to work around this?

Thanks again

Edit: I brought in the holidays table and done a select distinct on the id field - slight performance hit by bringing in the holidays table and not linking it but it works!

 

[CJ_London]

I reinterpreted the SQL as a UDF and I noticed a performance hit.

you will, 99 times out of 100 UDF's will be slower than sql

The 'Hols' field will go to zero if I filter on 'wdays' when there are holidays to discount.

It doesn't on the sql I provided. Are you talking about when using your UDF?

this returns an enormous amount of identical records.

Any idea on how to work around this?

you would need to group by all fields and count the holiday days - but that would be slow in comparison to using the subquery even if you use a non standard join.