RecordSource Limits

[jeremyh]

Hi all,

Im just wanting to know how to get around the limits of access 2000 recordsources.

what im doing is im making a query form for users to choose all these different options from list boxes which then queries a table.

But when in the VB im trying to pass the SQL string it produces to the recordsource, but its too long. Like it gets up to about 5000 characters, when the total amount it can take is 2048, Is there anyway around this?

Any help would me much appreciated

 

[Liv Manto]

can you break it down into four different variables?

Sql = String1 + string2 + ....

 

[jeremyh]

yeah i have already tried that. What i did was i broke the SQL string into different strings and when i concatinated them back together it still cause the recordsource to throw an error saying its too long.

 

[Liv Manto]

Have you tried using an alias?


For example


Select TablewithaVeryLongNAme.Field1, TablewithaVeryLongNAme.Field2 from
TablewithaVeryLongNAme

can be truncated by:


Select A.Field1, A.Field2 from
TablewithaVeryLongNAme as A

 

[jeremyh]

Na the main problem is that if the user chooses too many otions from the form, then the WHERE statement gets very long!

As what it is doing is its matching the text the user chooses from list boxes in the form aganist text in the tables its searching.

 

[Liv Manto]

I would probably create query subsets, like four querys designed to get subset from biggest set.

create querydef by code, then just delete it once you're done.

Ask Clip for:
CreateQueryDef Method Example

Other than that, can't think of simpler way, Sorry.

 

[jeremyh]

oh no worries, i will give that one a go though!

Thanks alot for the help though!