Criteria not working

nicwnacw

Registered User.
Local time
Today, 21:35
Joined
Feb 25, 2003
Messages
34
One day I will finish this database and leave you alone. Latest problem is that I have a query to calculate aged debts - it works very well so far.

I have used expressions to get line total/VAT/Total and Balance outstanding. One expression leading to the next so balance oustanding is expr4-expr3.

However this returns a list of all people who have ordered anything. I have tried IsNotNull, >0, >£0, Between 0 and 999999, etc ad nauseum, but each criteria brings up a parameter query box asking for a value for EXpr3. I assume that this is because the field is based on a formula.

Any clever ways around this? I can't upload the d/b it's too big but happy to e mail it.
 
You are going to have to repeat the calculation in the Where clause:

Where (fldA - fldB) > 0;
 
criteria query

Thank you - I just tried that and I got an error message saying that if I wanted the field to show i had to duplicate the column - so I did. Got an error message saying that i did not enter an aggregate function ??????. Now I'm even more confused.
 
criteria

tried again - I left the () off by mistake - now I have a parameter box for expr's 1-4 . the machine seems to want the entire equation.

Expr1= [sales price]*[quantity]

Expr2 = CCur([expr1]*17.5)/100

Expr3 = [expr1]-[expr2]

Expr3 = [expr3]-[amount]

Instead of using expr's in Expr 2-4, should I just repeat the formulae?
 
criteria

OK that worked - seems very long winded but there you go! I assume that I will now have to take Expr4 out of my IIf's and put the whole formula in - what fun
 
If it is too confusing to work with the query this way, use two queries. The first query does the calculations. The second query selects all the fields from the first query and applies the selection criteria.
 
Thank you

Thank you for that suggestion it would have been easier. You guys are a great help
 

Users who are viewing this thread

Back
Top Bottom