255 limit when using vba to enter reports record source

Posted on 2007-10-11
Last Modified: 2013-11-28
I have some VBA code that reads a listbox and uses that info to build 2 sql strings that are used as the record source in a report and it's subreport. This all works fine except when there are too many selections in the list box. I believe there is a 255 character limit somewhere, but, not sure where (report works is I copy the recordsource) or if I can work around it. The sql string ends up looking like:

SELECT Category, Issue, Sum(qryTicketCategorySummaryByWebIDOracleTest.CountOfIssue) AS CountOfIssue FROM qryTicketCategorySummaryByWebIDOracleTest WHERE ((WebID Like 'buick-*' Or WebID Like 'cadillac-*' Or WebID Like 'chevy-*' Or WebID Like 'gm-*' Or WebID Like 'gmps-*' Or WebID Like 'hummer-*' Or WebID Like 'pontiac-*' Or WebID Like 'saab-*' Or WebID Like 'saturn-*'))GROUP BY Category, Issue;

The report's (and supreport's) recordsource gets updated with the sql string on the open event.

I know I could shorten the query name and some other things, but, there could be more selections, so, I need to know if there's a work-around.

Question by:avoorheis
    LVL 92

    Accepted Solution

    Try using a query as the recordsource, then passing the query name in your code.

    Before opening the report, use code to update the SQL property of the QueryDef object
    representing that query, and you should be good to go.

    Author Comment

    patrick to the rescue again

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now