Add a Calulated Field to a Query

[supportt]

Hi, I have a Form with three fields, the field called Profit is based on the other fields. Basically Field1 - Fields2 = Profit Field. That works great on the Form itself, but I built a new Query and when I tried to add the Profit Field, it returned now rows. My query is based on a start date - end date. I tried adding the expression from the Form to my Profit field in the Query, but that didn't work either. Is there something special I need to do? In order to have that field in my Query?

Also, when I looked at the data directly in the tables, the Profit field is empty? Where is it storing the calulated Value?

Thanks
David

 

[RichO]

First of all, never store calculated fields. There is no logical reason to do that. To begin with, remove the profit field from your table.

To get the profit to show in your query enter something like:

Profit: [Field1] - [Field2]

and as long as [Field1] and [Field2] have values this calculation should work.

 

[supportt]

That did not seem to work

Here is the original Control Source I am using on the Form for my Profit Field: =[RATE_QUOTE]-[TOTAL_CONTRACT_PRICE]-[BILL_FEE]

Here is the Criteria I am using for my PROFIT field in the Query:
"PROFIT:[RATE_QUOTE]-[TOTAL_CONTRACT_PRICE]-[BILL_FEE]"

Hope this help you better understand what I have...

thanks

David

 

[WayneRyan]

David,

Here is the Criteria I am using for my PROFIT field in the Query:
"PROFIT:[RATE_QUOTE]-[TOTAL_CONTRACT_PRICE]-[BILL_FEE]"

You are right in not storing it, but it is not a criteria, it is a new field:

Put it on the TOP line in the query grid.

Wayne

 

[supportt]

No Good

Thanks for the additional info, but it did not seem to work....I added the expression to the query in the top line, which is the field:

"PROFIT:[RATE_QUOTE]-[TOTAL_CONTRACT_PRICE]-[BILL_FEE]"

When I excuted the query, it did pull the data, but the field only showed the string as above...

Thanks

David

 

[WayneRyan]

Support,

No quotes needed.

Wayne

 

[supportt]

Success!!!!!

Wayne, that did it, thanks a million....

David