In-line SQL too long -- Error 2176

[RottPaws]

I have a working report that I need to add a couple of new fields to in Access97.

I have a form that collects a couple of parameters from the user and then when they click the command button, it passes a couple of strings to the report. The report adds them to in-line SQL to create the report based on the specifications of the user.

The query uses a number of sub-queries to create calculated fields, so it's pretty long already. I have to add 2 new fields to the query and now when I try to run it I get

Run-time error '2176'.

The Setting for this property is too long.

You can enter up to either 255 or 2,048 characters for
this property, depending on the data type.

Any ideas on how I can get around this limitation? _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[FancyPrairie]

What I've done is, after I have created the SQL string, I create a new query with the SQL property equal to the SQL string you created. The report's Recordsource property is set equal to the query.

 

[TrojanRabbit]

Are the strings being passed as condition specs or just as strings in the recordsource? If the strings are put into the query as fields, you can just specify the form fields (Forms![<frmname>]![<field name>] or Form_<frmname>.<field name>). You can do this is the query or the report. Hope I'm explaining clearly. An idea to try.

 

[RottPaws]

The strings are the result of conditional specs. The retrieve/display fields are fixed, but depending on the options the user selects, criteria is added to (or left out of) the WHERE clause for the main and sub-queries. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[RottPaws]

FancyPrairie,

How would I save the query and use it when the SQL could change each time the report is run by the user? _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[RottPaws]

I got it figured out. Thanks for getting me pointed in the right direction!

FancyPrairie's suggestion to save the SQL to a query, I looked in the help files.

I created a query using the SQL and bound the report to that query.

In my code, I build the new SQL, use DoCmd.DeleteObject to delete the query, and then use CreateQueryDef to save the new SQL to the same name.

After that, the code opens the report. Works great!
figured out how to use _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!