RE: IIf to return a formula or field

Tech-Archive recommends: Fix windows errors by optimizing your registry

I copied the SQL below. I have had to put
"=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" in the report under the
or_cpu (price) field. WHen I put it in the query on the field line
"cost:=IIf([jc_mach_code]<140,Sum([or_cpu]/2),[or_cpu])" I get an error that
states "you tried to execute a query that does not inlcude the specified
expression 'cu_customer_name' as part of an aggregate function".
"cu_customer_name" is just the field with the customer name. Thanks again.

The value comes from when I enter a price each for the product. Normally I
would say I entered in a form, but the tables in this application are linked
into another foxpro program but the values are correct when I run the query
by itself without any IIf function. So if the part cost $30.00 or $100.00 it
shows up fine, but in the expression you had me try, it is adding up all the
values.

example:
Date range 08/01/05-08/05/05
customer A price $30.00 each and the return on the expression becomes
$380.00
customer B price $200.00 each (<140) " " " " " $190.00
customer C price $50.00 each " " " " " $380.00
customer D price $100.00 each (<140) " " " " " $190.00

SELECT q_cust.cu_cust_name, order.or_part_desc, order.or_cpu,
RejectQ.jd_jc_id, RejectQ.jc_run_date, RejectQ.jc_operation,
RejectQ.jc_order_num, RejectQ.jc_rel_number, RejectQ.jc_mach_code,
RejectQ.mt_mach_desc, RejectQ.jc_parts_bad, RejectQ.jd_notes, RejectQ.df_desc
FROM q_cust INNER JOIN ([order] INNER JOIN RejectQ ON order.or_order_num =
RejectQ.jc_order_num) ON q_cust.cu_cust_code = order.or_cust_code;

thanks -

.

Relevant Pages

  • Re: A Question of Tables
    ... Without getting into your specific query, ... > price (which should be in the STOCKPRICES table) with this exchange rate. ... > selected lang and currency, ...
    (microsoft.public.sqlserver.programming)
  • Re: Need help with expression on a query
    ... The code create a form in a webpage and the query is to update the data on ... Part, Description, Price, and Code columns. ... >> In the Form Button Memo I have the following code: ...
    (microsoft.public.access.queries)
  • Re: Adding a new parent key for a new child record on a subform
    ... The above query will make it impossible to display or enter OrderItems ... Private Sub Form_BeforeInsert ... vendor, simple enough. ... latest price and insert it as this order-line-items new price: ...
    (microsoft.public.access.forms)
  • Re: adding a text string to data from one field in one database to another
    ... After running the other create table query, ... I have items with price breaks a various quantity levels each ... Qty price at that qty price for that qty ... In the database the data needs to be entered into a field (not ...
    (comp.databases.ms-access)
  • Re: pricing formulas
    ... Partnumber, Description, and Cost and build some expressions in query design ... You can also compare your current retail price to the "formula price" ...
    (microsoft.public.access.forms)