• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

255 limit when using vba to enter reports record source

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.

1 Solution
Patrick MatthewsCommented:
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.
avoorheisAuthor Commented:
patrick to the rescue again

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now