User Defined Criteria for Number Field (1 Viewer)

LHolden

Registered User.
Local time
Today, 08:14
Joined
Jul 18, 2012
Messages
73
Greetings!

I'm in need of some help on a query that I have built to create a subform on one of my forms. It's my goal to make the subform easily navigable/query-able for the users, and that is where I've hit a roadblock. The subform contains a field - Balance - which I would like users to be able to search based on numeric/mathematic expressions (i.e. >0 and <40). In testing I have created a text box on the main form (BalanceCriteria), and linked it to the subform's balance field through the query in the Criteria field (forms!MainForm!BalanceCriteria).

This works fine with exact numbers - entering 19 will return client's with a balance of 19 - but returns an error - "Expression is typed incorrectly or is too complex to be evaluated" when tested with a numeric equation (>0).

Any help you fine folks can offer would be appreciated!
 

MarkK

bit cruncher
Local time
Today, 05:14
Joined
Mar 17, 2004
Messages
8,178
Is the balance calculated by the query? If so you have the problem that it can only calculate a balance on records in the query, so if you apply a filter, this deselects certain records, which recalculates the balances. Then, if you've applied a filter to the balance field, this recalculation may re- or de-select certain records, which recalcs, reselects, recalcs, and you have a loop.

You may have to calculate all your balances first, and store them in the table, and though that solves the immediate searching problem, has other risks, since any edit to historical records renders all your calculated balances incorrect.

Hope this helps,
 

LHolden

Registered User.
Local time
Today, 08:14
Joined
Jul 18, 2012
Messages
73
Markk - Thanks for the response.

Unfortunately, there are no calculations which go on anywhere within this database. All of the data that exists is pulled in from a report which is sent to us by another source, so all of the Balances are hard coded. For right now there are very few clients with a balance over 0, but I anticipate that that will change in the near future, hence the desire to sort based on a numeric equation.

When I plug the equation directly into the criteria for the query, there are 0 issues and it runs beautifully, it's only when I redirect it to the form that it decides such equations are too complex.
 

spikepl

Eledittingent Beliped
Local time
Today, 13:14
Joined
Nov 3, 2010
Messages
6,144
You are assuming that you can pass an expression as a parameter to your query. You cannot. You can rewrite the query (using a queryDef or perhaps rewrite the SQL if you use OPenRecordset..) like described here: http://allenbrowne.com/ser-62.html

If you only have few criteria than you can construct them logically: In the criterion field write

>0 AND Forms!MyFormname!MyControlName Is Null Or Forms!MyFormname!MyControlName

that will do >0 if you type nothing, or use the value oif the control in an equal comparison if you type something
 

Users who are viewing this thread

Top Bottom