Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simplify a simple expression?

Status
Not open for further replies.

clo

Programmer
Apr 23, 2001
9
CA
I want to apply filter criteria in a report. I use a form to enter criteria and I use these criterias to sort report (in the filter propriety of the report). There are a lot of combo boxes and text boxes on the form and when I click the OK button of the builder, Access is telling me that "The string returned by the builder was too long. The result will be truncated".

The expression is:

(([Forms]![TCR]![Test Case No Text] IS NOT NULL) AND ([Test Cases].[Test Case No] LIKE [Forms]![TCR]![Test Case No Text]))

I know that it works but this expression is repeted for each text box or combo box and it is too long to fit on the filter builder. Can anyone help me out here to simplify this expression, make the expression shorter?

 
WHY is the filter applied to " ... this expression is repeted for each text box or combo box ... "?

Normally a filter is only applied ONCE, to the form!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over
 
It looks to me like TCR is your filter form, right?

If you only have one field called [Test Case No], you can leave out the "[Test Cases]." before it. The same goes for each of your other fields. That should help a good deal, maybe enough.

You can leave out the brackets around [Forms] and [TCR]; they're only required in certain cases, mostly when the enclosed name contains spaces or special characters, or starts with a digit.

Other than that, you could leave out each entire expression if the corresponding control on TCR actually IS null. That would work as long as they don't put in too many criteria at once. It would take code on your part, but the resulting query would be much more efficient than your original one. (You have 8 property lookups for each field, whether or not it will actually affect the query results. Each of these will be done for each row in your record source, whether or not the row will be selected. That's a lot of lookups, and property lookups are relatively slow.)

Finally, for some of the controls, you could plug in the actual value (including surrounding quotes for text literals) from TCR instead of using the [Forms]![TCR]![controlname] reference. You'd only do this if the actual value entered was shorter than the form/control reference. This would be the most work to figure out, but it would also reduce the property lookups and give you greater efficiency. Rick Sprague
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top