Report record source SQL "too long" error 1

[ccoindre]

The error message returned from executing the me.RecordSource = stringSQL is:
"The setting for this property is too long". Run time error 2176.
I have two subreports that list the differences (added, deleted, and modified records) between two tables that the user selects. I used the "find unmatched" query wizard and modified the SQL to create SQL strings with the user selected table names used as the Record Sources for the subreports. With all the fields being compared, the SQL string gets lengthy (approx 2K characters). Selecting tables with short names works OK, but, the longer names do not. The table names are inserted into the SQL string multiple times and add proportionately to its length. I can always create temporary tables with short names and use these in the SQL string; but, that might create problems with concurrent users.
Any suggestions?
Thanks in advance

 

[RoyVidar]

I can't find the specifications for Access 2000, but in 2002 the limit is 32 750. This is also the same limit as the rowsource of listboxes has, which increased from 2048 (Access 2000) - so by that I'm guessing you're using Access 2000 or earlier, and that the recordsource limitation is 2048 characters.

Now, what you can try, is alias the tables. Simple sample:

[tt]select tblFirstTable.field1 tblFirstTable.field2, tblSecondTable.field3
from tblFirstTable inner join tblSecondTable
on tblFirstTable.idfield = tblSecondTable.fkfield[/tt]

becomes

[tt]select a.field1 a.field2, b.field3
from tblFirstTable a inner join tblSecondTable b
on a.idfield = b.fkfield[/tt]

- should save some characters, shouldn't it;-)

Roy-Vidar

 

[ccoindre]

Thanks Roy-Vidar
Haven't used an alias before, but, it should work. appreciate the suggestion.
ccoindre