Query using totals

[Page410]

Good afternoon all,
Quick question, should be an easy one but I think I'm a bit brain dead today.
I seem to be having some trouble applying criteria to an expression within a query that is using totals. I keep getting prompted for expressions that are defined within the query in other columns. What am I missing.

While I wait for enlightenment, I'll see if I can do something with a subquery.

Thanks in advance,
Mike

 

[r937]

[consults crystal ball]...

it's your gafloozle column, that belongs in the HAVING clause



rudy

http://r937.com/

 

[Page410]

Ok, that wasn't it. I will be able to do this as a select query.

Here is the column that I'm am trying to apply criteria to:
actualdurationdate: IIf(([tblIncomingDocVerify]![date_duration]-[tblIncomingDocVerify]![date_commenced])>[medrecertperlookup],[tblIncomingDocVerify]![date_commenced]+[medrecertperlookup],[tblIncomingDocVerify]![date_duration])

When I run the query, I am getting prompted for [medrecertperlookup], which is a field that is also defined in the query.

 

[r937]

also defined in the query? as a column alias?

you may have to replace it with its actual expression


rudy

 

[kphu]

the reason why the medrecertperlookup is being asked is because your query doesn't have a value for medrecertperlookup.

This might be taking up more space on your db but what you can do is copy this query and then point your medrecertperlookup value to the original query.

make sense?

 

[kphu]

to clearify.

if medrecertperlookup is the name of a formula within your query then how can medrecertperlookup have a value before the query is performed?

that's the reason why i suggested to create a duplicate query and point the medrecertperlookup to the original query.

hope that helps.

Currently seeking for position around Boston, MA. kenphu@yahoo.com

 

[lespaul]

What rudy said is correct

change the query to:

actualdurationdate: IIf(([tblIncomingDocVerify]![date_duration]-[tblIncomingDocVerify]![date_commenced])>put the formula for [medrecertperlookup] here,[tblIncomingDocVerify]![date_commenced]+put the formula for [medrecertperlookup] here,[tblIncomingDocVerify]![date_duration])



Leslie

 

[Page410]

Thanks everyone, of course I tried to put the actual calculation for medrecertperlookup into the expression but that makes the expression too long and truncates it.
I ended up creating a function for medrecertperlookup and made a call to in within the expression.

Thanks to everyone for their contributions