Using query and expression that is greater than 0

[trident]

May I request for assistance please.



I have a query with the following columns:

Submit Date - Medium Date
DeadLine Date - Medium Date
Revise Deadline Date - Medium Date

I have created 3 expressions:

EXPR1 - IIf(IsNull([revisedeadlinedate]),[deadlinedate],[revisedeadlinedate])
EXPR2 - Date()
EXPR3 - IIf(IsNull([Submit]),[expr1]-[expr2],0)

I am trying to use expression 3 to get all values that is greater 0, by putting >0 in the criteria, however, when the program runs, it is asking a value for expression 1 and 2.
If you press enter, it will show a blank list, but if you put values in EXPR 1 and 2, it shows an error message.

Hope to hear from you guys.

Thanks.

Trident

[rpeare]

if this is an aggregate query you can't reference a calculated field like that you'd have to do something like:

IIf(IsNull([Submit]),(IIf(IsNull([revisedeadlinedate]),[deadlinedate],[revisedeadlinedate]))-(Date()),0)

[NTC]

your exact syntax for those expression fields must be with a colon i.e.:
EXPR1:IIf(IsNull([revisedeadlinedate]),[deadlinedate],[revisedeadlinedate])
EXPR2ate()
EXPR3:IIf(IsNull([Submit]),[expr1]-[expr2],0)

your post says you are trying to use a >0 but I don't see where that is suppose to be going.....

[rpeare]

I think he means he's putting > 0 in the criteria of the EXPR3 field

putting criteria in calculation fields in aggregate queries just doesn't work if the formula uses any alias calculations within the query itself.

[trident]

The use of >0 in EXPR3 is that I will be able to get the records that are greater than 0, right now, i have a lot of record and that only few of these are greater than 0.

Any way to get this query running.

[rpeare]

I answered in post #2 did you try it